Oracle rownum 与 offset

rownum

rownum 主要有两类用处

• 处理 top N ；
• 分页查询；

rownum 的工作机制

rownum 是查询中的伪列，从 1 开始计数。

A ROWNUM value is assigned to a row after it passes the predicate phase of the query but before the query does any sorting or aggregation. Also, a ROWNUM value is incremented only after it is assigned .

rownum 的值是在行记录通过了查询的过滤阶段、在 排序 或聚合之前被赋值。rownum 只有在被赋值之后才会递增。 select * from t where ROWNUM > 1; 是永远查不到记录的。

查询的处理步骤大概如下：

1. from/where 首先执行；

2. rownum 被递增、赋值给 from/where 子句输出的每一行；

3. 执行 select 子句；

4. 执行 group by 子句；

5. 执行 having ；

6. 执行 order by 。

top N 查询的正确写法：

select * from (select * from emp order by sal desc) where ROWNUM <= 5;

Oracle 对这类 rownum top N 查询有个优化，不会排序整个结果集，而是利用 N 个元素的数组空间进行排序，节省了排序需要的空间（如果结果集太大，还得借助磁盘进行排序）。

分页的通用写法：

select *
  from (select /*+ FIRST_ROWS(n) */
         a.*, ROWNUM rnum
          from (your_query_goes_here, with order by) a
         where ROWNUM <= :MAX_ROW_TO_FETCH)
 where rnum >= :MIN_ROW_TO_FETCH;

这个写法会受益于前面提到的 top N 优化。

注意：这个分页查询的 order by 语句应该基于唯一性的列进行排序，如果排序列的重复值太多，排序的结果可能会混乱。

offset

在 Oracle 12c，可以通过 offset 进行分页：

offset 语法：

OFFSET { integer-literal | ? } {ROW | ROWS}
FETCH { FIRST | NEXT } [integer-literal | ? ] {ROW | ROWS} ONLY

第一条记录的 offset 是 0。如果没有与 order by 子句一起使用，则结果的顺序一般是记录插入的顺序。

-- 表示从第20条开始取下10条记录
SELECT * 
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;


-- 跳过前 100 行，如果表少于 101 条记录，结果集将是空的
SELECT * FROM T OFFSET 100 ROWS;