关于ROWID分页中，执行计划的错误选择与处理（一）

老实说呢,Oracle版本9以前对分页处理的真的不好，本来想来是很简单的语句，oracle偏偏就处理的有问题。。。。
具体不知道10g在这个方面是否有大的改进。我们见一个具体的例子。。。
SQL> set autot trace
SQL> select rid from
  2  (select a.rowid rid,row_number() over(order by a.topic_type DESC,a.topic_last_post_id DESC) rn
  3  from  forum_topics a
  4  WHERE a.forum_id=40
  5  AND a.topic_type < 2
  6  AND a.topic_status <> 3 
  7  ) WHERE rn < 2 and rn >= 1;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1678 Bytes=33560)
   1    0   VIEW (Cost=2 Card=1678 Bytes=33560)
   2    1     WINDOW (SORT PUSHED RANK) (Cost=2 Card=1678 Bytes=31882)
   3    2       INDEX (RANGE SCAN) OF 'IND_FORUM_TOP_FOR_TP_ST_ID' (NON-UNIQUE) (Cost=2 Card=1678 Bytes=31882)

Statistics
----------------------------------------------------------
          3  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
        388  bytes sent via SQL*Net to client
        504  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed
         
以上的语句，只返回一行记录，查询出来了一个rowid，执行计划是索引扫描即可，可见，逻辑读是非常少的。
但是，我们把这个语句作为一个子查询，马上发现情况就变了：  
SQL> SELECT t.topic_id,t.topic_type,t.topic_distillate,t.topic_vote,t.topic_status,t.topic_moved_id,
  2  TO_CHAR(t.topic_time,'YYYY-MM-DD HH24:MI:SS')  topic_time,
  3  t.topic_last_post_id,t.topic_views,t.topic_title, t.topic_replies,
  4  t.topic_poster FROM forum_topics t
  5  where rowid in
  6  (select rid from
  7  (select a.rowid rid,row_number() over(order by a.topic_type DESC,a.topic_last_post_id DESC) rn
  8  from  forum_topics a
  9  WHERE a.forum_id=40
 10  AND a.topic_type < 2
 11  AND a.topic_status <> 3
 12* ) WHERE rn < 2 and rn >= 1)

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=854 Card=1678 Bytes=194648)
   1    0   HASH JOIN (SEMI) (Cost=854 Card=1678 Bytes=194648)
   2    1     TABLE ACCESS (FULL) OF 'FORUM_TOPICS' (Cost=444 Card=221324 Bytes=24124316)
   3    1     VIEW OF 'VW_NSO_1' (Cost=2 Card=1678 Bytes=11746)
   4    3       VIEW (Cost=2 Card=1678 Bytes=33560)
   5    4         WINDOW (SORT PUSHED RANK) (Cost=2 Card=1678 Bytes=31882)
   6    5           INDEX (RANGE SCAN) OF 'IND_FORUM_TOP_FOR_TP_ST_ID'(NON-UNIQUE) (Cost=2 Card=1678 Bytes=31882)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       4613  consistent gets
          0  physical reads
          0  redo size
       1167  bytes sent via SQL*Net to client
        504  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

这里居然走了全表扫描后与子查询的hash join（这个结果真的是可以让人吐血）。子查询才返回一条rowid，根据这个rowid的查询oracle居然不走rowid扫描后的nested loop的连接。这个时候，我们就是采用/*+ ROWID(t) */或者/*+ use_nl(t) */的提示也不能改变oracle的执行计划。但是，如果我们采用原始的rule的执行计划，会发现情况大有好转。

SQL> SELECT /*+ rule */ t.topic_id,t.topic_type,t.topic_distillate,t.topic_vote,t.topic_status,t.topic_moved_id,
  2    TO_CHAR(t.topic_time,'YYYY-MM-DD HH24:MI:SS')  topic_time,
  3    t.topic_last_post_id,t.topic_views,t.topic_title, t.topic_replies,
  4    t.topic_poster FROM forum_topics t
  5    where rowid in
  6    (select rid from
  7    (select a.rowid rid,row_number() over(order by a.topic_type DESC,a.topic_last_post_id DESC) rn
  8    from  forum_topics a
  9    WHERE a.forum_id=40
 10    AND a.topic_type < 2
 11    AND a.topic_status <> 3 
 12    ) WHERE rn < 2 and rn >= 1) ;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=HINT: RULE
   1    0   NESTED LOOPS
   2    1     VIEW OF 'VW_NSO_1'
   3    2       SORT (UNIQUE)
   4    3         VIEW
   5    4           WINDOW (SORT PUSHED RANK)
   6    5             INDEX (RANGE SCAN) OF 'IND_FORUM_TOP_FOR_TP_ST_ID' (NON-UNIQUE)
   7    1     TABLE ACCESS (BY USER ROWID) OF 'FORUM_TOPICS'

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
       1167  bytes sent via SQL*Net to client
        504  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

通过最后的反复测试，发现只有在如下的情况下，强行指定驱动顺序与连接方式，oracle才最终选择了正确的执行计划。        
SQL> SELECT /*+ ordered use_nl(t) */ t.topic_id,t.topic_type,t.topic_distillate,t.topic_vote,
  2  t.topic_status,t.topic_moved_id,TO_CHAR(t.topic_time,'YYYY-MM-DD HH24:MI:SS')  topic_time,
  3  t.topic_last_post_id,t.topic_views,t.topic_title, t.topic_replies,
  4  t.topic_poster FROM (select rid from
  5  (select a.rowid rid,row_number()  over(order by a.topic_type DESC,a.topic_last_post_id DESC) rn
  6  from  forum_topics a
  7  WHERE a.forum_id=40
  8  AND a.topic_type < 2
  9  AND a.topic_status <> 3
 10  ) WHERE rn < 2 and rn >= 1)  b,forum_topics t
 11* where t.rowid=b.rid

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1680 Card=1678 Bytes=216462)
   1    0   NESTED LOOPS (Cost=1680 Card=1678 Bytes=216462)
   2    1     VIEW (Cost=2 Card=1678 Bytes=33560)
   3    2       WINDOW (SORT PUSHED RANK) (Cost=2 Card=1678 Bytes=31882)
   4    3         INDEX (RANGE SCAN) OF 'IND_FORUM_TOP_FOR_TP_ST_ID' (NON-UNIQUE) (Cost=2 Card=1678 Bytes=31882)
   5    1     TABLE ACCESS (BY USER ROWID) OF 'FORUM_TOPICS' (Cost=1 Card=1 Bytes=109)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         11  consistent gets
          0  physical reads
          0  redo size
       1167  bytes sent via SQL*Net to client
        504  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed