临时表不能利用索引的研究
临时表不能利用索引的研究
前言：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。
