oracle两表查询分页,Oracle分页查询语句（二）

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

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

表已创建。

SQL> SELECT COUNT(*) FROM T;

COUNT(*)

----------

457992

首先比较两种分页方法的区别：

SQL> SET AUTOT ON

SQL> COL OBJECT_NAME FORMAT A30

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T')

PL/SQL 过程已成功完成。

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

2085 ALL_ASSOCIATIONS

4997 ALL_AUDIT_POLICIES

4005 ALL_BASE_TABLE_MVIEWS

5753 ALL_CAPTURE

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 ptimizer=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)

Statistics

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

0 recursive calls

0 db block gets

8979 consistent gets

7422 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)

10 rows processed

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 <= 20

10 )

11 WHERE RN >= 11;

OBJECT_ID OBJECT_NAME

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

5807 ALL_APPLY_PROGRESS

1769 ALL_ARGUMENTS

2085 ALL_ASSOCIATIONS

4997 ALL_AUDIT_POLICIES

4005 ALL_BASE_TABLE_MVIEWS

5753 ALL_CAPTURE

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 ptimizer=CHOOSE (Cost=864 Card=20 Bytes=1840)

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

2 1 COUNT (STOPKEY)

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

Statistics

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

0 recursive calls

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)

10 rows processed

二者执行效率相差很大，一个需要8000多逻辑读，而另一个只需要5个逻辑读。观察二者的执行计划可以发现，两个执行计划唯一的区别就是第二个查询在COUNT这步使用了STOPKEY，也就是说，Oracle将ROWNUM <= 20推入到查询内层，当符合查询的条件的记录达到STOPKEY的值，则Oracle结束查询。

因此，可以预见，采用第二种方式，在翻页的开始部分查询速度很快，越到后面，效率越低，当翻到最后一页，效率应该和第一种方式接近。

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;

OBJECT_ID OBJECT_NAME

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

7128 XCF_I_HANDLE_STATUS

7126 XCF_P

7127 XCF_U1

7142 XDF

7145 XDF_I_DF_KEY

7146 XDF_I_HANDLE_STATUS

7143 XDF_P

7144 XDF_U1

TEST.YANGTINGKUN

TEST4.YANGTINGKUN

YANGTK.YANGTINGKUN

已选择11行。

Execution Plan

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

0 SELECT STATEMENT ptimizer=CHOOSE (Cost=864 Card=457990 Bytes=42135080)

1 0 VIEW (Cost=864 Card=457990 Bytes=42135080)

2 1 COUNT (STOPKEY)

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

Statistics

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

0 recursive calls

0 db block gets

8979 consistent gets

7423 physical reads

0 redo size

680 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)

11 rows processed