Oracle分页查询格式8

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

在10g以前,OracleGROUP 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 STOPKEYHASH GROUP BY,与9iGROUP BY相比较,二者的执行效率都很高,不过利用了标准分页模式,Oracle可以将STOPKEY推入到查询最内层,使得Oracle在扫描的开始阶段就将结果集限制住。从查询的逻辑读和执行时间上也可以看出来,GROUP BY STOPKEY的执行效率更高一些。

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

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