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

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

首先要強(qiáng)調(diào)的是，這并不是標(biāo)準(zhǔn)的或者推薦的一種分頁語句的寫法，這種方法需要對(duì)表、索引的結(jié)構(gòu)有清晰的認(rèn)識(shí)。而且這種方法的限制條件很多。因此，這里只是單獨(dú)討論一下，沒用將其放到分頁專題中去。

下面是分頁標(biāo)準(zhǔn)寫法和利用HINT的方式的對(duì)比：

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

表已創(chuàng)建。

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

已創(chuàng)建50418行。

SQL> CREATE INDEX IND_T_NAME ON T(NAME);

索引已創(chuàng)建。

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行。

執(zhí)行計(jì)劃
----------------------------------------------------------
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行。

執(zhí)行計(jì)劃
----------------------------------------------------------
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

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

更重要的是，對(duì)于9i版本，很可能標(biāo)準(zhǔn)SQL的寫法不會(huì)使用索引，因此第二種寫法的對(duì)于分頁查詢前幾頁具有更高的效率。

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

上面是這種寫法的優(yōu)點(diǎn)，不過這種寫法還存在著很多的缺點(diǎn)和不足。

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

而且這種寫法對(duì)于單表訪問有效，對(duì)于多個(gè)表連接等復(fù)雜情況就無法得到正確的結(jié)果了。表連接如果采用HASH JOIN，則會(huì)導(dǎo)致原有的排序被破壞，只有排序列的表作為驅(qū)動(dòng)表，則連接方式為NESTED LOOP才能保證最終結(jié)果的順序。但是，這只是簡單的情況，對(duì)于更多更復(fù)雜的執(zhí)行計(jì)劃，很難通過HINT的方式來保證最終結(jié)果的順序的。

到此，關(guān)于“數(shù)據(jù)庫中怎么利用索引提示減少分頁的嵌套層數(shù)”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識(shí)，請(qǐng)繼續(xù)關(guān)注億速云網(wǎng)站，小編會(huì)繼續(xù)努力為大家?guī)砀鄬?shí)用的文章！