关于分页查询的优化思路

目前在生产环境中有一个sql语句执行时间长达7分钟，而且执行频率极高。
其中PROC_INST中有将近6千万的数据。其中STEP_INST是一个物化视图，里面还有5千多条数据。
可以看到这个语句已经有了一些调优的痕迹，可以从里面的子查询和hint能够看出一些信息。
SELECT PROC_INST.OBJID, PROC_INST.CREATION_TIME
  FROM PROC_INST,
       (SELECT / * +leading(PROC_INST LIST table_bpm_step_inst)
          FULL(PROC_INST) hash_aj(LIST) use_nl(table_bpm_step_inst) * /
          PROC_INST.OBJID, PROC_INST.CREATION_TIME, ROW_NUMBER() OVER(ORDER BY creation_time) rn
          FROM PROC_INST,
               (SELECT / * +materialize
                  FULL(in_step) PARALLEL(in_step, 8) * / in_step.root2proc_inst
                  FROM STEP_INST in_step
                 WHERE in_step.status NOT IN (?)
                    OR in_step.WAIT_TIME IS NOT NULL) LIST,
               STEP_INST
         WHERE STEP_INST.ROOT2PROC_INST =
               PROC_INST.OBJID
           AND PROC_INST.ROOT_STATUS = ?
           AND PROC_INST.STATUS = ?
           AND STEP_INST.OBJID =
               PROC_INST.BEGIN2STEP_INST
           AND STEP_INST.COMMITTER = ?
           AND STEP_INST.STATUS IN (?)
           AND STEP_INST.WAIT_TIME IS NULL
           AND STEP_INST.ASSIGNEE = ?
           AND PROC_INST.OBJID = list.root2proc_inst(+)
           AND list.root2proc_inst IS NULL
         ORDER BY CREATION_TIME) INNER_QRY
 WHERE INNER_QRY.OBJID = PROC_INST.OBJID
   AND INNER_QRY.rn > ?
   AND INNER_QRY.rn


上面的查询有几个地方需要注意
首先是分析函数row_number的使用。其实在大量的数据查询中直接使用rownum要高效一些，而且在子查询的结果又加了一层order by的排序操作，所以可以考虑去除row_number()
关于AND list.root2proc_inst IS NULL的查询条件，如果看得仔细一点就会发现，这个过滤条件完全可以放在子查询list里面，尽可能排除较多的数据。
子查询的输出结果集PROC_INST.OBJID, PROC_INST.CREATION_TIME，可以考虑直接使用rowid来代替对应的字段值，这样可能对于索引来说就可以是的索引的使用更加高效，如果是range scan就可以从某种程度上提升为fast scan.
最后的这个地方看似没有问题，其实是最需要做改进的地方。如果输出1000~2000行的数据，那么子查询就会先得查出2000条数据。
 AND INNER_QRY.rn > ?
   AND INNER_QRY.rn
如果输出100000~101000 这样的话，就得先得到101000行的数据，然后再排除过滤。这样的话每个查询的执行代价都会不同，可以考虑在回表的数据上进行一个统一的规划。
基本思路就是先在order by之后的子查询之后做一个rownum  然后只输出rowid。再上一层的子查询中继续拍段rownum>? 这样基于rowid的排除更加清晰。

改进之后的sql语句类似下面的样子。
SELECT PROC_INST.OBJID, PROC_INST.CREATION_TIME
  FROM PROC_INST where rid in (
       (SELECT / * +leading(PROC_INST LIST table_bpm_step_inst)
          FULL(PROC_INST) hash_aj(LIST) use_nl(table_bpm_step_inst) * /
          rid
          FROM
          (select rowid rid,rownum rn  from
          (select PROC_INST.rowid from
               PROC_INST,
               (SELECT / * +materialize
                  FULL(in_step) PARALLEL(in_step, 8) * / in_step.root2proc_inst
                  FROM STEP_INST in_step
                 WHERE in_step.status NOT IN (?)
                    OR in_step.WAIT_TIME IS NOT NULL 
                  AND list.root2proc_inst IS NULL) LIST,
               STEP_INST
         WHERE STEP_INST.ROOT2PROC_INST =
               PROC_INST.OBJID
           AND PROC_INST.ROOT_STATUS = ?
           AND PROC_INST.STATUS = ?
           AND STEP_INST.OBJID =
               PROC_INST.BEGIN2STEP_INST
           AND STEP_INST.COMMITTER = ?
           AND STEP_INST.STATUS IN (?)
           AND STEP_INST.WAIT_TIME IS NULL
           AND STEP_INST.ASSIGNEE = ?
           AND PROC_INST.OBJID = list.root2proc_inst(+)
         ORDER BY CREATION_TIME) INNER_QRY
         where rownum)
   AND INNER_QRY.rn >= ?)

最后优化的结果稍后奉上。