有用户报一个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 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 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显式这个索引只有两行，表的统计信息也是两行。用户反映说是，当初批量记载数据过来后，没有进行过统计信息的收集。 而实际上这个索引远远不止两行，就凭我们加了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_U 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