Debug - Oracle索引未生效的问题

 

问题描述

2022-09-26T14:13:02,065 [ComparableSecurity-thread-1] INFO  com.huatai.nats.api.impl.client.Client - Query done(total=6), sql=select * from FICC_DWODS.FICC_RDS_BOND_VALUATION where TRADE_DATE in (20220919,20220920,20220921,20220922,20220923,20220926) and HEADS_SECURITY_ID in ('2105778.IB') and VALUATION_SOURCE  IN ('chinabond')
2022-09-26T14:13:02,065 [ComparableSecurity-thread-1] INFO  com.huatai.quant.utils.ComparableSecurityUtil - Query out [6] results, using sql query = [select * from FICC_DWODS.FICC_RDS_BOND_VALUATION where TRADE_DATE in (20220919,20220920,20220921,20220922,20220923,20220926) and HEADS_SECURITY_ID in ('2105778.IB') and VALUATION_SOURCE  IN ('chinabond')]
2022-09-26T14:15:24,367 [ComparableSecurity-thread-1] INFO  com.huatai.nats.api.impl.client.Client - Query done(total=18), sql=select * from FICC_DWODS.FICC_RDS_BOND_VALUATION where TRADE_DATE in (20220919,20220920,20220921,20220922,20220923,20220926) and HEADS_SECURITY_ID in ('2171116.IB' ,'2171266.IB' ,'2171269.IB') and VALUATION_SOURCE  IN ('chinabond')
2022-09-26T14:15:24,367 [ComparableSecurity-thread-1] INFO  com.huatai.quant.utils.ComparableSecurityUtil - Query out [18] results, using sql query = [select * from FICC_DWODS.FICC_RDS_BOND_VALUATION where TRADE_DATE in (20220919,20220920,20220921,20220922,20220923,20220926) and HEADS_SECURITY_ID in ('2171116.IB' ,'2171266.IB' ,'2171269.IB') and VALUATION_SOURCE  IN ('chinabond')]

 

前后两个请求语句相差的时间是，2分22秒：

2022-09-26T14:13:02,065

2022-09-26T14:15:24,367

 

这是非常不正常的，查询【18】条数据就要2分22秒

但其实该表 FICC_DWODS.FICC_RDS_BOND_VALUATION 已经按照该sql语句的condition顺序建了索引

 

问题分析

该索引已经存在，但不知道为何没有起效。

 

 

通过下方sql分析（两个sql要在一个界面里面，分别执行）：

Explain plan FOR select * from FICC_DWODS.FICC_RDS_BOND_VALUATION where 
TRADE_DATE in (20220919,20220920,20220921,20220922,20220923,20220926) 
and HEADS_SECURITY_ID in ('2171116.IB' ,'2171266.IB' ,'2171269.IB') 
and VALUATION_SOURCE  IN ('chinabond');

--查看索引使用明细
select plan_table_output from TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));

 

发现这里是ACCESS FULL，说明是查询了全表。

里面有TO_NUMBER()去转换了tradeDate的格式，怀疑是因为格式问题未能走索引。

 

 

 

将原sql修改后，再次尝试分析是否走索引（两个sql要在一个界面里面，分别执行）：

Explain plan FOR select * from FICC_DWODS.FICC_RDS_BOND_VALUATION where 
TRADE_DATE in ('20220919','20220920','20220921','20220922','20220923','20220926') 
and HEADS_SECURITY_ID in ('2171116.IB' ,'2171266.IB' ,'2171269.IB') 
and VALUATION_SOURCE  IN ('chinabond');

--查看索引使用明细
select plan_table_output from TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));

 

修改后，说明走了索引！！！

 

 

参考

oracle查看执行计划explain plan FOR：https://www.jianshu.com/p/bd4fe6ac3925