rownum应用系列之分页查询--续1

昨天的思考题，老虎刘的建议是：

创建col_1和col_2字段上的联合索引，避免排序，提高效率。联合索引的字段顺序不能错，order by的字段要放在联合索引的最后。如果是order by desc，正常情况优化器会自动使用index descending扫描方式，不需要建索引的时候加desc 。也有sql复杂的时候优化器没有使用index descending扫描方式，则可以用index_desc来纠正。

今天刚好遇到一个分页查询的问题SQL，简化如下：

SELECT *

FROM (

SELECT

......

,ROWNUM RNSTART

FROM S_SRV_REQ SSR

WHERE SSR.ACT_OPEN_DT + 1 / 3 >= SYSDATE - 60 AND

ROWNUM <= 10 ORDER BY SSR.CREATED DESC

) T

WHERE T.RNSTART >= 1;

这个SQL有两个问题：

1、分页查询的写法有问题，rownum 与 order by并列同时出现在同一个where后面，这种写法得到的结果可能是乱序（除非使用CREATED字段上索引的降序扫描，但是如果该索引不可用，得到的结果就不对了），需要按照上一篇文章的写法进行改写。

2、谓词条件SSR.ACT_OPEN_DT + 1 / 3 >= SYSDATE - 60过滤性很好，而且存在ACT_OPEN_DT字段上的索引，但是因为字段上存在表达式 “+1 / 3“，导致索引不能使用，需要改写sql，将 SSR.ACT_OPEN_DT + 1 / 3 >= SYSDATE - 60 改成SSR.ACT_OPEN_DT >= SYSDATE - 60 - 1 / 3。

注：

因为谓词条件不是等值条件（昨天的sql是col_1= :b1，今天是col_x >=），这个sql如果创建ACT_OPEN_DT和CREATED联合索引是没有意义的，这种情况还是只会使用索引的第一个字段，无法避免排序。