Oracle分頁查詢格式（十三）

前幾天有個網友問到，為什麼這個系列的大部分例子中都沒有包括查詢條件。

其實分頁只是一種標準的寫法，分頁巢狀的業務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表只針對指定查詢，而建立索引則可以根據查詢的不同以不同的方式建立。