Oracle左连接主键未选关联表字段时执行计划差异咨询

问题原因分析 #

你的两个查询执行计划差异的核心是Oracle优化器的连接消除（Join Elimination）逻辑是否触发：

• 针对t2的查询：优化器判断左连接不会影响最终结果（既不会过滤t表的行，也不需要获取t2的字段），因此自动消除了连接，直接扫描t表返回结果。这种情况通常满足两个条件：

  • 统计信息显示t.t2_id的所有非空值都存在于t2.id（主键）中，或者t.t2_id空值率极高，连接无实际意义；
  • 优化器确认左连接不会改变结果集的行数和内容。

• 针对t1的查询：优化器未触发连接消除，选择哈希连接，可能的原因包括：

  1. 缺少外键约束：你未建立t.t1_id到t1.id的外键，优化器无法确定t.t1_id的非空值一定存在于t1.id中，因此认为需要执行连接来确保左连接的语义（即使最终不选择t1的字段）；
  2. 统计信息不准确：t或t1的统计信息过时，导致优化器误判连接的代价，认为哈希连接比直接扫描t表更高效；
  3. 数据量比例影响：t1的行数（400万）接近t表的三分之一，优化器可能认为哈希连接的内存代价和时间代价在可接受范围内，未触发消除逻辑。

解决方案与优化建议 #

1. 建立外键约束（最推荐） #

如果t.t1_id和t.t2_id确实是分别引用t1.id和t2.id的外键，创建外键约束可以让优化器明确表间关联关系，自动消除不必要的左连接：

-- 给t.t1_id创建外键
ALTER TABLE t ADD CONSTRAINT fk_t_t1 FOREIGN KEY (t1_id) REFERENCES t1(id);
-- 给t.t2_id创建外键
ALTER TABLE t ADD CONSTRAINT fk_t_t2 FOREIGN KEY (t2_id) REFERENCES t2(id);

2. 更新统计信息 #

如果统计信息过时，优化器会做出错误决策，执行以下命令更新表的统计信息：

-- 更新t表及索引的统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '你的用户名', TABNAME => 't', CASCADE => TRUE);
-- 更新t1表及索引的统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '你的用户名', TABNAME => 't1', CASCADE => TRUE);
-- 更新t2表及索引的统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(OWNNAME => '你的用户名', TABNAME => 't2', CASCADE => TRUE);

3. 针对多左连接场景的优化 #

对于你提到的数十个左连接、BI用户选择不同字段的场景，可采取以下措施：

• 视图封装常用查询：创建视图时提前消除不必要的连接，BI用户直接查询视图即可避免冗余连接；
• 引导用户查询规范：提醒用户仅在需要某表字段时才添加对应的左连接，减少无意义的表关联；
• 使用优化器提示（可选）：如果优化器仍未消除冗余连接，可在查询中添加/*+ ELIMINATE_JOIN(t1) */提示强制消除连接（需确认Oracle版本支持该提示）。

内容的提问来源于stack exchange，提问作者Room'on