【YashanDB 知识库】outline 固化执行计划

SQL
--创建测试outline：ol_ab/ol_ba
conn sales/sales
CREATE OUTLINE ol_ab FOR CATEGORY ctgy_ab ON
SELECT /*+ leading(a,b) */ a.area_name, b.branch_name
FROM area a, branches b
WHERE a.area_no = b.area_no
AND b.branch_no LIKE '01%'
AND a.area_no LIKE '01';
 
CREATE OUTLINE ol_ba FOR CATEGORY ctgy_ab ON
SELECT a.area_name, b.branch_name
FROM area a, branches b
WHERE a.area_no = b.area_no
AND b.branch_no LIKE '01%'
AND a.area_no LIKE '01';
 
--修改基表，交换outline
conn / as sysdba
UPDATE OL$HINTS SET OL_NAME=DECODE(OL_NAME,'OL_AB','OL_BA','OL_BA','OL_AB') WHERE OL_NAME IN ('OL_AB','OL_BA');
 
--打开outline开关
ALTER SESSION SET USE_STORED_OUTLINES=ctgy_ab;
 
--验证应用SQL执行计划
conn sales/sales
explain SELECT a.area_name, b.branch_name
FROM area a, branches b
WHERE a.area_no = b.area_no
AND b.branch_no LIKE '01%'
AND a.area_no LIKE '01';