Oracle 迁移 PostgreSQL 踩坑：ROWNUM 与 DISTINCT 执行顺序差异导致 SQL 结果不一致

文章目录引言一次诡异的数据丢失排查一、现象复现同样的 SQL不同的结果在 KES / Oracle 中的执行过程在 PostgreSQL 中的执行过程二、原理剖析执行优先级的致命差异2.1 KES / OracleROWNUM 的先截后去2.2 PostgreSQLLIMIT 的先去后截2.3 执行计划差异ROWNUM 阻断子查询提升三、避坑指南如何写出跨库一致的 SQL方案一嵌套子查询推荐方案二明确你的业务意图方案三使用窗口函数最精确的控制四、总结同样一条 SQL换个数据库跑行数不一样了。这不是玄学是执行优先级的锅。引言一次诡异的数据丢失排查上周一位从 Oracle 迁移到金仓数据库 KES 的开发者在群里抛出一个问题“我的查询明明写了ROWNUM 10为什么返回的结果有时候是 7 行、8 行就是不到 10 行而且同样的 SQL 在同事的 PostgreSQL 上跑偏偏返回的就是 10 行。”他跑的 SQL 是这样的SELECTDISTINCTuser_idFROMaccess_logWHERErownum10;access_log表存储的是用户访问日志同一个user_id可能出现在多行中。他的本意是取前 10 个不重复的用户。但实际结果却让人困惑。如果你也遇到过类似的问题或者你正在从 Oracle 迁移到 KES / PostgreSQL这篇文章将帮你彻底理清背后的执行优先级差异避免在后续开发中踩同样的坑。一、现象复现同样的 SQL不同的结果让我们用一个简单的数据集来复现这个现象。假设access_log表的前 15 行数据如下rowiduser_id1A2A3B4C5A6D7E8B9F10G11H12C13I14J15K执行SELECT DISTINCT user_id FROM access_log WHERE rownum 10;时在 KES / Oracle 中的执行过程先取 10 行扫描前 10 行物理记录rowid 1-10后去重对这 10 行做DISTINCT得到 A、B、C、D、E、F、G结果7 行而非 10 行在 PostgreSQL 中的执行过程PG 使用LIMIT而非ROWNUM等价 SQL 为SELECT DISTINCT user_id FROM access_log LIMIT 10;先去重对全表做DISTINCT得到所有不重复的 user_id后取 10 行对去重后的结果取前 10 个结果10 行恰好 10 个不重复 user_id二、原理剖析执行优先级的致命差异2.1 KES / OracleROWNUM 的先截后去在 KES 和 Oracle 中ROWNUM是一个动态生成的伪列。它的赋值发生在数据读取阶段早于DISTINCT、ORDER BY等操作。执行顺序可以概括为全表扫描 → 逐行赋予 ROWNUM → 过滤 ROWNUM 条件 → DISTINCT 去重 → 返回结果关键问题在于ROWNUM 10在去重之前就截断了数据。如果前 10 行物理记录中存在大量重复值去重后的结果自然会少于 10 行。用流程图表述原始 10 行: A A B C A D E B F G ↓ DISTINCT 去重 结果 7 行: A B C D E F G2.2 PostgreSQLLIMIT 的先去后截PostgreSQL 的LIMIT作用于最终结果集。执行顺序为全表扫描 → DISTINCT 去重 → LIMIT 截取前 N 行 → 返回结果这种语义更符合大多数开发者的直觉——“我要 10 个不重复的值”。2.3 执行计划差异ROWNUM 阻断子查询提升更深入地说ROWNUM的存在还会影响优化器的决策。在 KES / Oracle 中当子查询内部引用了ROWNUM时外部查询的过滤条件无法下推到子查询中这一优化技术称为子查询提升或Pull-up。这意味着SELECT*FROM(SELECTDISTINCTuser_idFROMaccess_logWHERErownum10)tWHEREt.user_idA;在这条 SQL 中WHERE t.user_id A这个外部过滤条件无法被下推到子查询内部。优化器被迫先对子查询做全表扫描取前 10 行然后在外层做过滤。如果数据量很大这可能导致不必要的性能损耗。相比之下如果将ROWNUM替换为LIMITPostgreSQL 的优化器通常可以将外部条件下推从而减少扫描范围。三、避坑指南如何写出跨库一致的 SQL方案一嵌套子查询推荐如果你确实需要先取 N 行再去重的 Oracle / KES 语义但希望在 PG 上得到一致结果使用嵌套子查询-- KES / Oracle / PG 均可执行行为一致SELECTDISTINCTuser_idFROM(SELECTuser_idFROMaccess_logWHERErownum10)t;或者在 PG 中SELECTDISTINCTuser_idFROM(SELECTuser_idFROMaccess_logLIMIT10)t;方案二明确你的业务意图问自己一个问题你的业务到底想要什么业务意图KES / Oracle 写法PG 写法取前 N 行物理记录然后去重SELECT DISTINCT ... WHERE rownum N用子查询 LIMIT取 N 个不重复的值嵌套子查询 或ROW_NUMBER()SELECT DISTINCT ... LIMIT N大多数情况下开发者的真实意图是后者——“我要 N 个不重复的值”。在这种情况下KES / Oracle 中的DISTINCT ROWNUM组合其实是写错了。方案三使用窗口函数最精确的控制如果你需要对排序、去重、截断的顺序有完全精确的控制使用窗口函数是最可靠的方式-- 先按 user_id 分组取每个 user_id 的最小 rowid然后取前 10 个SELECTuser_idFROM(SELECTuser_id,ROW_NUMBER()OVER(ORDERBYMIN(rowid))ASrnFROMaccess_logGROUPBYuser_id)tWHERErn10;这种写法在所有数据库中行为一致且语义最为明确。四、总结DISTINCT ROWNUM的执行优先级陷阱本质上是不同数据库对行号伪列赋值时机的设计差异。关键要点回顾KES / OracleROWNUM赋值在DISTINCT之前——先截取后去重结果可能少于 N 行。PostgreSQLLIMIT作用于最终结果——先去重后截取结果恰好 N 行。ROWNUM 阻断子查询提升引用ROWNUM的子查询外部过滤条件无法下推可能导致全表扫描。最佳实践明确业务意图选择正确的写法跨库兼容场景下使用嵌套子查询或窗口函数避免将DISTINCT ROWNUM作为取 N 个不重复值的手段记住一条铁律永远不要用ROWNUM去做你真正想做之外的事情。它的行为高度依赖于它在 SQL 中的位置和数据库引擎的实现细节。当你对执行顺序有一丝不确定时窗口函数永远是最安全的选择。本文基于金仓数据库 KingbaseES V9 / Oracle 19c / PostgreSQL 15 编写。