Oracle Paging：解析函数 ROW_NUMBER() VS ROWNUM

Question

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

分析函数 - ROW_NUMBER()

• Oracle 10G

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

  选择列A， 
      B栏
  FROM (选择列A,
             B 栏，
             row_number() over (按 columnB 排序) rn
        从表）
  LOW_LIMIT 和 OFFSET 之间的位置；
  

ROWNUM

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

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

  选择 * FROM (
    SELECT rownum rn, a.* 
    从（
      选择 A 列、B 列
      从表 
      按 B 列排序
    ) 一个 
    其中 rn <= 偏移量
  ）
  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 更有效的排序操作。我不确定窗口排序推送排名是如何工作的，但它似乎以类似的方式工作。

运行两个查询后查看 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 没有。也许我可以让它使用带有一些提示的索引——这是你可以尝试的其他东西。

Answer 2

除了答案中提到的其他差异之外，您还应该考虑性能。这里有一篇非权威但很有趣的报告，比较了各种分页方式，其中ROWNUM的使用与ROW_NUMBER() OVER()相比：

https://web.archive.org/web/20160901191310/http://www.inf.unideb.hu:80/~gabora/pagination/results.html

Answer 3

生成您自己的经验结果：

-- Create test table
CREATE TABLE test_large_tab (
  tlt_id   NUMBER,
  tlt_data VARCHAR2(50)
);

-- Load with data
BEGIN
   FORALL i IN 1 .. 1000000
      INSERT INTO test_large_tab
      (
       tlt_id,
       tlt_data
      )
      VALUES
      (
       i,
       TO_CHAR(sysdate-i, 'FMMon ddth, YYYY')
      );
END;

当然，您可以增加表的大小以满足您的测试目的！

设置计时并针对大表运行两个查询。

更改表结构以更好地适合您的测试，因为您可能也希望为查询对某些列建立索引等。但本质上这是一个简单的测试，不会花费您很长时间来运行。

如果两者的时间大致相同，则使用最具可读性（因此最受支持）的版本。