Oracle分页查询格式

来源：互联网发布：知润山房子有问题吗编辑：程序博客网时间：2024/05/16 07:56

本文简单讨论一下包含GROUP BY、DISTINCT、UNIQUE等操作的分页查询语句。

由于包含上述的集操作，Oracle必须处理完所有的数据才会将结果全部的提交给用户。即使包含了ROWNUM，Oracle也只是在处理完所有的数据之后，将需要的数据进行返回。

不过并不是说前面提到的标准写法在这里没有意义，而是最耗时的部分已经处理完毕。通过ROWNUM来限制意义不大。

虽然标准分页的写法对于GROUP BY之类的操作意义不大，但是如果在执行分页之前需要计算总数的话，那么可以对分页的查询语句稍做修改，将计算总数和查询第一页的SQL结合起来，避免一次计算总数的操作。

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

会话已更改。

SQL> CREATE TABLE T AS SELECT ROWNUM ID, A.* FROM DBA_OBJECTS A;

表已创建。

SQL> ALTER TABLE T ADD PRIMARY KEY (ID);

表已更改。

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

PL/SQL 过程已成功完成。

SQL> SET AUTOT ON
SQL> SELECT *
2 FROM
3 (
4 SELECT ROWNUM RN, A.*
5 FROM
6 (
7 SELECT OBJECT_TYPE, CREATED, COUNT(*)
8 FROM T
9 GROUP BY OBJECT_TYPE, CREATED
10 ORDER BY CREATED
11 ) A
12 WHERE ROWNUM < 20
13 )
14 WHERE RN >= 10;

RN OBJECT_TYPE CREATED COUNT(*)
---------- ------------------ ------------------- ----------
10 TABLE 2003-11-13 01:41:01 16
11 CLUSTER 2003-11-13 01:41:02 3
12 INDEX 2003-11-13 01:41:02 31
13 LOB 2003-11-13 01:41:02 2
14 SEQUENCE 2003-11-13 01:41:02 4
15 TABLE 2003-11-13 01:41:02 20
16 INDEX 2003-11-13 01:41:03 16
17 LOB 2003-11-13 01:41:03 6
18 SEQUENCE 2003-11-13 01:41:03 2
19 SYNONYM 2003-11-13 01:41:03 1

已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=97 Card=19 Bytes=874)
1 0 VIEW (Cost=97 Card=19 Bytes=874)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=97 Card=32185 Bytes=1062105)
4 3 SORT (GROUP BY STOPKEY) (Cost=97 Card=32185 Bytes=547145)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=24 Card=32185 Bytes=547145)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
232 consistent gets
0 physical reads
0 redo size
759 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 *
2 FROM
3 (
4 SELECT ROWNUM RN, A.*
5 FROM
6 (
7 SELECT OBJECT_TYPE, CREATED, COUNT(*)
8 FROM T
9 GROUP BY OBJECT_TYPE, CREATED
10 ORDER BY CREATED
11 ) A
12 )
13 WHERE RN >= 10 AND RN < 20;

已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=97 Card=32185 Bytes=1480510)
1 0 VIEW (Cost=97 Card=32185 Bytes=1480510)
2 1 COUNT
3 2 VIEW (Cost=97 Card=32185 Bytes=1062105)
4 3 SORT (GROUP BY) (Cost=97 Card=32185 Bytes=547145)
5 4 TABLE ACCESS (FULL) OF 'T' (Cost=24 Card=32185 Bytes=547145)

由于查询操作中GROUP BY操作是耗时最大的SQL，因此标准分页方式在这里所带来的性能提升十分有限。但是，如果在执行分页查询前需要执行COUNT(*)的话，那么可以考虑将COUNT(*)的结果和分页结果一起返回，从而减少了一次计算总数所需的时间。

SQL> SELECT COUNT(*)
2 FROM
3 (
4 SELECT OBJECT_TYPE, CREATED, COUNT(*)
5 FROM T
6 GROUP BY OBJECT_TYPE, CREATED
7 ORDER BY CREATED
8 );

COUNT(*)
----------
3570

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=97 Card=1)
1 0 SORT (AGGREGATE)
2 1 VIEW (Cost=97 Card=32185)
3 2 SORT (GROUP BY) (Cost=97 Card=32185 Bytes=547145)
4 3 TABLE ACCESS (FULL) OF 'T' (Cost=24 Card=32185 Bytes=547145)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
232 consistent gets
0 physical reads
0 redo size
377 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)
1 rows processed

