SQL语句如下:select
t1.SERV_ID ,
t2.DETAIL AS SERV_ADDR_NAME
from ctltmp.serv_hist_all t1 , stg.tb_cm_address t2
where t1.address_id=t2.address_idctltmp.serv_hist_all大概是450万数据,
stg.tb_cm_address大概是800万数据,ctltmp.serv_hist_all在address_id上面没有索引,
而stg.tb_cm_address在address_id上面有索引,为什么看查询计划stg.tb_cm_address总是不走索引?SELECT STATEMENT, GOAL = ALL_ROWS 27783 27705 4323846 237811530
PX COORDINATOR
PX SEND QC (RANDOM) SYS :TQ10002 27783 27705 4323846 237811530
HASH JOIN 27783 27705 4323846 237811530
PX RECEIVE 2730 2721 4323846 112419996
PX SEND HASH SYS :TQ10001 2730 2721 4323846 112419996
PX BLOCK ITERATOR 2730 2721 4323846 112419996
TABLE ACCESS FULL CTLTMP SERV_HIST_ALL 2730 2721 4323846 112419996
BUFFER SORT
PX RECEIVE 25049 24984 7774761 225468069
PX SEND HASH SYS :TQ10000 25049 24984 7774761 225468069
TABLE ACCESS FULL STG TB_CM_ADDRESS 25049 24984 7774761 225468069
t1.SERV_ID ,
t2.DETAIL AS SERV_ADDR_NAME
from ctltmp.serv_hist_all t1 , stg.tb_cm_address t2
where t1.address_id=t2.address_idctltmp.serv_hist_all大概是450万数据,
stg.tb_cm_address大概是800万数据,ctltmp.serv_hist_all在address_id上面没有索引,
而stg.tb_cm_address在address_id上面有索引,为什么看查询计划stg.tb_cm_address总是不走索引?SELECT STATEMENT, GOAL = ALL_ROWS 27783 27705 4323846 237811530
PX COORDINATOR
PX SEND QC (RANDOM) SYS :TQ10002 27783 27705 4323846 237811530
HASH JOIN 27783 27705 4323846 237811530
PX RECEIVE 2730 2721 4323846 112419996
PX SEND HASH SYS :TQ10001 2730 2721 4323846 112419996
PX BLOCK ITERATOR 2730 2721 4323846 112419996
TABLE ACCESS FULL CTLTMP SERV_HIST_ALL 2730 2721 4323846 112419996
BUFFER SORT
PX RECEIVE 25049 24984 7774761 225468069
PX SEND HASH SYS :TQ10000 25049 24984 7774761 225468069
TABLE ACCESS FULL STG TB_CM_ADDRESS 25049 24984 7774761 225468069
事实上这种使用索引的执行计划往往使用nl(嵌套循环)方式,nl在结果返回大量数据的情况下,cpu资源的消耗是相当大的.在10g以前好似在执行计划的成本评估中尚未引入cpu成本的概念,几乎所有的成本来自于io成本.象你所提到的语句,按常理,走hash会更快,根本用不上索引.而且从你提供的执行计划来看,表上启用了并行,成本的计算更添了很多变数,除非指定优化提示,否则很难按你预期的方式执行
像这种关联,所有数据都要匹配,还是走全表扫描合适。走索引的话相当于索引扫描+全表扫描,更慢。