Oracle使用索引做表关联时出现两次Nested Loops Join问题

问题原因 #

你看到的两次Nested Loops Join，大概率是优化器在完成索引层面的join后，额外多了一次回表取数的NL join：第一次NL是用两个表的索引完成关联条件匹配，拿到符合条件的行的rowid，第二次NL是根据rowid回表读取你查询语句中没有被索引覆盖的字段。

调整方案 #

• 优先创建覆盖索引：将你SQL语句中涉及的COURSE、PROFESSOR表的所有字段（包括SELECT列表、WHERE过滤条件、JOIN关联条件里的字段）都包含到对应表的索引中。优化器检测到所有需要的字段都能从索引直接获取时，就会跳过回表步骤，只保留一次NL join。
• 使用Hint强制执行路径：如果无法修改索引结构，可以在查询语句开头添加Hint指定执行逻辑，示例如下：

SELECT /*+ LEADING(course professor) USE_NL(course professor) INDEX(course 你的COURSE表索引名) INDEX(professor 你的PROFESSOR表索引名) */
-- 此处填写你原本的查询逻辑

注意替换语句中的表别名、索引名为你实际使用的名称。

• 先更新表统计信息：统计信息过时会导致优化器行数估算错误，误选多步NL的执行计划，执行以下命令更新统计信息后再验证执行计划：

-- 替换YOUR_SCHEMA为你实际的 schema 名称
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'YOUR_SCHEMA', TABNAME=>'COURSE', CASCADE=>TRUE);
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME=>'YOUR_SCHEMA', TABNAME=>'PROFESSOR', CASCADE=>TRUE);

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