使用临时表的sql优化案例一

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