Oracle 19c WHERE条件顺序不同导致索引失效执行效率差异问题

核心原因 #

两个SQL的WHERE条件顺序不同，在Oracle的执行计划体系下会被识别为完全独立的SQL，触发了优化器的判断偏差：

• Oracle的共享池以完整SQL文本作为执行计划缓存的唯一key，空格、条件顺序、大小写的差异都会生成独立的执行计划缓存条目，两个SQL不会复用同一个计划。
• 你所使用的19.3是19c的初始版本，存在优化器谓词匹配的边缘bug：你的联合索引BOOKING_511_2的列顺序为PARENTFOREIGNKEY, CLASSID, ID，当SQL的WHERE条件顺序和索引前缀顺序完全匹配时，优化器可以快速识别到可用索引，生成索引范围扫描计划；当条件顺序反过来时，部分场景下优化器会错误跳过该索引的成本评估，直接选择全表扫描。
• 慢SQL首次执行时可能刚好碰到统计信息过时、或者对应谓词值的数据量采样偏差，优化器误判全表扫描成本低于索引扫描，生成了错误的执行计划，后续执行都直接复用了该缓存计划，导致持续慢查询。

修复方案 #

1. 首先更新表和索引的统计信息，消除统计偏差：

EXEC DBMS_STATS.GATHER_TABLE_STATS(
    ownname => '你的表所属Schema名称',
    tabname => 'BOOKING',
    cascade => TRUE,
    estimate_percent => 100
);

2. 清除慢SQL的错误执行计划缓存：
   先执行下面的SQL拿到慢查询的SQL_ID和子游标编号：

   SELECT SQL_ID, CHILD_NUMBER, SQL_TEXT FROM V$SQL 
   WHERE SQL_TEXT LIKE '%SELECT * FROM BOOKING WHERE (CLASSID=511)%';
   

   再执行存储过程清理对应缓存：

   EXEC DBMS_SHARED_POOL.PURGE('{SQL_ID},{CHILD_NUMBER}','C');
   

3. 如果问题稳定复现，属于版本bug的话，可以通过以下两种方式规避：
   • 在慢SQL中增加强制索引Hint：SELECT /*+ INDEX(BOOKING BOOKING_511_2) */ * FROM BOOKING WHERE (CLASSID=511) AND PARENTFOREIGNKEY=31647961 ORDER BY BOOKINGNO;
   • 统一SQL的WHERE条件写法，和索引前缀的列顺序保持一致。

内容的提问来源于stack exchange，提问作者Go RanGer