【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';