Oracle的分页查询语句基本上可以按照本文给出的格式来进行套用。
这篇介绍HASH SORT CLUSTER表对分页查询的帮助。
Oracle分页查询格式（一）：http://yangtingkun.itpub.net/post/468/100278
Oracle分页查询格式（二）：http://yangtingkun.itpub.net/post/468/101703
Oracle分页查询格式（三）：http://yangtingkun.itpub.net/post/468/104595
Oracle分页查询格式（四）：http://yangtingkun.itpub.net/post/468/104867
Oracle分页查询格式（五）：http://yangtingkun.itpub.net/post/468/107934
Oracle分页查询格式（六）：http://yangtingkun.itpub.net/post/468/108677
Oracle分页查询格式（七）：http://yangtingkun.itpub.net/post/468/109834
Oracle分页查询格式（八）：http://yangtingkun.itpub.net/post/468/224557
Oracle分页查询格式（九）：http://yangtingkun.itpub.net/post/468/224409
Oracle分页查询格式（十）：http://yangtingkun.itpub.net/post/468/224823
Oracle分页查询格式（十一）：http://yangtingkun.itpub.net/post/468/485481
Oracle分页查询格式（十二）：http://yangtingkun.itpub.net/post/468/485496
前几天有个网友问到，为什么这个系列的大部分例子中都没有包括查询条件。
其实分页只是一种标准的写法，分页嵌套的业务SQL才是实现查询功能的关键，而这部分可能会很简单，也可能会复杂的很。因此很难模拟各种复杂的业务SQL在分页中的表现。如果要分析分页查询，只能依旧不同业务SQL的特性进行分类，比如：包括GROUP BY操作、包含UNION ALL查询、通过全表扫描获取记录、通过索引扫描获取记录等等。
而且要先假定一些前提，既然要讨论分页的效率，那么分页的SQL一般返回的数据量会比较大或者返回记录数未知。如果业务SQL只是返回10条以内的记录，那么根本没有分页的必要；如果业务SQL本身就可以在秒级以内获取到结果，那么也不用太关心分页后的性能问题。
因此这前面实际上讨论的是返回大数据量的情况下，分页所能带来的性能优势。
至于网友提到的，大部分SQL没有包含查询条件，其实这里是简化SQL后的结果。如果SQL的查询条件可以快速的定位结果，并返回少量的数据，那么这种情况本身效率就很高，使用分页并不会带来进一步的性能提高；如果索引返回大量的数据，或者由于数据量太大，根本不会使用索引，而采用全表扫描的情况，那么这种情况其实完全可以用单表的全表扫描来模拟。
不过Oracle确实有一种表结构非常适合满足索引查询条件的排序分页，这就是HASH排序聚簇表：
SQL> CREATE CLUSTER C_HASH_SORT
2 (ID NUMBER, CREATED DATE SORT)
3 HASHKEYS 100000 SIZE 1125;
Cluster created.
SQL> CREATE TABLE T_HASH_SORT
2 (ID NUMBER,
3 OWNER VARCHAR2(30),
4 OBJECT_NAME VARCHAR2(30),
5 OBJECT_TYPE VARCHAR2(30),
6 CREATED DATE SORT)
7 CLUSTER C_HASH_SORT (ID, CREATED);
Table created.
SQL> CREATE TABLE T_NORMAL
2 (ID NUMBER,
3 OWNER VARCHAR2(30),
4 OBJECT_NAME VARCHAR2(30),
5 OBJECT_TYPE VARCHAR2(30),
6 CREATED DATE);
Table created.
SQL> INSERT INTO T_HASH_SORT
2 SELECT *
3 FROM
4 (
5 SELECT MOD(ROWNUM, 100000) ID,
6 A.OWNER,
7 OBJECT_NAME,
8 OBJECT_TYPE,
9 A.CREATED
10 FROM DBA_OBJECTS A, DBA_DB_LINKS
11 )
12 ORDER BY ID, CREATED;
2476775 rows created.
SQL> COMMIT;
Commit complete.
SQL> INSERT INTO T_NORMAL
2 SELECT *
3 FROM
4 (
5 SELECT MOD(ROWNUM, 100000) ID,
6 A.OWNER,
7 OBJECT_NAME,
8 OBJECT_TYPE,
9 A.CREATED
10 FROM DBA_OBJECTS A, DBA_DB_LINKS
11 )
12 ORDER BY ID, CREATED;
2476775 rows created.
SQL> COMMIT;
Commit complete.
SQL> SET TIMING ON
SQL> SET AUTOT ON
SQL> SELECT *
2 FROM
3 (
4 SELECT ROWNUM RN, A.*
5 FROM
6 (
7 SELECT ID, OWNER, OBJECT_TYPE, CREATED
8 FROM T_NORMAL
9 WHERE ID = 11232
10 ORDER BY CREATED
11 ) A
12 WHERE ROWNUM <= 20
13 )
14 WHERE RN > 10;
RN ID OWNER OBJECT_TYPE CREATED
---------- ---------- -------------------- ------------------------------ --------------
11 11232 SYS JAVA CLASS 11-6月 -08
12 11232 SYS JAVA CLASS 11-6月 -08
13 11232 PUBLIC SYNONYM 11-6月 -08
14 11232 SYS JAVA CLASS 11-6月 -08
15 11232 CTXSYS TABLE 11-6月 -08
16 11232 ORDSYS JAVA RESOURCE 11-6月 -08
17 11232 MDSYS PACKAGE BODY 11-6月 -08
18 11232 PUBLIC SYNONYM 11-6月 -08
19 11232 PUBLIC SYNONYM 11-6月 -08
20 11232 JIANGSU15 INDEX 12-6月 -08
10 rows selected.
Elapsed: 00:00:00.11
Execution Plan
----------------------------------------------------------
Plan hash value: 1455441750
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 1380 | 3213 (1)| 00:00:45 |
|* 1 | VIEW | | 20 | 1380 | 3213 (1)| 00:00:45 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 106 | 5936 | 3213 (1)| 00:00:45 |
|* 4 | SORT ORDER BY STOPKEY| | 106 | 5936 | 3213 (1)| 00:00:45 |
|* 5 | TABLE ACCESS FULL | T_NORMAL | 106 | 5936 | 3212 (1)| 00:00:45 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">10)
2 - filter(ROWNUM<=20)
4 - filter(ROWNUM<=20)
5 - filter("ID"=11232)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9638 consistent gets
0 physical reads
0 redo size
1099 bytes sent via SQL*Net to client
492 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 ID, OWNER, OBJECT_TYPE, CREATED
8 FROM T_HASH_SORT
9 WHERE ID = 11232
10 ORDER BY CREATED
11 ) A
12 WHERE ROWNUM <= 20
13 )
14 WHERE RN > 10;
RN ID OWNER OBJECT_TYPE CREATED
---------- ---------- -------------------- ------------------------------ --------------
11 11232 SYS JAVA CLASS 11-6月 -08
12 11232 SYS JAVA CLASS 11-6月 -08
13 11232 PUBLIC SYNONYM 11-6月 -08
14 11232 PUBLIC SYNONYM 11-6月 -08
15 11232 CTXSYS TABLE 11-6月 -08
16 11232 ORDSYS PROCEDURE 11-6月 -08
17 11232 MDSYS TYPE 11-6月 -08
18 11232 SYS JAVA CLASS 11-6月 -08
19 11232 SYSMAN TRIGGER 11-6月 -08
20 11232 JIANGSU15 INDEX 12-6月 -08
10 rows selected.
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 156907859
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 1380 | 5 (100)| 00:00:01 |
|* 1 | VIEW | | 20 | 1380 | 5 (100)| 00:00:01 |
|* 2 | COUNT STOPKEY | | | | | |
| 3 | VIEW | | 2293 | 125K| 5 (100)| 00:00:01 |
|* 4 | TABLE ACCESS HASH| T_HASH_SORT | 2293 | 125K| | |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">10)
2 - filter(ROWNUM<=20)
4 - access("ID"=11232)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
5 consistent gets
0 physical reads
0 redo size
1087 bytes sent via SQL*Net to client
491 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
为了避免Oracle的PARSE以及缓存对SQL的影响，上面的两个查询都是第二次执行的结果。
比较了一个普通表和HASH SORT聚簇表在分页情况下的性能差距，普通表需要9000多的逻辑读，而HASH SORT CLUSTER表仅仅需要5个逻辑读。显然后者更适合这种指定一个查询条件并排序的分页查询。
当然这是全表扫描的情况，如果普通表建立了复合索引，可以极大的提高查询的效率：
SQL> CREATE INDEX IND_T_NORMAL ON T_NORMAL (ID, CREATED);
Index created.
Elapsed: 00:00:04.18
SQL> SELECT *
2 FROM
3 (
4 SELECT ROWNUM RN, A.*
5 FROM
6 (
7 SELECT ID, OWNER, OBJECT_TYPE, CREATED
8 FROM T_NORMAL
9 WHERE ID = 11232
10 ORDER BY CREATED
11 ) A
12 WHERE ROWNUM <= 20
13 )
14 WHERE RN > 10;
RN ID OWNER OBJECT_TYPE CREATED
---------- ---------- -------------------- ------------------------------ --------------
11 11232 SYS JAVA CLASS 11-6月 -08
12 11232 SYS JAVA CLASS 11-6月 -08
13 11232 PUBLIC SYNONYM 11-6月 -08
14 11232 SYS JAVA CLASS 11-6月 -08
15 11232 CTXSYS TABLE 11-6月 -08
16 11232 ORDSYS JAVA RESOURCE 11-6月 -08
17 11232 MDSYS PACKAGE BODY 11-6月 -08
18 11232 PUBLIC SYNONYM 11-6月 -08
19 11232 PUBLIC SYNONYM 11-6月 -08
20 11232 JIANGSU15 INDEX 12-6月 -08
10 rows selected.
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1590327436
-------------------------------------------------------------------------------------------
|Id | Operation | Name |Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT | | 20| 1380 | 4 (0)| 00:00:01 |
|* 1| VIEW | | 20| 1380 | 4 (0)| 00:00:01 |
|* 2| COUNT STOPKEY | | | | | |
| 3| VIEW | | 25| 1400 | 4 (0)| 00:00:01 |
| 4| TABLE ACCESS BY INDEX ROWID| T_NORMAL | 25| 1400 | 4 (0)| 00:00:01 |
|* 5| INDEX RANGE SCAN | IND_T_NORMAL | 25| | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("RN">10)
2 - filter(ROWNUM<=20)
5 - access("ID"=11232)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
1099 bytes sent via SQL*Net to client
492 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
10 rows processed
可以看到，通过建立复合索引可以显著的提高普通表针对分页查询的性能。但是性能仍然比HASH SORT CLUSTER要差。不过利用索引的方式灵活性更高，因为HASH SORT CLUSTER表只针对指定查询，而建立索引则可以根据查询的不同以不同的方式创建。