Oracle单查询中对MEDIUM_B同时执行全表扫描与唯一索引扫描的原因

查询语句 #

SELECT COUNT(LARGE_A.id_a), SUM(LARGE_A.b_integer)
FROM LARGE_A
INNER JOIN MEDIUM_A ON LARGE_A.id_a = MEDIUM_A.id
INNER JOIN MEDIUM_B ON LARGE_A.id_b = MEDIUM_B.id
WHERE
    MEDIUM_A.a_varchar2 LIKE 'Example%' AND
    EXTRACT(YEAR FROM MEDIUM_B.a_datetime) = 2000 AND
    LARGE_A.a_integer BETWEEN 0 AND 1000;

表结构与数据量 #

• LARGE_A表：100万行，主键为复合键(id_a, id_b)，字段类型可从名称推断（如id_a为整数，b_integer为整数）
• MEDIUM_A表：10万行，主键id为整数，配有非聚簇主键索引PK(MEDIUM_A)，a_varchar2为字符串类型
• MEDIUM_B表：10万行，主键id为整数，配有非聚簇主键索引PK(MEDIUM_B)，a_datetime为日期时间类型

实际执行计划（来自V$SQL视图） #

SELECT STATEMENT                        
|SORT AGGREGATE                         
||HASH JOIN                             
|||NESTED LOOPS                         
||||NESTED LOOPS                        
|||||STATISTICS COLLECTOR                
||||||NESTED LOOPS                      
|||||||TABLE ACCESS FULL                LARGE_A
|||||||TABLE ACCESS BY INDEX ROWID      MEDIUM_A
||||||||INDEX UNIQUE SCAN               PK(MEDIUM_A)
|||||INDEX UNIQUE SCAN                  PK(MEDIUM_B)
||||TABLE ACCESS BY INDEX ROWID         MEDIUM_B
|||TABLE ACCESS FULL                    MEDIUM_B

疑问 #

为何优化器先通过PK(MEDIUM_B)索引逐条查询记录、用ROWID获取行数据后，又执行MEDIUM_B的全表扫描？

1. 多路径分支的合并逻辑
   这个执行计划是优化器生成的两个独立访问路径分支，最终通过HASH JOIN合并结果：

   • 上方分支：从LARGE_A全表扫描出发，通过嵌套循环依次关联MEDIUM_A（利用主键索引快速匹配id_a）和MEDIUM_B（通过主键索引匹配id_b，再用ROWID读取完整行），这部分是处理LARGE_A过滤后的数据关联逻辑。
   • 下方分支：直接全表扫描MEDIUM_B，目的是单独筛选出满足EXTRACT(YEAR FROM a_datetime) = 2000的记录。

2. 过滤条件的拆分处理
   EXTRACT(YEAR FROM MEDIUM_B.a_datetime) = 2000这个条件无法通过仅包含主键id的PK(MEDIUM_B)索引过滤。优化器选择双路径结合的方式：

   • 一是通过LARGE_A关联MEDIUM_B时，利用主键索引快速定位关联行，但这部分行不保证满足年份条件；
   • 二是单独全表扫描MEDIUM_B筛选年份符合要求的记录，再通过HASH JOIN将两个分支的结果合并，确保最终返回的MEDIUM_B行同时满足主键关联和年份过滤的双重要求。

3. 成本估算的最优选择
   优化器认为这种“双路径+哈希连接”的总执行成本最低：一方面LARGE_A的a_integer BETWEEN 0 AND 1000和MEDIUM_A的前缀匹配过滤后，返回的数据量不大，嵌套循环关联MEDIUM_B的成本可控；另一方面10万行的MEDIUM_B全表扫描成本也不高，两者结合的代价比单一路径（比如先筛选MEDIUM_B年份再关联其他表）更优。

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