Oracle多表左连接查询未使用索引问题咨询（大表场景）

DevOps 小满

2026-5-22

解决Oracle多表左外连接未使用索引的问题

首先，先明确你的查询语句（方便后续分析）：

SELECT * 
FROM TABLE A 
LEFT OUTER JOIN B ON A.FACT_KEY=B.FACT_KEY AND A.ENTITY_ID=B.ENTITY_ID 
LEFT OUTER JOIN C ON A.FACT_KEY=C.FACT_KEY AND A.ENTITY_ID=C.ENTITY_ID AND C.CREATED_DT BETWEEN 201611 AND 201712 
LEFT OUTER JOIN D ON A.FACT_KEY=D.FACT_KEY AND A.ENTITY_ID=D.ENTITY_ID;

针对你提到的“已创建Fact_key等字段普通索引但未被使用”的问题，结合各表的超大数据量（A:6000万、B:1.7亿、C:1.5亿、D:2亿），我整理了以下排查和优化方向：

优先更新表和索引的统计信息
Oracle的成本优化器（CBO）完全依赖准确的统计信息来选择最优执行计划。如果大表的统计信息过时，优化器可能会错误判断数据分布，放弃使用索引而选择全表扫描。执行以下命令更新所有涉及表的统计信息（替换SCHEMA_NAME为你的实际schema名称）：
```
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'A', CASCADE => TRUE, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE);
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'B', CASCADE => TRUE, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE);
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'C', CASCADE => TRUE, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE);
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA_NAME', 'D', CASCADE => TRUE, ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE);
```
CASCADE => TRUE会同时收集索引的统计信息，AUTO_SAMPLE_SIZE让Oracle自动选择合适的采样比例，保证统计信息的准确性。
替换单字段索引为复合索引
你的连接条件是FACT_KEY + ENTITY_ID的组合，单字段索引无法高效支持多列连接条件。建议为各表创建匹配连接条件的复合索引：
- 表A：CREATE INDEX IDX_A_FACT_ENTITY ON A(FACT_KEY, ENTITY_ID);
- 表B：CREATE INDEX IDX_B_FACT_ENTITY ON B(FACT_KEY, ENTITY_ID);
- 表C：由于还有CREATED_DT的过滤条件，建议创建CREATE INDEX IDX_C_FACT_ENTITY_DT ON C(FACT_KEY, ENTITY_ID, CREATED_DT);（把过滤字段放在复合索引末尾，利用索引覆盖过滤）
- 表D：CREATE INDEX IDX_D_FACT_ENTITY ON D(FACT_KEY, ENTITY_ID);

查看执行计划，分析连接方式选择
先执行以下命令获取执行计划，明确优化器的选择：

EXPLAIN PLAN FOR
SELECT * 
FROM TABLE A 
LEFT OUTER JOIN B ON A.FACT_KEY=B.FACT_KEY AND A.ENTITY_ID=B.ENTITY_ID 
LEFT OUTER JOIN C ON A.FACT_KEY=C.FACT_KEY AND A.ENTITY_ID=C.ENTITY_ID AND C.CREATED_DT BETWEEN 201611 AND 201712 
LEFT OUTER JOIN D ON A.FACT_KEY=D.FACT_KEY AND A.ENTITY_ID=D.ENTITY_ID;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

对于超大表连接，Oracle默认可能选择哈希连接（Hash Join），这种连接方式下索引通常不会被使用（因为哈希连接是将驱动表数据加载到内存哈希表，再探测被连接表）。如果执行计划显示是哈希连接，不一定是坏事——哈希连接在大表连接时往往比嵌套循环（需要索引）更高效。但如果确认嵌套循环更适合你的场景，可以尝试用查询提示引导优化器，比如：

SELECT * 
FROM TABLE A 
LEFT OUTER JOIN B /*+ USE_NL(B) INDEX(B IDX_B_FACT_ENTITY) */ ON A.FACT_KEY=B.FACT_KEY AND A.ENTITY_ID=B.ENTITY_ID 
LEFT OUTER JOIN C /*+ USE_NL(C) INDEX(C IDX_C_FACT_ENTITY_DT) */ ON A.FACT_KEY=C.FACT_KEY AND A.ENTITY_ID=C.ENTITY_ID AND C.CREATED_DT BETWEEN 201611 AND 201712 
LEFT OUTER JOIN D /*+ USE_NL(D) INDEX(D IDX_D_FACT_ENTITY) */ ON A.FACT_KEY=D.FACT_KEY AND A.ENTITY_ID=D.ENTITY_ID;

注意：查询提示是最后手段，只有在你确定索引连接更优时使用，否则优先依赖CBO的选择。

检查索引有效性
确认你的索引没有被禁用或失效，执行以下查询：

SELECT INDEX_NAME, STATUS, TABLE_NAME 
FROM USER_INDEXES 
WHERE TABLE_NAME IN ('A', 'B', 'C', 'D') 
  AND INDEX_NAME LIKE '%FACT_KEY%'; -- 替换为你的索引名称关键词

如果索引状态不是VALID，需要重建索引：

ALTER INDEX IDX_A_FACT_ENTITY REBUILD;

考虑分区表优化（可选）
对于表C的CREATED_DT时间字段，以及其他大表，如果还没有分区，可以考虑按时间或FACT_KEY/ENTITY_ID做分区。分区表配合分区索引可以让查询只扫描目标分区，大幅减少数据扫描量，同时也会让优化器更倾向于使用索引。

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