使用临时表的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博客 ” ，链接：http://blog.itpub.net/60217/viewspace-1007878/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/60217/viewspace-1007878/