數據庫中怎么利用索引提示減少分頁的嵌套層數

這篇文章主要介紹“數據庫中怎么利用索引提示減少分頁的嵌套層數”，在日常操作中，相信很多人在數據庫中怎么利用索引提示減少分頁的嵌套層數問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”數據庫中怎么利用索引提示減少分頁的嵌套層數”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！

首先要強調的是，這并不是標準的或者推薦的一種分頁語句的寫法，這種方法需要對表、索引的結構有清晰的認識。而且這種方法的限制條件很多。因此，這里只是單獨討論一下，沒用將其放到分頁專題中去。

下面是分頁標準寫法和利用HINT的方式的對比：

SQL> CREATE TABLE T (ID NUMBER PRIMARY KEY, NAME VARCHAR2(30) NOT NULL);

表已創建。

SQL> INSERT INTO T SELECT ROWNUM, OBJECT_NAME FROM DBA_OBJECTS;

已創建50418行。

SQL> CREATE INDEX IND_T_NAME ON T(NAME);

索引已創建。

SQL> SET AUTOT ON
SQL> SET AUTOT ON EXP
SQL> SELECT *
 2  FROM
 3  (
 4   SELECT A.*, ROWNUM RN
 5   FROM
 6   (
 7    SELECT * FROM T ORDER BY NAME
 8   ) A
 9   WHERE ROWNUM <= 20
10  ) WHERE RN > 10;

       ID NAME                                   RN
---------- ------------------------------ ----------
    11501 /1023e902_OraCharsetUTFE               11
    11502 /1023e902_OraCharsetUTFE               12
    46027 /10240eba_GenPropertySequence          13
    46145 /10240eba_GenPropertySequence          14
    43203 /1025308f_SunTileScheduler             15
    44344 /1025308f_SunTileScheduler             16
    37617 /10297c91_SAXAttrList                  17
    38208 /10297c91_SAXAttrList                  18
    24613 /103a2e73_DefaultEditorKitEndP         19
    24614 /103a2e73_DefaultEditorKitEndP         20

已選擇10行。

執行計劃
----------------------------------------------------------
Plan hash value: 3635692127

--------------------------------------------------------------------------------------------
| Id  | Operation                      | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |            |    20 |   860 |     4   (0)| 00:00:01 |
|*  1 |  VIEW                          |            |    20 |   860 |     4   (0)| 00:00:01 |
|*  2 |   COUNT STOPKEY                |            |       |       |            |          |
|   3 |    VIEW                        |            | 45221 |  1324K|     4   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| T          | 45221 |  1324K|     4   (0)| 00:00:01 |
|   5 |      INDEX FULL SCAN           | IND_T_NAME |    21 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter("RN">10)
  2 - filter(ROWNUM<=20)

Note
-----
  - dynamic sampling used for this statement

SQL> SELECT *
 2  FROM
 3  (
 4   SELECT /*+ INDEX(T IND_T_NAME) */ T.*, ROWNUM RN
 5   FROM T
 6   WHERE ROWNUM <= 20
 7  )
 8  WHERE RN > 10;

       ID NAME                                   RN
---------- ------------------------------ ----------
    11501 /1023e902_OraCharsetUTFE               11
    11502 /1023e902_OraCharsetUTFE               12
    46027 /10240eba_GenPropertySequence          13
    46145 /10240eba_GenPropertySequence          14
    43203 /1025308f_SunTileScheduler             15
    44344 /1025308f_SunTileScheduler             16
    37617 /10297c91_SAXAttrList                  17
    38208 /10297c91_SAXAttrList                  18
    24613 /103a2e73_DefaultEditorKitEndP         19
    24614 /103a2e73_DefaultEditorKitEndP         20

已選擇10行。

執行計劃
----------------------------------------------------------
Plan hash value: 2512188149

--------------------------------------------------------------------------------------------
| Id  | Operation                     | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |            |    20 |   860 |     4   (0)| 00:00:01 |
|*  1 |  VIEW                         |            |    20 |   860 |     4   (0)| 00:00:01 |
|*  2 |   COUNT STOPKEY               |            |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| T          | 45221 |  1324K|     4   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN           | IND_T_NAME | 45221 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter("RN">10)
  2 - filter(ROWNUM<=20)

Note
-----
  - dynamic sampling used for this statement

對于第二種方法，由于Oracle會采用索引全掃描的方式，因此返回的數據本身就是排好序的，避免的ORDER BY語句，而且可以減少一層嵌套。

更重要的是，對于9i版本，很可能標準SQL的寫法不會使用索引，因此第二種寫法的對于分頁查詢前幾頁具有更高的效率。

對于降序的情況，需要改變HINT，由INDEX修改為INDEX_DESC。

上面是這種寫法的優點，不過這種寫法還存在著很多的缺點和不足。

首先，這種寫法要求排序列必須建立索引，且該列不能為空。否則，Oracle不使用INDEX FULL SCAN執行計劃，則無法保證按照正確的排序返回結果。這就造成了SQL的寫法與表結構、列的NOT NULL約束以及索引的情況有關，SQL的書寫不在透明。而且一旦SQL寫法依賴的結構發生了變化，就會導致SQL得到錯誤的結果。

而且這種寫法對于單表訪問有效，對于多個表連接等復雜情況就無法得到正確的結果了。表連接如果采用HASH JOIN，則會導致原有的排序被破壞，只有排序列的表作為驅動表，則連接方式為NESTED LOOP才能保證最終結果的順序。但是，這只是簡單的情況，對于更多更復雜的執行計劃，很難通過HINT的方式來保證最終結果的順序的。

到此，關于“數據庫中怎么利用索引提示減少分頁的嵌套層數”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！