Oracle无WHERE条件自LEFT JOIN行数少于原表的原因及优化方案

• 最常见的诱因是行数统计使用了非实时的统计信息估值：Oracle数据字典user_tables/all_tables中的num_rows字段是统计信息采样得到的估算值，并非实时准确的行数。你看到的原表27236296行、新表27209850行大概率是估值偏差，建议先执行以下语句获取真实行数验证：

-- 查原表真实行数
SELECT COUNT(*) FROM schema1.table_old;
-- 查新表真实行数
SELECT COUNT(*) FROM schema1.table_new;

如果两者实际行数一致，仅为统计信息未更新导致的显示偏差，不存在逻辑问题。

• 若真实行数确实减少，排查以下少见场景：
  1. 原表table_old配置了虚拟私有数据库（VPD）行级安全策略，不同操作场景下可见的行范围不一致，导致CTAS（建表语句）读取的行数少于直接查询的行数
  2. 你使用的Oracle版本存在CTAS关联分组查询的已知bug，可通过更换写法验证
  3. 原表存在数据坏块，建表时触发了坏块跳过逻辑导致少行

你当前的SQL写法逻辑上是正确的，没有语法错误，LEFT JOIN本身不会过滤左表数据。但该写法性能较低：需要对table_old执行两次全表扫描，还要额外做一次关联操作，千万级数据量下资源消耗、执行耗时都偏高。

推荐使用Oracle分析函数（窗口函数）实现需求，仅需要一次全表扫描，无需关联操作，性能提升非常明显，且从逻辑上完全保证返回行数和原表一致：

CREATE TABLE schema1.table_new AS
SELECT 
    t.*,
    MAX(age_at_midmonth) OVER (PARTITION BY person_id) AS current_age
FROM schema1.table_old t;

如果是不支持分析函数的极老版本Oracle，可保留原有写法，优先排查行数统计偏差问题即可。

内容的提问来源于stack exchange，提问作者PJ T. de Barros