Oracle 11g中用row_number() over(order by null)替代rownum是否安全？该行为是设计使然吗？

嘿，这个问题问得很专业，我来给你把细节掰明白：

1. 能否安全用row_number() over (order by null)替代rownum？ #

答案是分场景，但你给出的嵌套查询写法是一种合理的替代方式，尤其当你需要在排序后的结果集上生成连续行号时。

先明确两者的核心差异：

• rownum是Oracle的伪列，它是在查询返回结果集的过程中逐行分配的——也就是说，它的顺序依赖于Oracle执行计划中数据的访问顺序，如果你直接在外层加order by，rownum会是排序前的序号（比如select rownum, * from c_invoice order by dateinvoiced desc里的rownum是乱的）。
• row_number() over (order by null)是窗口函数，它会在窗口范围内（这里是整个结果集）按照输入行的顺序分配序号。当你把它放在带order by的子查询里时，它会基于排序后的行顺序生成连续的行号，这刚好解决了直接用rownum加排序的问题。

你写的语句：

select rownum, i.* from ( 
  select row_number() over (order by null) as rnum, i.* from c_invoice i order by i.dateinvoiced desc 
) i;

这里内层的order by dateinvoiced desc先把数据排序，然后row_number()基于这个排序后的结果生成序号，外层的rownum其实和内层的rnum值是一致的（当然你其实可以只保留其中一个）。这种用法在11g里是稳定的，可以安全使用。

2. 这个行为是设计使然还是巧合？ #

这绝对是设计使然，不是巧合：

• Oracle官方文档明确说明，窗口函数的order by子句可以指定null，表示不需要对窗口内的行进行显式排序。此时，Oracle会按照数据被窗口处理的自然顺序（也就是输入到窗口的行顺序）来分配row_number的值。
• 当你的子查询包含order by时，虽然理论上Oracle优化器在某些场景下可能忽略无限制的子查询排序，但如果窗口函数依赖这个输入顺序，优化器会保留排序步骤，确保row_number()基于排序后的行生成序号——这是Oracle优化器的设计逻辑之一。

不过要提一个小注意点：如果你的查询没有显式的order by，row_number() over (order by null)和rownum的序号都依赖于执行计划的访问顺序（比如全表扫描的顺序、索引扫描的顺序），这个顺序在数据分布或执行计划变化时可能改变，但这是两者共有的特性，不是row_number()的问题。

内容的提问来源于stack exchange，提问作者Ionuț U