扫二维码与项目经理沟通
我们在微信上24小时期待你的声音
解答本文疑问/技术咨询/运营咨询/技术建议/互联网交流
这篇文章给大家介绍Oracle分页查询格式是怎么样的，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。
创新互联是网站建设专家,致力于互联网品牌建设与网络营销，专业领域包括成都做网站、成都网站建设、电商网站制作开发、重庆小程序开发、微信营销、系统平台开发，与其他网站设计及系统开发公司不同，我们的整合解决方案结合了恒基网络品牌建设经验和互联网整合营销的理念，并将策略和执行紧密结合，且不断评估并优化我们的方案，为客户提供全方位的互联网品牌整合方案！
Oracle的分页查询语句基本上可以按照本文给出的格式来进行套用。
这篇介绍HASH SORT CLUSTER表对分页查询的帮助。
前几天有个网友问到，为什么这个系列的大部分例子中都没有包括查询条件。
其实分页只是一种标准的写法，分页嵌套的业务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表只针对指定查询，而建立索引则可以根据查询的不同以不同的方式创建。
关于Oracle分页查询格式是怎么样的就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。
我们在微信上24小时期待你的声音
解答本文疑问/技术咨询/运营咨询/技术建议/互联网交流
微信二维码
移动版官网