使用临时表的sql优化案例一
基于RBO的情况下
[@more@]oracle：9204
1.table和index的相关信息：
create global temporary table tmp_ic_atp_test1
(CTMPCORPID CHAR(4),
CTMPCALBODYID CHAR(20),
CINVENTORYID CHAR(20),
VFREE1 VARCHAR2(20),
VFREE2 VARCHAR2(20),
VFREE3 VARCHAR2(20) ,
VFREE4 VARCHAR2(20) ,
VFREE5 VARCHAR2(20) ,
VFREE6 VARCHAR2(20) ,
VFREE7 VARCHAR2(20) ,
VFREE8 VARCHAR2(20) ,
VFREE9 VARCHAR2(20) ,
VFREE10 VARCHAR2(20) ,
DPLANDATE CHAR(10) ,
USABLEAMOUNT CHAR(16) ,
USABLEAMOUNTBYFREE CHAR(1) ,
TS CHAR(19) )
on commit preserve rows;
create index i_bd_bd_produce1 on bd_produce (pk_invmandoc,pk_calbody);
create inedx i_tmp_ic_atp_test1 on tmp_ic_atp_test1 (cinventoryid,ctmpcalbodyid);
insert into tmp_ic_atp_test1(ctmpcorpid,ctmpcalbodyid,cinventoryid)
values('1001','1001AA1000000000015U','0001AA1000000000200L')；
select count(*) from bd_produre =3178823
size: 476.21M
2.sql的原始执行计划：
分析了table bd_produce，index i_bd_produce1
select bd_produce.pk_corp , bd_produce.pk_calbody ccalbodyid , bd_produce.pk_invmandoc
cinventoryid , bd_produce.safetystocknum nsafenum , bd_produce.usableamount , bd_produce.usableamountbyfree from tmp_ic_atp_test1 ,bd_produce
where tmp_ic_atp_test1.cinventoryid = bd_produce.pk_invmandoc
and tmp_ic_atp_test1.ctmpcalbodyid =bd_produce.pk_calbody
我们发现，在CBO下，该sql执行时间很长。观察该sql的执行计划，发现对table bd_produce进行了全表扫描：
SELECT STATEMENT, GOAL = CHOOSE 8511 1060318
HASH JOIN 8511 1060318
INDEX FAST FULL SCAN IND_TMP_IC_ATP_TEST1 4 719840 NON-UNIQUE
TABLE ACCESS FULL BD_PRODUCE 5755 196450720 -此处对大表进行了全表扫描
尝试改写sql：
select bd_produce.pk_corp , bd_produce.pk_calbody ccalbodyid ,
bd_produce.pk_invmandoc cinventoryid , bd_produce.safetystocknum nsafenum ,
bd_produce.usableamount , bd_produce.usableamountbyfree
from bd_produce
where (pk_invmandoc,pk_calbody)
in (select tmp_ic_atp_test1.cinventoryid,tmp_ic_atp_test1 .ctmpcalbodyid from tmp_ic_atp_test1)
执行计划依然是：TABLE ACCESS FULL BD_PRODUCE
SELECT STATEMENT, GOAL = CHOOSE 8511 1060318
HASH JOIN 8511 1060318
SORT UNIQUE
INDEX FAST FULL SCAN IND_TMP_IC_ATP_TEST1 4 719840 NON-UNIQUE
TABLE ACCESS FULL BD_PRODUCE 5755 196450720
在RBO下的情况：
SELECT STATEMENT, GOAL = RULE
TABLE ACCESS BY INDEX ROWID BD_PRODUCE
NESTED LOOPS
TABLE ACCESS FULL TMP_IC_ATP_TEST1
INDEX RANGE SCAN I_BD_PRODUCE1 NON-UNIQUE
对table bd_produce进行的是index访问，执行时间很快。
3.分析临时表的统计信息
分析临时表TMP_IC_ATP_TEST1进行统计信息分析后，执行计划终于正常了：
SELECT STATEMENT, GOAL = CHOOSE 4 106
TABLE ACCESS BY INDEX ROWID BD_PRODUCE 3 62
NESTED LOOPS 4 106
INDEX FULL SCAN IND_TMP_IC_ATP_TEST1 44 NON-UNIQUE
INDEX RANGE SCAN I_BD_PRODUCE1 2 NON-UNIQUE
4.改写sql来引导oracle使用index：
删除临时表的统计信息，并改写sql：
select bd_produce.pk_corp , bd_produce.pk_calbody ccalbodyid ,
bd_produce.pk_invmandoc cinventoryid , bd_produce.safetystocknum nsafenum ,
bd_produce.usableamount,bd_produce.usableamountbyfree from bd_produce ,tmp_ic_atp_test1
where tmp_ic_atp_test1.cinventoryid = bd_produce.pk_invmandoc
and tmp_ic_atp_test1 .ctmpcalbodyid =bd_produce.pk_calbody
and bd_produce.pk_invmandoc > chr(0) –增加一个条件，使oracle使用index
SELECT STATEMENT, GOAL = CHOOSE 1640 53000
TABLE ACCESS BY INDEX ROWID BD_PRODUCE 3 62
NESTED LOOPS 1640 53000
INDEX RANGE SCAN IND_TMP_IC_ATP_TEST1 2 35992 NON-UNIQUE
INDEX RANGE SCAN I_BD_PRODUCE1 2 NON-UNIQUE
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/60217/viewspace-1007878/ ，如需转载，请注明出处，否则将追究法律责任。
