有用户报一个sql问题,说一张表从EGIS用GG同步到GBS,特意在GBS上将此表重定义成分区表,可是该sql仍然不走分区消除。现在走索引的方式,要20多秒,用户体验不好。
gbs:
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秒。