ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 利用索引提示减少分页的嵌套层数

利用索引提示减少分页的嵌套层数

原创 Linux操作系统 作者:yangtingkun 时间:2007-07-31 00:00:00 0 删除 编辑

今天和同事讨论了一下索引扫描避免排序的问题,感觉比较有意思,就简单总结一下。


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

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

简单总结一下,这种方法对于偶然一次的查询是没有问题的。但是,不能够替代标准分页写到程序中,因为一旦表结构发生了变化,这个SQL就得到错误的结果,而且不会有任何错误信息来提示你,问题已经发生了。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/4227/viewspace-69370/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
暂无介绍

注册时间:2007-12-29

  • 博文量
    1955
  • 访问量
    10355573