Oracle rownum 与 offset

主要来自:
On ROWNUM and Limiting Results
The result offset and fetch first clauses

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;

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

此站点使用Akismet来减少垃圾评论。了解我们如何处理您的评论数据