sql优化案例-从走索引变成走分区消除

有用户报一个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生产库,都是走的索引。

sql优化案例-从走索引变成走分区消除

用户很疑惑,为什么不能走分区消除,并且走索引。

其实驱动查询条件里面,要么走索引,要么走分区的,只能选择其中其一。

当然,在走索引的情况下,如果索引是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行。

 

GBSdb_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秒。


 


 


sql优化案例-从走索引变成走分区消除,布布扣,bubuko.com

sql优化案例-从走索引变成走分区消除

上一篇:Import Data from A DB to Another (To be formated)


下一篇:【SQL Server高可用性】数据库复制:修改表结构、新增表、新增存储过程 会被复制到订阅服务器?