Oracle 分页 : Analytic function ROW_NUMBER() VS ROWNUM

Question

当网站需要进行分页时...哪种方法效果更好？

分析函数 - ROW_NUMBER()

• Oracle 10G

• http://www.oracle.com/technetwork/issue-archive/2007/07-jan/o17asktom-093877.html

  SELECT columnA, 
      columnB
  FROM (SELECT columnA,
             columnB,
             row_number() over (order by columnB) rn
        FROM table)
  WHERE rn BETWEEN LOW_LIMIT AND OFFSET;
  

ROWNUM

• http://www.oracle.com/technetwork/issue-archive/2007/07-jan/o56asktom-086197.html

• INMHO，我发现这种方法是一种更易于人类阅读的代码

  SELECT * FROM (
    SELECT rownum rn, a.* 
    FROM(
      SELECT columnA, columnB
      FROM table 
      ORDER BY columnB
    ) a 
    WHERE rn <= OFFSET
  )
  WHERE rnum >= LOW_LIMIT
  

  • 注意:我知道存在 RANK 和 DENSE_RANK 分析函数，但假设我只需要通过确定性查询进行分页。

  • 注释 2:要检索记录总数，我正在考虑使用单独的简单查询 count(*)

Answer 1

我觉得这个问题很有趣，所以我尝试了一些东西。

我有一个名为large_t 的表，其中包含大约 110 万行。

然后我有两个疑问:

select * 
from
(
  select rownum rnum, a.*
  from (
         select owner, object_name, object_id
         from large_t
         order by object_id
       ) a
  where rownum   <= 30      
) where rnum > 20;

还有

select *
from
(
select owner, object_name, object_id,
       row_number() over (order by object_id) rnum
from large_t
) where rnum > 20 and rnum <= 30;

如果您查看两个查询生成的计划，第一个查询有一个操作:

SORT ORDER BY STOPKEY

虽然分析查询包含一个名为

的操作

WINDOW SORT PUSHED RANK

SORT ORDER BY STOPKEY 是比普通 ORDER BY 更有效的排序操作。我不确定 WINDOW SORT PUSHED RANK 是如何工作的，但它似乎以类似的方式工作。

运行两个查询后查看 v$sql_workarea，两个查询都只需要 4096 字节的 sort_area。

相反，如果我在没有分页查询的情况下运行查询:

select owner, object_name, object_id
from large_t
order by object_id

那么需要的排序区域是37M，证明两个查询中的排序大致相同。

通常，如果您想有效地返回排序查询的前 N ​​个，您将需要在排序列上建立索引 - 这将阻止 Oracle 完全需要排序。因此，我在 OBJECT_ID 上创建了一个索引，然后再次解释了这两个查询。

这次第一个查询使用了索引，并在 0.2 秒内返回，而第二个查询没有使用新索引，速度慢了很多。

因此，我从快速分析中得出的结论是，在一般情况下，使用 rownum 进行过滤或分析 row_number 函数的执行效果大致相同。但是，rownum 示例自动开始使用我在表上创建的索引，而 row_number 没有。也许我可以让它使用带有一些提示的索引 - 这是你可以尝试的其他东西。