SQL> SELECT *
2 FROM
3 (
4 SELECT ROWNUM RN, A.*
5 FROM
6 (
7 SELECT OBJECT_TYPE, CREATED, COUNT(*)
8 FROM T
9 GROUP BY OBJECT_TYPE, CREATED
10 ORDER BY CREATED
11 ) A
12 WHERE ROWNUM < 20
13 )
14 WHERE RN >= 10;

已选择10行。

与上面的两个查询相比，下面的两种方法都可以通过一个SQL语句来实现：

SQL> SELECT *
2 FROM
3 (
4 SELECT ROWNUM RN, A.*
5 FROM
6 (
7 SELECT COUNT(*) OVER() CNT, OBJECT_TYPE, CREATED, COUNT(*) CN
8 FROM T
9 GROUP BY OBJECT_TYPE, CREATED
10 ORDER BY CREATED
11 ) A
12 WHERE ROWNUM < 20
13 )
14 WHERE RN >= 10;

RN CNT OBJECT_TYPE CREATED CN
---------- ---------- ------------------ ------------------- ----------
10 3570 TABLE 2003-11-13 01:41:01 16
11 3570 CLUSTER 2003-11-13 01:41:02 3
12 3570 INDEX 2003-11-13 01:41:02 31
13 3570 LOB 2003-11-13 01:41:02 2
14 3570 SEQUENCE 2003-11-13 01:41:02 4
15 3570 TABLE 2003-11-13 01:41:02 20
16 3570 INDEX 2003-11-13 01:41:03 16
17 3570 LOB 2003-11-13 01:41:03 6
18 3570 SEQUENCE 2003-11-13 01:41:03 2
19 3570 SYNONYM 2003-11-13 01:41:03 1

已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=97 Card=19 Bytes=1121)
1 0 VIEW (Cost=97 Card=19 Bytes=1121)
2 1 COUNT (STOPKEY)
3 2 VIEW (Cost=97 Card=32185 Bytes=1480510)
4 3 WINDOW (BUFFER) (Cost=97 Card=32185 Bytes=547145)
5 4 SORT (GROUP BY) (Cost=97 Card=32185 Bytes=547145)
6 5 TABLE ACCESS (FULL) OF 'T' (Cost=24 Card=32185 Bytes=547145)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
232 consistent gets
0 physical reads
0 redo size
808 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
10 rows processed

SQL> SELECT *
2 FROM
3 (
4 SELECT COUNT(*) OVER() CNT, ROWNUM RN, A.*
5 FROM
6 (
7 SELECT OBJECT_TYPE, CREATED, COUNT(*) CN
8 FROM T
9 GROUP BY OBJECT_TYPE, CREATED
10 ORDER BY CREATED
11 ) A
12 )
13 WHERE RN >= 10 AND RN < 20;

CNT RN OBJECT_TYPE CREATED CN
---------- ---------- ------------------ ------------------- ----------
3570 10 TABLE 2003-11-13 01:41:01 16
3570 11 CLUSTER 2003-11-13 01:41:02 3
3570 12 INDEX 2003-11-13 01:41:02 31
3570 13 LOB 2003-11-13 01:41:02 2
3570 14 SEQUENCE 2003-11-13 01:41:02 4
3570 15 TABLE 2003-11-13 01:41:02 20
3570 16 INDEX 2003-11-13 01:41:03 16
3570 17 LOB 2003-11-13 01:41:03 6
3570 18 SEQUENCE 2003-11-13 01:41:03 2
3570 19 SYNONYM 2003-11-13 01:41:03 1

已选择10行。

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=97 Card=32185 Bytes=1898915)
1 0 VIEW (Cost=97 Card=32185 Bytes=1898915)
2 1 WINDOW (BUFFER) (Cost=97 Card=32185 Bytes=1062105)
3 2 COUNT
4 3 VIEW (Cost=97 Card=32185 Bytes=1062105)
5 4 SORT (GROUP BY) (Cost=97 Card=32185 Bytes=547145)
6 5 TABLE ACCESS (FULL) OF 'T' (Cost=24 Card=32185 Bytes=547145)

第一种方法采用了标准分页方式，效率相对更高一些，但是第一种方法需要对原始SQL进行修改，而第二种方式不需要修改原始SQL，直接在原始SQL外面添加一些代码就可以实现。

ask tom上关于分页改进的一篇文章：

http://www.oracle.com/technetwork/issue-archive/2007/07-jan/o17asktom-093877.html?ssSourceSiteId=otncn