在Oracle数据库里做分页，新手常常会直接把ROWNUM写在WHERE条件里，试图用大于和小于来截取第N到M条数据，结果要么一条都查不出来，要么顺序完全不对。真正可靠的写法是利用嵌套查询，先在内部子查询中完成排序和ROWNUM赋值，再在外部用 BETWEEN 或大于小于来截取区间。这种方式不仅符合Oracle优化器的执行逻辑，也能避免排序后行号错位的问题。
ROWNUM的底层赋值机制与常见误区
ROWNUM是Oracle在每次从结果集取行时动态分配的一个伪列，它从1开始，只有在数据行被选中返回给上层时才递增。这意味着如果你在单层查询中写 WHERE ROWNUM > 10，优化器先尝试取第一行并判断它是否大于10，因为此时ROWNUM还是1，不满足条件就被丢弃，接着下一行又变成新的1，永远无法满足大于10，最终返回空结果。很多人在调试时怎么都查不到数据，根源就在这里。
另一个误区是认为先ORDER BY再限制ROWNUM就安全。实际上在默认执行顺序中，ROWNUM的分配可能发生在排序之前，除非通过子查询强制先排序再生成行号。如果直接在包含ORDER BY的语句里写 AND ROWNUM <= 20，有可能取到的是未排序前的二十行，排序只是对这二十行生效，分页结果自然是错误的。只有把排序和ROWNUM生成包进内层，外层再截取，才能保证逻辑正确。
我们可以通过一段错误代码看清问题。下面这种写法在多数业务场景下都会返回空或乱序数据：
SELECT id, name FROM user_table WHERE ROWNUM >= 11 AND ROWNUM <= 20 ORDER BY create_time DESC;
这段SQL里，ROWNUM的限制在ORDER BY之前求值，而且大于条件直接导致无数据。即便改成 ROWNUM <= 20 而没有内层排序，也只是拿到物理存储的前二十行再做排序，并不是按时间最新的第二十行。
标准嵌套分页SQL模板与代码解析
最稳妥的写法是三层或两层嵌套：内层做排序和必要过滤，中间层用ROWNUM生成连续行号并限制上限，外层再截取下限。这样优化器会先按子查询逻辑把结果集排好，赋予行号，再由外层切出目标页。下面给出一个常用的两层写法，适用于大部分Oracle 11g及以前版本。
SELECT *
FROM (
SELECT t.*, ROWNUM rn
FROM (
SELECT id, name, create_time
FROM user_table
WHERE status = 1
ORDER BY create_time DESC
) t
WHERE ROWNUM <= 20
)
WHERE rn >= 11;
上面的SQL中，最内层先按创建时间倒序取出有效数据；中间层给每一行编上ROWNUM并只保留前二十行，极大减少了外层处理量；外层通过别名 rn 取出第十一到二十行。这种结构下，排序一定先于行号生成，分页绝对不会错乱。同时由于内层已经用 ROWNUM <= 20 截断，数据量越大越能体现出相比一次性取出全部再内存分页的优势。
如果使用的是Oracle 12c及以上，也可以采用标准SQL的OFFSET FETCH语法，可读性更好，但在复杂视图或老旧兼容环境中，嵌套ROWNUM依然是最通用的方案。OFFSET写法如下：
SELECT id, name, create_time FROM user_table WHERE status = 1 ORDER BY create_time DESC OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
这段SQL语义清晰，数据库自己处理分页逻辑。但在迁移老系统或某些报表工具不支持新语法时，前面讲的嵌套模板仍是首选。两者在正确索引支撑下性能接近，但嵌套写法对DBA调优更透明。
性能对比与索引设计建议
分页查询慢，往往不是因为ROWNUM本身，而是内层排序缺少索引。如果 ORDER BY create_time DESC 的列上没有索引，Oracle就要做全表扫描加排序操作，数据量到几十万行时第一页都会卡顿。建议在排序和过滤列上建组合索引，例如 CREATE INDEX idx_status_time ON user_table(status, create_time DESC)，这样内层子查询可以走索引避免排序。
| 写法 | 深翻页表现 | 可读性 | 版本要求 |
|---|---|---|---|
| 嵌套ROWNUM | 稳定，依赖索引 | 一般 | 所有版本 |
| OFFSET FETCH | 稳定，依赖索引 | 好 | 12c+ |
| 单层ROWNUM限制 | 错误或无数据 | 看似简单 | 不推荐 |
从实测来看，在百万级表且具备上述组合索引时，取第一页和取第一千页的耗时差距在两倍以内，因为索引可以定位起始点。如果没有索引，深翻页会让排序区爆满，响应时间呈数量级恶化。因此写分页SQL时，不能只抄模板，还要同步检查执行计划里有没有 INDEX RANGE SCAN 而不是 TABLE ACCESS FULL。
另外注意，在分页查询中尽量不要用 SELECT * 包裹过多宽列，内层只取业务需要的字段和排序键，能进一步降低逻辑读。配合绑定变量避免硬解析，高并发下的Oracle分页就能既准又快。写SQL时把ROWNUM当成必须嵌套处理的伪列，而不是普通字段，是写出很棒的Oracle分页语句的核心心法。