数据库中怎么利用索引提示减少分页的嵌套层数

这篇文章主要介绍“数据库中怎么利用索引提示减少分页的嵌套层数”，在日常操作中，相信很多人在数据库中怎么利用索引提示减少分页的嵌套层数问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”数据库中怎么利用索引提示减少分页的嵌套层数”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

首先要强调的是，这并不是标准的或者推荐的一种分页语句的写法，这种方法需要对表、索引的结构有清晰的认识。而且这种方法的限制条件很多。因此，这里只是单独讨论一下，没用将其放到分页专题中去。

下面是分页标准写法和利用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的方式来保证最终结果的顺序的。

到此，关于“数据库中怎么利用索引提示减少分页的嵌套层数”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！