左驱动非分区表数据量变化影响分区查询执行计划变形分析！

---wangyingjia20140909

select count(1) from(
(select t.ID,
t.OUR_TRANS_NO,
t.ORDER_TYPE,
t.TRANS_REQ_NO,
t.CONN_CHANNEL,
t.SETTLE_DATE,
t.CHK_DATE,
t.ERR_TYPE,
t.ORDER_DATE,
t.TOTAL_AMT,
t.ORDER_STATUS,
t.DELETE_FLAG,
t.CREATE_TIME,
t.LAST_UPD_TIME,
t.HANDLE_STATUS,
t.HANDLE_DATE,
t.HANDLER,
t.HANDLER_IP,
t.HANDLE_TYPE
from T_INNER_CHK_ERR t
where t.DELETE_FLAG = '0'
and t.SETTLE_DATE >= to_date('20140801', 'YYYYMMDD')
AND t.SETTLE_DATE <= to_date('20140805', 'YYYYMMDD')) t
left join t_pay_trans_detail p
on t.our_trans_no = p.our_trans_no
and p.delete_flag = '0'
AND p.SETTLE_DATE >= to_date('20140801', 'YYYYMMDD')
AND p.SETTLE_DATE <= to_date('20140805', 'YYYYMMDD')
left join (select b.org_code, b.org_bank_code, b.bank_code, info.bank_name
from (select t.org_code,
t.org_bank_code,
min(t.bank_code) bank_code
from t_org_bank t
group by t.org_code, t.org_bank_code) b
left join t_bank_info info
on b.bank_code = info.bank_code) bank
on p.pay_org_code = bank.org_code
and p.org_bank_code = bank.org_bank_code
left join t_busi_trans_detail d
on d.our_trans_no = t.our_trans_no
AND d.SETTLE_DATE >= to_date('20140801', 'YYYYMMDD')
AND d.SETTLE_DATE <= to_date('20140805', 'YYYYMMDD')
and d.delete_flag = '0'
left join t_order_busi_field_detail f
on f.our_trans_no = t.our_trans_no
AND f.SETTLE_DATE >= to_date('20140801', 'YYYYMMDD')
AND f.SETTLE_DATE <= to_date('20140805', 'YYYYMMDD')
and f.delete_flag = '0'
);

 

单独抽取了部分SQL进行实验。

明明建有索引，为何4天之内的数据可以走，超过4天就走全表扫描了呢？

可以确定的是执行计划的变更取决于T_INNER_CHK_ERR表数据结果集的多少，达到一定阀值（达到阀值指此时走nest loop的cost已经超过了hash join）之后，两表的链接方式将由NEST LOOP变为HASH JOIN。

开启10053事件追踪ORACLE是如何选择执行计划。

这里截取部分信息进行说明：

选择NEST LOOP的：

 

选择HASH JOIN

我们注意到，走NEST LOOP时可以通过OUR_TRANS_NO列走索引，速度也很快。

而到了HASH JOIN时，鉴于hash join连接的方式，需要首先或者两个表的结果集。在获取大表结果集的过程中，由于谓词条件settle_date处于复合索引的第二列，走它要使用skip index scan的方式，cost巨大，固优化器选择了走全表扫描。

到这里也就明了了，为了消除全表扫描，只需要单独为T_PAY_TRANS_DETAIL表的SETTLE_DATE列创建索引即可。

创建索引之后，表多了一个访问方式