临时表不能利用索引的研究

前言：Oracle临时表能避免并发，提高程序的可伸缩性等诸多优点，但在查询时，有时不能有效利用索引

。

问题描述：有如下两张表，其中yd_yhjbda是普通表，有记录数10万条，TMP_JSHH_X为Oracle基于会话的

临时表，有一条记录：
表TMP_JSHH_X
CREATE GLOBAL TEMPORARY TABLE TMP_JSHH_X
(
  HH    NUMBER(8),
  DFNY  VARCHAR2(6),
  SFPC  NUMBER(2)
)
ON COMMIT PRESERVE ROWS;

CREATE TABLE YD_YHJBDA
(
  HH       NUMBER(8)                            NOT NULL,
  HM       VARCHAR2(100),
  YDDZ     VARCHAR2(50),
  SFZH     VARCHAR2(18)
);

CREATE UNIQUE INDEX YD_YHJBDA_PK ON YD_YHJBDA
(HH);

(1)进行如下关联查询
select a.hh,a.hm,a.YDDZ,a.sfzh
from tmp_jshh_x b,yd_yhjbda a
where a.hh=b.hh

执行计划
Operation        Object Name        Rows        Bytes        Cost        Object Node        In/Out        PStart       

PStop

SELECT STATEMENT Optimizer Mode=CHOOSE                7 M                 4677                                     

                
  HASH JOIN                7 M        373 M        4677                                                      
    TABLE ACCESS FULL        TMP_JSHH_X        8 K        103 K        10                                             

        
    TABLE ACCESS FULL        YD_YHJBDA        94 K        3 M        3624                                             

        
发现没有利用索引

(2)改用如下查询（查询字段减少）：
select a.hh
from tmp_jshh_x b,yd_yhjbda a
where a.hh=b.hh
或：
select b.*
from tmp_jshh_x b,yd_yhjbda a
where a.hh=b.hh

执行计划：
Operation        Object Name        Rows        Bytes        Cost        Object Node        In/Out        PStart       

PStop

SELECT STATEMENT Optimizer Mode=CHOOSE                7 M                 10                                     

                
  NESTED LOOPS                7 M        131 M        10                                                      
    TABLE ACCESS FULL        TMP_JSHH_X        8 K        103 K        10                                             

        
    INDEX UNIQUE SCAN        YD_YHJBDA_PK        94 K        459 K                                                    

        
发现成功利用了YD_YHJBDA_PK索引

(3)改写查询，利用hint
select /*+ ORDERED use_nl(a)*/a.hh,a.hm,a.yddz,a.sfzh
from tmp_jshh_x b,yd_yhjbda a
where a.hh=b.hh

执行计划
Operation        Object Name        Rows        Bytes        Cost        Object Node        In/Out        PStart       

PStop

SELECT STATEMENT Optimizer Mode=CHOOSE                7 M                 8178                                     

                
  NESTED LOOPS                7 M        373 M        8178                                                      
    TABLE ACCESS FULL        TMP_JSHH_X        8 K        103 K        10                                             

        
    TABLE ACCESS BY INDEX ROWID        YD_YHJBDA        94 K        3 M        1                                     

                
      INDEX UNIQUE SCAN        YD_YHJBDA_PK        94 K                                                             

        
发现成功利用索引

后来把TMP_JSHH_X改成普通表能够正确使用索引YD_YHJBDA_PK。

总结：在使用Oracle临时表时有时不能使用索引，可能是和临时表的特性有关系，不能收集统计数据，执

行计划不是最优，此时需要注意使用hint。

【转自bbs.bitsCN.com】

本文网址:http://bbs.bitscn.com/69514

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/13999636/viewspace-663273/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/13999636/viewspace-663273/