oracle物理分页查询,Oracle分页查询语句（七）

在文章的最后看一下ORDER BY STOPKEY和ORDER BY在翻页查询的最后几页的性能差异：

SQL> CREATE TABLE T AS SELECT A.* FROM DBA_OBJECTS A, DBA_USERS B, TAB;

表已创建。

SQL> SELECT COUNT(*) FROM T;

COUNT(*)

----------

458064

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

PL/SQL 过程已成功完成。

SQL> SET AUTOT TRACE

SQL> SET TIMING ON

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 ORDER BY TIMESTAMP

8 )

9 WHERE ROWNUM <= 20

10 )

11 WHERE RN >= 11;

已选择10行。

已用时间: 00: 00: 00.03

Execution Plan

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

0 SELECT STATEMENT ptimizer=CHOOSE (Cost=13888 Card=20 Bytes=1840)

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

2 1 COUNT (STOPKEY)

3 2 VIEW (Cost=13888 Card=458064 Bytes=36187056)

4 3 SORT (ORDER BY STOPKEY) (Cost=13888 Card=458064 Bytes=18780624)

5 4 TABLE ACCESS (FULL) OF 'T' (Cost=537 Card=458064 Bytes=18780624)

Statistics

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

0 recursive calls

0 db block gets

5579 consistent gets

0 physical reads

0 redo size

694 bytes sent via SQL*Net to client

503 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

1 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 ORDER BY TIMESTAMP

8 )

9 )

10 WHERE RN BETWEEN 11 AND 20;

已选择10行。

已用时间: 00: 00: 09.05

Execution Plan

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

0 SELECT STATEMENT ptimizer=CHOOSE (Cost=13888 Card=458064 Bytes=42141888)

1 0 VIEW (Cost=13888 Card=458064 Bytes=42141888)

2 1 COUNT

3 2 VIEW (Cost=13888 Card=458064 Bytes=36187056)

4 3 SORT (ORDER BY) (Cost=13888 Card=458064 Bytes=18780624)

5 4 TABLE ACCESS (FULL) OF 'T' (Cost=537 Card=458064 Bytes=18780624)

Statistics

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

0 recursive calls

41 db block gets

5579 consistent gets

7935 physical reads

0 redo size

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

1 sorts (disk)

10 rows processed

对于翻页查询的前几页，采用ORDER BY STOPKEY的方式比ORDER BY性能上有很大的优势，那么对于分页查询的最后几页，ORDER BY STOPKEY是否和其他分页查询技术一样，性能比普通方式还要低很多：

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 ORDER BY TIMESTAMP

8 )

9 WHERE ROWNUM <= 458060

10 )

11 WHERE RN >= 458051;

已选择10行。

已用时间: 00: 00: 09.07

Execution Plan

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

0 SELECT STATEMENT ptimizer=CHOOSE (Cost=13888 Card=458060 Bytes=42141520)

1 0 VIEW (Cost=13888 Card=458060 Bytes=42141520)

2 1 COUNT (STOPKEY)

3 2 VIEW (Cost=13888 Card=458064 Bytes=36187056)

4 3 SORT (ORDER BY STOPKEY) (Cost=13888 Card=458064 Bytes=18780624)

5 4 TABLE ACCESS (FULL) OF 'T' (Cost=537 Card=458064 Bytes=18780624)

Statistics

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

0 recursive calls

41 db block gets

5579 consistent gets

7933 physical reads

0 redo size

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

1 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 ORDER BY TIMESTAMP

8 )

9 )

10 WHERE RN BETWEEN 458051 AND 458060;

已选择10行。

已用时间: 00: 00: 10.01

Execution Plan

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

0 SELECT STATEMENT ptimizer=CHOOSE (Cost=13888 Card=458064 Bytes=42141888)

1 0 VIEW (Cost=13888 Card=458064 Bytes=42141888)

2 1 COUNT

3 2 VIEW (Cost=13888 Card=458064 Bytes=36187056)

4 3 SORT (ORDER BY) (Cost=13888 Card=458064 Bytes=18780624)

5 4 TABLE ACCESS (FULL) OF 'T' (Cost=537 Card=458064 Bytes=18780624)

Statistics

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

0 recursive calls

41 db block gets

5579 consistent gets

7935 physical reads

0 redo size

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

1 sorts (disk)

10 rows processed

出乎意料的是，虽然ORDER BY STOPKEY的方式在分页查询的最后几页性能也有明显的下降，但是在和普通的ORDER BY相比，无论从逻辑读、物理读还是从执行时间上看，二者都属于一个数量级上的。

看来ORDER BY STOPKEY排序方式，在STOPKEY接近排序总量的时候也不会有明显的性能下降。