sql优化案例-从走索引变成走分区消除
有用户报一个sql问题，说一张表从EGIS用GG同步到GBS，特意在GBS上将此表重定义成分区表，可是该sql仍然不走分区消除。现在走索引的方式，要20多秒，用户体验不好。
ｇｂｓ：
select /*+index(a1 IX_ABBS_SETT_DTL_UWD_PRDNO_ANO) leading(a1,a2) use_nl(a1,a2) */
deptno as deptNo,
a1.agencyno as agencyNo,
productno as productNo,
count(certno) as totalNumber,count(distinct billno) as billNumber,
count(decode(cert_sts,‘P‘,cert_sts,null)) as undwrtNumber,
sum(decode(is_together_insure, ‘Y‘, (decode(cert_sts,‘P‘,coins_tot_prem,0)), (decode(cert_sts,‘P‘,tot_prem,0)))) as undwrtPrem,
count(decode(cert_sts,‘N‘,cert_sts,null)) as revokeNumber,
sum(decode(is_together_insure, ‘Y‘, (decode(cert_sts,‘N‘,coins_tot_prem,0)), decode(cert_sts,‘N‘,tot_prem,0))) as revokePrem,
sum(decode(is_together_insure, ‘Y‘, (decode(cert_sts,‘P‘,coins_tot_prem,‘N‘,-coins_tot_prem)), decode(cert_sts,‘P‘,tot_prem,‘N‘,-tot_prem))) as totalPrem
from abbs_sett_detail a1
where (a1.UNDWRT_DATE >= date‘2013-1-1‘ and a1.UNDWRT_DATE <date;‘2013-2-1‘ and a1.cert_sts = ‘P‘)
and a1.agencyno like ‘SCXN%‘
and a1.deptNo=‘G35‘
group by deptno,productno,a1.agencyno
order by deptno, a1.agencyno,productno
egis：
select /*+index(a1 IX_ABBS_SETT_DTL_UWD_PRDNO_ANO) leading(a1,a2) use_nl(a1,a2) */
deptno as deptNo,
a1.agencyno as agencyNo,
productno as productNo,
count(certno) as totalNumber,count(distinct billno) as billNumber,
count(decode(cert_sts,‘P‘,cert_sts,null)) as undwrtNumber,
sum(decode(is_together_insure, ‘Y‘, (decode(cert_sts,‘P‘,coins_tot_prem,0)), (decode(cert_sts,‘P‘,tot_prem,0)))) as undwrtPrem,
count(decode(cert_sts,‘N‘,cert_sts,null)) as revokeNumber,
sum(decode(is_together_insure, ‘Y‘, (decode(cert_sts,‘N‘,coins_tot_prem,0)), decode(cert_sts,‘N‘,tot_prem,0))) as revokePrem,
sum(decode(is_together_insure, ‘Y‘, (decode(cert_sts,‘P‘,coins_tot_prem,‘N‘,-coins_tot_prem)), decode(cert_sts,‘P‘,tot_prem,‘N‘,-tot_prem))) as totalPrem
from abbs_sale_detail a1
where (a1.UNDWRT_DATE >= date‘2013-1-1‘ and a1.UNDWRT_DATE <date;‘2013-2-1‘ and a1.cert_sts = ‘P‘)
and a1.agencyno like ‘SCXN%‘
and a1.deptNo=‘G35‘
group by deptno,productno,a1.agencyno
order by deptno, a1.agencyno,productno
这个语句，无论在GBS还是EGIS生产库，都是走的索引。
用户很疑惑，为什么不能走分区消除，并且走索引。
其实驱动查询条件里面，要么走索引，要么走分区的，只能选择其中其一。
当然，在走索引的情况下，如果索引是local partition index，是可以选择走index的分区消除，但没什么必要的在这个sql里面，性能完全没优化。
Index的索引分区消除，更多是用于索引的可用性，以及出现index 的FFS等时提升性能。
这个索引IX_ABBS_SETT_DTL_UWD_PRDNO_ANO的统计信息，num_rows显式这个索引只有两行，表的统计信息也是两行。用户反映说是，当初批量记载数据过来后，没有进行过统计信息的收集。
SQL> @statind IX_ABBS_SETT_DTL_UWD_PRDNO_ANO
OWNER TABLE_NAME INDEX_NAME LAST_ANALYZED NUM_ROWS DISTINCT_KEYS CLUSTERING_FACTOR UNIQUENESS BLEVEL LEAF_BLOCKS STATUS
-------- ------------------------------ ------------------------------ -------------------------------------------------- ---------- ------------- ----------------- ---------- ---------- ----------- ----------
ABBSDATA ABBS_SETT_DETAIL IX_ABBS_SETT_DTL_UWD_PRDNO_ANO 2014/2/19 4:10:02 2 2 2 NONUNIQUE 0 1 VALID
而实际上这个索引远远不止两行，就凭我们加了UNDWRT_DATE条件来约束，都能返回2332190行，耗时13秒。
select count(*) from abbs_sett_detail a1 where a1.UNDWRT_DATE >= date‘2013-1-1‘ and a1.UNDWRT_DATE < date‘2013-2-1‘
--返回2332190行
而实际上，这个SQL实际跑的时候，先走的索引，Access谓词也是如上的UNDWRT_DATE。
尽管这个索引有三列,尽管SQL还有如a1.agencyno like ‘SCXN%‘这样的，但是因为AGENCYNO位于第3列，所以不能作为Access谓词，只能作为Index range scan后的过滤谓词。
SQL> @ind IX_ABBS_SETT_DTL_UWD_PRDNO_ANO
COLUMN_NAME COL INDEX_NAME INDEX_OWNER TABLE_OWNER
------------------------- --- ------------------------------ -------------------- ---------------
UNDWRT_DATE 1 IX_ABBS_SETT_DTL_UWD_PRDNO_ANO ABBSDATA ABBSDATA
PRODUCTNO 2 IX_ABBS_SETT_DTL_UWD_PRDNO_ANO ABBSDATA ABBSDATA
AGENCYNO 3 IX_ABBS_SETT_DTL_UWD_PRDNO_ANO ABBSDATA ABBSDATA
select count(*) from abbs_sett_detail a1 where a1.UNDWRT_DATE >= date‘2013-1-1‘ and a1.UNDWRT_DATE < date‘2013-2-1‘
and a1.agencyno like ‘SCXN%‘
--返回48582行。
所以要根据这48582的rowid进行回表操作。加上之前走索引的2332190行，近db file sequential read单块读就得IO将近28万行。
既然统计信息已经不准（GBS的统计信息收集策略是收集不启用），所以索引上的Clustering_factor也没有参考价值了。
而根据这个条件a1.deptno=‘G35‘
select count(*) from abbs_sett_detail a1 where a1.deptno=‘G35‘;
--返回743820行。
GBS库db_file_multiblock_read_count参数是128，db_block_size=8192。
从128这个值看来，我们更倾向于走全表而不是走索引，在这个特殊场景里面，可以认为，我们应该更倾向于走分区消除而不是走索引。
我用no_index hint使其第一步走的是分区，并且将多余的表连接hint去除。
select /*+NO_INDEX(a1 IX_ABBS_SETT_DTL_UWD_PRDNO_ANO) no_index(a1 ux_abbs_sett_dtl_certno_ctsts) fwy1123 */
deptno as deptNo,
a1.agencyno as agencyNo,
productno as productNo,
count(certno) as totalNumber,
count(distinct billno) as billNumber,
count(decode(cert_sts, ‘P‘, cert_sts, null)) as undwrtNumber,
sum(decode(is_together_insure,
‘Y‘,
(decode(cert_sts, ‘P‘, coins_tot_prem, 0)),
(decode(cert_sts, ‘P‘, tot_prem, 0)))) as undwrtPrem,
count(decode(cert_sts, ‘N‘, cert_sts, null)) as revokeNumber,
sum(decode(is_together_insure,
‘Y‘,
(decode(cert_sts, ‘N‘, coins_tot_prem, 0)),
decode(cert_sts, ‘N‘, tot_prem, 0))) as revokePrem,
sum(decode(is_together_insure,
‘Y‘,
(decode(cert_sts, ‘P‘, coins_tot_prem, ‘N‘, -coins_tot_prem)),
decode(cert_sts, ‘P‘, tot_prem, ‘N‘, -tot_prem))) as totalPrem
from abbs_sett_detail a1
where (a1.UNDWRT_DATE >= date‘2013-1-1‘ and a1.UNDWRT_DATE < date‘2013-2-1‘ and a1.cert_sts = ‘P‘)
and a1.agencyno like ‘SCXN%‘ and a1.deptNo = ‘G35‘ group by deptno, productno, a1.agencyno order by deptno, a1.agencyno, productno
buffer get从原来的67459下降到19820，执行速度从20秒提升到7秒。
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
