Oracle优化器未优先选用索引：自连接查询未走索引求助
咱们先把你的场景和问题理清楚：你有一张100万行的SCHM.MY_TABLE，这次查询只涉及大概1万行（占比不到1%），但执行自连接时优化器没用到你预期的索引，执行计划哈希值是1210306805。你的查询语句是：
explain plan for SELECT * FROM SCHM.MY_TABLE A1, SCHM.MY_TABLE A2 WHERE (A1.K_ID = '123abc') AND A1.HDT = A2.HDT AND A2.C_DATE BETWEEN A1.SYSDATE - 0.0004 AND A1.SYSDATE + 0.0004 AND A1.GKID = A2.GKID;
下面我从几个常见的排查方向给你梳理解决办法：
一、先确认索引是否建对了、是否有效
首先，驱动表A1的过滤条件是A1.K_ID = '123abc'，这一步应该先快速缩小结果集到1万行，所以先检查K_ID上有没有索引：
SELECT index_name FROM user_indexes WHERE table_name = 'MY_TABLE' AND column_name = 'K_ID';
如果查不到结果，那赶紧给K_ID建个单键索引——这是第一步，不然A1可能会全表扫描，直接拖慢整个查询。
然后看被驱动表A2的连接+过滤条件：A1.HDT = A2.HDT、A1.GKID = A2.GKID，再加上A2.C_DATE的范围过滤。最优的联合索引应该是(HDT, GKID, C_DATE)——把等值匹配的列放在前面，范围列放在最后。如果你的现有索引是单键索引，或者列的顺序不对（比如把C_DATE放前面了），优化器大概率不会选它，因为成本太高。
二、检查统计信息是否过时了
100万行的表，如果统计信息好久没更新，优化器就会瞎猜结果集的大小，自然会选错执行计划。你可以跑这条命令更新一下统计信息：
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => 'SCHM', TABNAME => 'MY_TABLE', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, CASCADE => TRUE);
这里CASCADE => TRUE会同时更新索引的统计信息，确保优化器能准确算出用索引的成本。
三、排查查询里的函数/列名问题
哎，你查询里写了A1.SYSDATE——这是笔误吗？SYSDATE是Oracle的系统函数，如果你是想取当前系统时间，应该直接写SYSDATE，而不是A1.SYSDATE（除非你的表真有个叫SYSDATE的列，这其实是个不好的命名习惯，容易和系统函数冲突）。
如果是笔误，改成SYSDATE之后，A2.C_DATE的范围就是固定值了，优化器更容易判断用索引的价值；如果确实是表中的列（比如叫SYS_DATE），那这个范围条件是依赖A1每行的值的，优化器很难准确估算A2的结果集，这时候可以试试用CTE先把A1的结果集缓存下来：
WITH A1_FILTERED AS ( SELECT * FROM SCHM.MY_TABLE WHERE K_ID = '123abc' ) SELECT * FROM A1_FILTERED A1 JOIN SCHM.MY_TABLE A2 ON A1.HDT = A2.HDT AND A1.GKID = A2.GKID AND A2.C_DATE BETWEEN A1.SYS_DATE - 0.0004 AND A1.SYS_DATE + 0.0004;
这样优化器先处理A1_FILTERED得到1万行，再针对每一行去A2用索引查找，大概率会选择索引扫描。
四、临时方案：用索引提示强制使用索引
如果前面的调整都没用，你可以试试用索引提示逼优化器用指定索引，比如假设A2的联合索引叫IDX_MY_TABLE_HDT_GKID_CDATE：
explain plan for SELECT * FROM SCHM.MY_TABLE A1, SCHM.MY_TABLE A2 WHERE (A1.K_ID = '123abc') AND A1.HDT = A2.HDT AND A2.C_DATE BETWEEN A1.SYSDATE - 0.0004 AND A1.SYSDATE + 0.0004 AND A1.GKID = A2.GKID AND /*+ INDEX(A2 IDX_MY_TABLE_HDT_GKID_CDATE) */ 1=1;
不过提示是最后手段，优先让优化器自己选，所以还是先搞定索引结构和统计信息的问题。
五、看完整执行计划找线索
光看执行计划哈希值不够，你跑这条命令看完整的执行计划：
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
重点看这两个地方：
A1的访问方式：如果是FULL TABLE SCAN，那说明K_ID的索引没生效，要么是索引不存在，要么是统计信息错了；如果是INDEX RANGE SCAN，那这一步没问题。A2的访问方式：如果是HASH JOIN，说明优化器觉得哈希连接比嵌套循环+索引扫描成本低，这时候要看A2的结果集估算是不是太大，大概率是统计信息过时导致的。
内容的提问来源于stack exchange，提问作者mehmet sahin
