Oracle 中 ROWNUM、ROW_NUMBER 和分页查询有什么区别？

简化版

ROWNUM 是 Oracle 在结果返回过程中分配的伪列，常用于 Top-N；ROW_NUMBER() 是分析函数，按指定窗口和排序生成行号。分页时必须先排序再编号，否则结果不稳定。新版 Oracle 支持 offset ... fetch，但深分页仍要关注排序、索引和扫描成本。

详细版

常见区别：

ROWNUM 在行被取出时分配，不能直接写 where rownum > 10 获取后续行。
Top-N 常写成内层排序、外层限制 rownum <= N。
ROW_NUMBER() 可以按 order by 生成稳定行号，再按行号过滤。
offset fetch 语法更直观，但深分页仍然需要跳过大量行。

正确分页关键是确定排序字段，并让排序字段有稳定唯一的补充条件，如 order by created_at desc, id desc。

完整版教学

一、ROWNUM 是伪列，不是排序后的天然行号

ROWNUM 是 Oracle 在返回行时分配的伪列。它的分配发生在某些排序之前，因此直接把 ROWNUM 和 ORDER BY 放在同一层，很容易得到不符合预期的结果。

错误直觉：

select *
from employees
where rownum <= 10
order by salary desc;

这可能先取出任意 10 行，再对这 10 行排序，而不是取全表工资最高的 10 行。

记忆钩子：Top-N 要先排序再截断，ROWNUM 直接写外面才安全。

二、ROWNUM 做 Top-N 要用子查询

正确 Top-N 写法是内层先排序，外层再用 rownum 限制数量。

select *
from (
  select *
  from employees
  order by salary desc
)
where rownum <= 10;

这样语义是先按工资排序，再取前 10 行。面试里这是非常经典的 Oracle 问法。

三、为什么 rownum > 10 直接查不到

ROWNUM 是行通过条件时才分配的。第一行进来时，ROWNUM 是 1，如果条件是 rownum > 10，第一行不满足，被丢弃；下一行进来仍然是 1，继续不满足。

select *
from employees
where rownum > 10;

这条查询通常返回不了想要的“第 11 行之后”。分页要先在子查询中生成行号，再外层过滤。

四、ROW_NUMBER 更适合清晰分页

ROW_NUMBER() 是分析函数，可以按指定排序生成行号。它比 ROWNUM 更直观，也支持分组内编号。

select *
from (
  select e.*,
         row_number() over(order by salary desc, id desc) rn
  from employees e
)
where rn between 11 and 20;

写法	行号来源	适合场景	主要风险
`ROWNUM` 双层查询	取行过程伪列	老版本 Top-N、简单分页	写错层级会先截断后排序
`ROW_NUMBER()`	分析函数排序编号	复杂排序、分组 Top-N	大结果集排序成本高
`offset fetch`	标准分页语法	新版本简单分页	深分页仍要跳过前 N 行

这里先按工资和 ID 排序生成稳定行号，再取第 11 到 20 行。补上 id desc 是为了工资相同的行也有稳定顺序。

五、offset fetch 语法更现代但不解决深分页

较新版本 Oracle 支持：

select *
from employees
order by salary desc, id desc
offset 10 rows fetch next 10 rows only;

它更易读，但深分页仍然需要跳过前面大量行。比如 offset 100000 时，数据库仍要找到并跳过前 100000 行，成本不会凭空消失。

深分页要考虑 keyset pagination，也叫 seek pagination。

六、深分页更适合基于游标条件翻页

如果按 created_at desc, id desc 排序，下一页可以带上上一页最后一条的排序键，而不是 offset 很大。

select *
from orders
where (created_at < :last_created_at)
   or (created_at = :last_created_at and id < :last_id)
order by created_at desc, id desc
fetch next 20 rows only;

这个方式可以利用索引继续往后扫，避免跳过大量行。缺点是不能直接跳到第 1000 页，更适合信息流和列表翻页。

七、常见误区与追问

误区：ROWNUM 是 order by 后的行号。 ROWNUM 分配时机容易早于排序，Top-N 要用子查询。
误区：where rownum > 10 可以查第 10 行以后。 ROWNUM 从 1 开始分配，直接大于条件通常无法按预期工作。
误区：offset fetch 彻底解决分页性能。 语法更好，但深分页跳过成本仍在。
追问：ROWNUM 和 ROW_NUMBER 区别？ ROWNUM 是伪列，ROW_NUMBER 是分析函数，可按窗口排序编号。
追问：分页排序为什么要加唯一字段？ 防止排序键相同时跨页重复或遗漏。
追问：深分页怎么优化？ 用 keyset pagination、合适联合索引或限制可跳页范围。

八、面试中可以这样落地

如果问 Oracle 分页，可以给出三种写法：老版本 ROWNUM 双层查询，分析函数 ROW_NUMBER()，新版本 offset fetch。再强调排序稳定性和深分页优化。

select *
from (
  select t.*, rownum rn
  from (
    select *
    from orders
    order by created_at desc, id desc
  ) t
  where rownum <= 20
)
where rn > 10;

这类写法能解释 ROWNUM 的分配时机，也能展示你知道传统 Oracle 分页套路。

九、加强记忆

Oracle 分页记住“ROWNUM 先截断，排序要内层；ROW_NUMBER 先编号，外层再筛；offset fetch 易读但深分页仍贵”。稳定分页一定要有确定排序，深分页要考虑基于最后一条记录继续查。

简化版

详细版