Oracle的分页查询详解及效率.doc

分页查询格式：

SELECT * FROM

(

SELECT A.*, ROWNUM RN

FROM (SELECT * FROM TABLE_NAME) A

WHERE ROWNUM <= 40

)

WHERE RN >= 21

其中最内层的查询 SELECT * FROM TABLE_NAME 表示不进行翻页的原始查询语句。

第二层的 WHERE ROWNUM <= 40 语句，在查询的最外层控制分页的最小值和最大值。

这是，查询语句如下：

SELECT * FROM

(

SELECT A.*, ROWNUM RN

FROM (SELECT * FROM TABLE_NAME) A

)

WHERE RN BETWEEN 21 AND 40

对比这两种写法，绝大多数的情况下，第一个查询的效率比第二个高得多。

这是由于 CBO 优化模式下，Oracle 可以将外层的查询条件推到内层查询中，以提高

是所有满足条件的数据，而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成，

显然这个效率要比第一个查询低得多。

上面分析的查询不仅仅是针对单表的简单查询，对于最内层查询是复杂的多表联合查询

或最内层查询包含排序的情况一样有效。

这里就不对包含排序的查询进行说明了，下一篇文章会通过例子来详细说明。下面简单

讨论一下多表联合的情况。对于最常见的等值表连接查询，CBO 一般可能会采用两种连接

方式 NESTED LOOP 和 HASH JOIN（MERGE JOIN 效率比 HASH JOIN 效率低，一般

CBO 不会考虑）。在这里，由于使用了分页，因此指定了一个返回的最大记录数，NESTED

LOOP 在返回记录数超过最大值时可以马上停止并将结果返回给中间层，而 HASH JOIN

必须处理完所有结果集（MERGE JOIN 也是）。那么在大部分的情况下，对于分页查询选

FROM (SELECT * FROM TABLE_NAME) A

WHERE ROWNUM <= 40

)

WHERE RN >= 21

这篇文章用几个例子来说明分页查询的效率。首先构造一个比较大的表作为测试表：

SQL> CREATE TABLE T AS SELECT * FROM DBA_OBJECTS, DBA_SEQUENCES;

表已创建。

SQL> SELECT COUNT(*) FROM T;

COUNT(*)

----------

SQL> SELECT OBJECT_ID, OBJECT_NAME

2 FROM

3 (

4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME

5 FROM

6 (

7 SELECT OBJECT_ID, OBJECT_NAME FROM T

8 )

9 )

10 WHERE RN BETWEEN 11 AND 20;

OBJECT_ID OBJECT_NAME

---------- ------------------------------

5807 ALL_APPLY_PROGRESS

1769 ALL_ARGUMENTS

5765 ALL_CAPTURE_PREPARED_SCHEMAS

5769 ALL_CAPTURE_PREPARED_TABLES

已选择 10 行。

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=864 Card=457992 Bytes=42135264)

1 0 VIEW (Cost=864 Card=457992 Bytes=42135264)

2 1 COUNT

3 2 TABLE ACCESS (FULL) OF 'T' (Cost=864 Card=457992 Bytes=9617832)

0 redo size

758 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

10 rows processed

SQL> SELECT OBJECT_ID, OBJECT_NAME

2 FROM

3 (

4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME

5 FROM

6 (

9 WHERE ROWNUM <= 20

10 )

11 WHERE RN >= 11;

1769 ALL_ARGUMENTS

2085 ALL_ASSOCIATIONS

5757 ALL_CAPTURE_PARAMETERS

5761 ALL_CAPTURE_PREPARED_DATABASE

5765 ALL_CAPTURE_PREPARED_SCHEMAS

5769 ALL_CAPTURE_PREPARED_TABLES

已选择 10 行。

Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=864 Card=20 Bytes=1840)

1 0 VIEW (Cost=864 Card=20 Bytes=1840)

2 1 COUNT (STOPKEY)

0 db block gets

5 consistent gets

0 physical reads

0 redo size

758 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

用了 STOPKEY，也就是说，Oracle 将 ROWNUM <= 20 推入到查询内层，当符合查询

因此，可以预见，采用第二种方式，在翻页的开始部分查询速度很快，越到后面，效率

越低，当翻到最后一页，效率应该和第一种方式接近。

SQL> SELECT OBJECT_ID, OBJECT_NAME

2 FROM

3 (

4 SELECT ROWNUM RN, OBJECT_ID, OBJECT_NAME

5 FROM

6 (

7 SELECT OBJECT_ID, OBJECT_NAME FROM T

8 )

9 WHERE ROWNUM <= 457990

10 )

11 WHERE RN >= 457980;

7145 XDF_I_DF_KEY

7146 XDF_I_HANDLE_STATUS

7143 XDF_P

7144 XDF_U1

TEST.YANGTINGKUN

TEST4.YANGTINGKUN

YANGTK.YANGTINGKUN

已选择 11 行。