Oracle 使用row_number() over (order by null) 作为rownum替代是安全的吗？

askTom 2018-03-23

562

问题描述

嗨，

我做了一些测试，似乎row_number() over (按null排序) 可以用作oracle的rownum伪列的替代方法。这种行为是设计的还是只是巧合？

我之所以尝试这样做，是因为使用rownum (与row_number() 相比) 分页时某些查询非常慢，并且我不能简单地将order by子句移动到row_number() 中

有关示例，请参见livesql链接。

专家解答

从某种意义上说，我们不需要真正进行排序，例如

select row_number() over (order by null) as rnum, i.* 
from invoice i 

Statistics
----------------------------------------------------
          1  recursive calls
          0  db block gets
         71  consistent gets
          0  physical reads
          0  redo size
      30365  bytes sent via SQL*Net to client
       1334  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
       1000  rows processed复制

但是我想看到一些确切的证据表明它比rownum快。

SQL>   create table t pctfree 0 as
  2     select rownum x from
  3  ( select 1 from dual connect by level <= 10000 ),
  4  ( select 1 from dual connect by level <= 10000 );

Table created.

SQL> set timing on
SQL> begin
  2  for i in (
  3          select rownum, t1.* from (
  4              select t.*
  5              from t
  6              order by t.x
  7          ) t1
  8  )
  9  loop
 10    null;
 11  end loop;
 12  end;
 13  /

PL/SQL procedure successfully completed.

Elapsed: 00:01:00.70
SQL>
SQL> set timing on
SQL> begin
  2  for i in (
  3              select row_number() over (order by null) as rnum, t.*
  4              from t
  5              order by t.x
  6  )
  7  loop
  8    null;
  9  end loop;
 10  end;
 11  /

PL/SQL procedure successfully completed.

Elapsed: 00:01:05.19
复制