關於分頁查詢的最佳化思路

目前在生產環境中有一個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 >= ?)

最後最佳化的結果稍後奉上。