oracle几种分页方法效率比较。

最新推荐文章于 2021-04-05 17:17:00 发布

原创最新推荐文章于 2021-04-05 17:17:00 发布 · 1.2k 阅读

0 ·

CC 4.0 BY-SA版权

oracle 专栏收录该内容

2 篇文章

订阅专栏

本文比较了Oracle数据库中三种不同的分页查询方法的效率，包括基于ROWNUM的查询、基于ROWNUM和BETWEEN的查询以及基于ROWID的查询。通过实际执行计划和性能测试，展示了每种方法的逻辑读次数和执行时间，强调了在分页查询中使用NESTED LOOP和STOPKEY关键字可以提高查询效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

第一种：

SELECT *
FROM (SELECT ROW_.*, ROWNUM ROWNUM_
FROM (SELECT *
FROM TABLE1
WHERE TABLE1_ID = XX
ORDER BY GMT_CREATE DESC) ROW_
WHERE ROWNUM <= 20)
WHERE ROWNUM_ >= 10;

第二种：

SELECT *
FROM (SELECT A.*, ROWNUM RN
FROM (SELECT *
FROM TABLE1
WHERE TABLE1_ID = XX
ORDER BY GMT_CREATE DESC) A)
WHERE RN BETWEEN 10 AND 20;

第三种：

SELECT *
FROM (SELECT RID
FROM (SELECT R.RID, ROWNUM LINENUM
FROM (SELECT ROWID RID
FROM TABLE1
WHERE TABLE1_ID = XX
ORDER BY GMT_CREATE DESC) R
WHERE ROWNUM <= 20)
WHERE LINENUM >= 10) T1,
TABLE1 T2
WHERE T1.RID = T2.ROWID;

第一种和第二种比较:

由于Oracle可以将外层的查询条件推到内层查询中，以提高内层查询的执行效率，但不能跨越多层。
对于第一个查询语句，第二层的查询条件WHERE ROWNUM <= 20就可以被Oracle推入到内层查询中，这样Oracle查询的结果一旦超过了ROWNUM限制条件，就终止查询将结果返回了。
而第二个查询语句，由于查询条件BETWEEN 10 AND 20是存在于查询的第三层，而Oracle无法将第三层的查询条件推到最内层（即使推到最内层也没有意义，因为最内层查询不知道RN代表什么）。因此，对于第二个查询语句，Oracle最内层返回给中间层的是所有满足条件的数据，而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成，显然这个效率要比第一个查询低得多。

第一种和第三种比较：

第三种有效的降低了回表操作，因为很多时候“回表“的开销是非常大的！逻辑读(consistent gets)相应的变少,性能相对提高很多，举例如下:

1.  基于rownum的写法：
********************************************************************************

SELECT  *
FROM    (
      select t.*,rownum rn from(
         SELECT  *
         FROM mytable
         ORDER BY
         id
         ) t
         where rownum<=2800010
      )
where rn>=2800001

call    count    cpu elapsed    disk    query current       rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       1    0.00    0.00       0       0       0          0
Execute    1    0.00    0.00       0       0       0          0
Fetch       2    1.33    2.03    16864    16866       0       10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       4    1.33    2.03    16864    16866       0       10

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 27

Rows    Row Source Operation
-------  ---------------------------------------------------
   10  VIEW  (cr=16866 pr=16864 pw=0 time=2037919 us)
2800010 COUNT STOPKEY (cr=16866 pr=16864 pw=0 time=2801613 us)
2800010 VIEW  (cr=16866 pr=16864 pw=0 time=1597 us)
2800010    TABLE ACCESS BY INDEX ROWID MYTABLE (cr=16866 pr=16864 pw=0 time=1592 us) ----此处TABLE ACCESS BY INDEX ROWID执行了2800010次
2800010    INDEX FULL SCAN PK_MYTABLE_ID (cr=6100 pr=6099 pw=0 time=1389 us)(object id 10827)

Elapsed times include waiting on following events:
  Event waited on                            Times Max. Wait  Total Waited
  ---------------------------------------- Waited  ----------  ------------
  SQL*Net message to client                      2       0.00       0.00
  db file sequential read                   16864       0.07       0.43
  SQL*Net message from client                   2       0.00       0.00
********************************************************************************

********************************************************************************

2. 基于rowid的写法：

select /*+ ordered use_nl(t1,t2) */
   t2.id,
      t2.paginator,
      t2.value
from (select rid
   from (select r.rid,rownum rn
               from(select rowid rid
                              from mytable t
                        order by id ) r
                        where rownum<=2800010)
      where rn >=2800001) t1,
      mytable t2
where t1.rid=t2.rowid

call    count    cpu elapsed    disk    query current       rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       1    0.00    0.01       0       0       0          0
Execute    1    0.00    0.00       0       0       0          0
Fetch       2    0.60    0.82    6100    6110       0       10
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       4    0.61    0.83    6100    6110       0       10

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 27

Rows    Row Source Operation
-------  ---------------------------------------------------
   10  NESTED LOOPS  (cr=6110 pr=6100 pw=0 time=827099 us)
   10 VIEW  (cr=6100 pr=6099 pw=0 time=827082 us)
2800010 COUNT STOPKEY (cr=6100 pr=6099 pw=0 time=436 us)
2800010    VIEW  (cr=6100 pr=6099 pw=0 time=431 us)
2800010    INDEX FULL SCAN PK_MYTABLE_ID (cr=6100 pr=6099 pw=0 time=427 us)(object id 10827)
   10 TABLE ACCESS BY USER ROWID MYTABLE (cr=10 pr=1 pw=0 time=93 us)   -------此处TABLE ACCESS BY INDEX ROWID执行了10次

在多表联合情况下： CBO一般可能会采用两种连接方式NESTED LOOP和HASH JOIN（MERGE JOIN效率比HASH JOIN效率低，一般CBO不会考虑）。在这里，由于使用了分页，因此指定了一个返回的最大记录数，NESTED LOOP在返回记录数超过最大值时可以马上停止并将结果返回给中间层，而HASH JOIN必须处理完所有结果集（MERGE JOIN也是）。那么在大部分的情况下，对于分页查询选择NESTED LOOP作为查询的连接方法具有较高的效率（分页查询的时候绝大部分的情况是查询前几页的数据，越靠后面的页数访问几率越小）

因此，如果不介意在系统中使用HINT的话，可以将分页的查询语句改写为：
SELECT /*+ FIRST_ROWS */ * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21