Oracle分頁(yè)查詢格式是怎么樣的

這篇文章給大家介紹Oracle分頁(yè)查詢格式是怎么樣的，內(nèi)容非常詳細(xì)，感興趣的小伙伴們可以參考借鑒，希望對(duì)大家能有所幫助。

Oracle的分頁(yè)查詢語(yǔ)句基本上可以按照本文給出的格式來(lái)進(jìn)行套用。

這篇介紹HASH SORT CLUSTER表對(duì)分頁(yè)查詢的幫助。

前幾天有個(gè)網(wǎng)友問(wèn)到，為什么這個(gè)系列的大部分例子中都沒(méi)有包括查詢條件。

其實(shí)分頁(yè)只是一種標(biāo)準(zhǔn)的寫(xiě)法，分頁(yè)嵌套的業(yè)務(wù)SQL才是實(shí)現(xiàn)查詢功能的關(guān)鍵，而這部分可能會(huì)很簡(jiǎn)單，也可能會(huì)復(fù)雜的很。因此很難模擬各種復(fù)雜的業(yè)務(wù)SQL在分頁(yè)中的表現(xiàn)。如果要分析分頁(yè)查詢，只能依舊不同業(yè)務(wù)SQL的特性進(jìn)行分類，比如：包括GROUP BY操作、包含UNION ALL查詢、通過(guò)全表掃描獲取記錄、通過(guò)索引掃描獲取記錄等等。

而且要先假定一些前提，既然要討論分頁(yè)的效率，那么分頁(yè)的SQL一般返回的數(shù)據(jù)量會(huì)比較大或者返回記錄數(shù)未知。如果業(yè)務(wù)SQL只是返回10條以內(nèi)的記錄，那么根本沒(méi)有分頁(yè)的必要；如果業(yè)務(wù)SQL本身就可以在秒級(jí)以內(nèi)獲取到結(jié)果，那么也不用太關(guān)心分頁(yè)后的性能問(wèn)題。

因此這前面實(shí)際上討論的是返回大數(shù)據(jù)量的情況下，分頁(yè)所能帶來(lái)的性能優(yōu)勢(shì)。

至于網(wǎng)友提到的，大部分SQL沒(méi)有包含查詢條件，其實(shí)這里是簡(jiǎn)化SQL后的結(jié)果。如果SQL的查詢條件可以快速的定位結(jié)果，并返回少量的數(shù)據(jù)，那么這種情況本身效率就很高，使用分頁(yè)并不會(huì)帶來(lái)進(jìn)一步的性能提高；如果索引返回大量的數(shù)據(jù)，或者由于數(shù)據(jù)量太大，根本不會(huì)使用索引，而采用全表掃描的情況，那么這種情況其實(shí)完全可以用單表的全表掃描來(lái)模擬。

不過(guò)Oracle確實(shí)有一種表結(jié)構(gòu)非常適合滿足索引查詢條件的排序分頁(yè)，這就是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以及緩存對(duì)SQL的影響，上面的兩個(gè)查詢都是第二次執(zhí)行的結(jié)果。

比較了一個(gè)普通表和HASH SORT聚簇表在分頁(yè)情況下的性能差距，普通表需要9000多的邏輯讀，而HASH SORT CLUSTER表僅僅需要5個(gè)邏輯讀。顯然后者更適合這種指定一個(gè)查詢條件并排序的分頁(yè)查詢。

當(dāng)然這是全表掃描的情況，如果普通表建立了復(fù)合索引，可以極大的提高查詢的效率：

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

可以看到，通過(guò)建立復(fù)合索引可以顯著的提高普通表針對(duì)分頁(yè)查詢的性能。但是性能仍然比HASH SORT CLUSTER要差。不過(guò)利用索引的方式靈活性更高，因?yàn)?span lang="EN-US">HASH SORT CLUSTER表只針對(duì)指定查詢，而建立索引則可以根據(jù)查詢的不同以不同的方式創(chuàng)建。

關(guān)于Oracle分頁(yè)查詢格式是怎么樣的就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，可以學(xué)到更多知識(shí)。如果覺(jué)得文章不錯(cuò)，可以把它分享出去讓更多的人看到。