Oracle分页查询格式

本文简单讨论一下包含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;

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


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

由于查询操作中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;

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语句来实现:

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)


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进行修改,而第二种方式不需要修改原始SQL,直接在原始SQL外面添加一些代码就可以实现。


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

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

©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页