Oracle分页查询格式（十）

【转载】 yangtingkun: Oracle分页查询格式（十） 2011-01-12 16:52:58

分类： Oracle

Oracle10g的新功能GROUP BY STOPKEY，使得Oracle10g解决了上一篇文章中提到的GROUP BY操作无法分页的问题。

在10g以前，Oracle的GROUP BY操作必须完全执行完，才能将结果返回给用户。但是Oracle10g增加了GROUP BY STOPKEY执行路径，使得用户在执行GROUP BY操作时，可以根据STOPKEY随时中止正在运行的操作。

这使得标准分页函数对于GROUP BY操作重新发挥了作用。

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

表已创建。

SQL> CREATE INDEX IND_T_CREATED ON T (CREATED);

索引已创建。

SQL> ALTER TABLE T MODIFY CREATED NOT NULL;

表已更改。

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

会话已更改。

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

PL/SQL 过程已成功完成。

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

RN CREATED COUNT(*)
---------- ------------------- ----------
10 2005-12-19 17:07:57 50
11 2005-12-19 17:07:58 36
12 2005-12-19 17:08:24 10
13 2005-12-19 17:08:25 49
14 2005-12-19 17:08:26 66
15 2005-12-19 17:08:27 62
16 2005-12-19 17:08:28 81
17 2005-12-19 17:08:29 82
18 2005-12-19 17:08:33 1
19 2005-12-19 17:08:35 3

已选择10行。

已用时间: 00: 00: 00.04

执行计划
----------------------------------------------------------
Plan hash value: 3639065582

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 19 | 665 | 1 (0)|
|* 1 | VIEW | | 19 | 665 | 1 (0)|
|* 2 | COUNT STOPKEY | | | | |
| 3 | VIEW | | 973 | 21406 | 1 (0)|
|* 4 | SORT GROUP BY STOPKEY| | 973 | 7784 | 1 (0)|
| 5 | INDEX FULL SCAN | IND_T_CREATED | 984 | 7872 | 1 (0)|
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("RN">=10)
2 - filter(ROWNUM<20)
4 - filter(ROWNUM<20)

统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
67 consistent gets
0 physical reads
0 redo size
730 bytes sent via SQL*Net to client
385 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 CREATED, COUNT(*)
8 FROM T
9 GROUP BY CREATED
10 ) A
11 )
12 WHERE RN >= 10
13 AND RN < 20;

RN CREATED COUNT(*)
---------- ------------------- ----------
10 2005-12-19 17:09:27 34
11 2005-12-19 17:09:31 29
12 2005-12-19 17:09:40 29
13 2005-12-19 17:09:58 11
14 2005-12-19 17:10:06 6
15 2005-12-19 17:10:12 48
16 2005-12-19 17:10:20 24
17 2005-12-19 17:10:37 8
18 2005-12-19 17:10:40 2
19 2005-12-19 17:10:49 2

已选择10行。

已用时间: 00: 00: 00.06

执行计划
----------------------------------------------------------
Plan hash value: 4036621539

------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 973 | 34055 | 14 (36)|
|* 1 | VIEW | | 973 | 34055 | 14 (36)|
| 2 | COUNT | | | | |
| 3 | VIEW | | 973 | 21406 | 14 (36)|
| 4 | HASH GROUP BY | | 973 | 7784 | 14 (36)|
| 5 | INDEX FAST FULL SCAN| IND_T_CREATED | 50359 | 393K| 10 (10)|
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter("RN">=10 AND "RN"<20)

统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
73 consistent gets
0 physical reads
0 redo size
724 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed

通过上面的执行计划可以看到，无论使用标准分页函数，还是使用其他方式，Oracle采用的都是10g特有的执行计划GROUP BY STOPKEY和HASH GROUP BY，与9i的GROUP BY相比较，二者的执行效率都很高，不过利用了标准分页模式，Oracle可以将STOPKEY推入到查询最内层，使得Oracle在扫描的开始阶段就将结果集限制住。从查询的逻辑读和执行时间上也可以看出来，GROUP BY STOPKEY的执行效率更高一些。

从这一点上看，Oracle10g的新功能确实使得查询效率得到提高。