一次分页查询的优化

SELECT a.ID,
       GMT_MODIFIED,
       GMT_CREATE,
       NAME,
       SOURCE,
       SIGN,
       CATEGORY_ID,
       DESCRIPTION,
       DETAILS,
       KEYWORDS,
       PIC_URLS,
       STATUS,
       KEY_VALUE1,
       KEY_VALUE2,
       KEY_VALUE3
  FROM (SELECT *
          FROM (SELECT id, rownum linenum
                  FROM (SELECT *
                          FROM (SELECT id
                                  FROM alibaba.SPU
                                 WHERE CATEGORY_ID = 1043726
                                   AND BITAND(Nvl(SIGN, 0), 1) = 1
                                 ORDER BY ID DESC)
                         WHERE rownum < 10001)
                 WHERE rownum <= 200)
         WHERE linenum >= 0) a,
       alibaba.spu b
 WHERE a.id = b.id;
同事问的一个问题，说这个SQL查询很慢，该如何优化？
Index                          Column                          Col Column
Name                           Name                            Pos Details
------------------------------ ------------------------------ ---- ------------------------
SPU_CKKKSSGGNI_IND             CATEGORY_ID                       1 NUMBER(22)
                               KEY_VALUE1                        2 VARCHAR2(256)
                               KEY_VALUE2                        3 VARCHAR2(256)
                               KEY_VALUE3                        4 VARCHAR2(256)
                               STATUS                            5 VARCHAR2(16)
                               SIGN                              6 NUMBER(22)
                               GMT_CREATE                        7 DATE NOT NULL
                               GMT_MODIFIED                      8 DATE NOT NULL
                               NAME                              9 VARCHAR2(128)
                               ID                               10 NUMBER(22) NOT NULL
查询谓词出现了CATEGORY_ID，SIGN（函数）.排序的条件是ID,ID也在索引里。
由于SIGN上使用了函数，因此这个字段只能在索引里过滤。CATEGORY_ID = 1043726的值有非常多。
由于我们的查询是分页查询，如果能在索引里完成分页，效率应该还是可以的。
可是遗憾的是，这个查询并不能在索引里完成分页。
因为虽然ID包含在了索引里，可是ID的前面还有很多其他的键值包含在索引里。
对于（CATEGORY_ID，SIGN）为等值查询的情况下，如果索引的组合为
（CATEGORY_ID，SIGN，ID），ID一定是有序的。
但是如果索引的组合是
（CATEGORY_ID，SIGN，XX,ID），那么ID就不是有序的了。需要完全扫描相关索引排序后，再取出前TOP N条。
因此效率就差了。
解决的办法很简单，就是创建一个索引（CATEGORY_ID，SIGN，ID）就可以了。
还有就是同事说创建索引要对ID加上DESC关键字（CATEGORY_ID，SIGN，ID DESC），因为ORDER BY ID DESC.
其实这个是没必要的，ORACLE可以完成这种转换。执行计划部分会出现INDEX RANGE SCAN DESCENDING的执行方式。

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

转载于:http://blog.itpub.net/22034023/viewspace-718478/