Oracle分页查询格式是怎么样的

这篇文章给大家介绍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分页查询格式是怎么样的就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。