Selection and handling of execution plan errors in rowid paging (1)

To be honest, Oracle versions earlier than were really not good at paging processing. It was originally a very simple statement, but oracle had to deal with problems ....
I do not know whether there are major improvements in this aspect for 10 Gb. Let's see a specific example...
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
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 cballs
0 dB block gets
11 consistent gets
0 physical reads
0 redo size
388 bytes sent via SQL * Net to client
504 bytes encoded ed via SQL * Net From Client
2 SQL * Net roundtrips to/from client
2 sorts (memory)
0 sorts (Disk)
1 rows processed

In the preceding statement, only one row of records is returned, and a rowid is queried. The execution plan is an index scan. It can be seen that there are very few logical reads.
However, we use this statement as a subquery and immediately find that the situation has changed:
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
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 cballs
0 dB block gets
4613 consistent gets
0 physical reads
0 redo size
1167 bytes sent via SQL * Net to client
504 bytes encoded ed via SQL * Net From Client
2 SQL * Net roundtrips to/from client
1 sorts (memory)
0 sorts (Disk)
1 rows processed

Here we went through the hash join with the subquery after the full table scan (this result can really make people vomit blood ). A rowid is returned for the subquery. Oracle does not use the connection of the nested loop after the rowid Scan Based on this rowid. At this time, we use the/* + rowid (t) */or/* + use_nl (t) */prompt, and cannot change the Oracle execution plan. However, if we use the original rule execution plan, we will find that the situation has improved significantly.

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
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 cballs
0 dB block gets
11 consistent gets
0 physical reads
0 redo size
1167 bytes sent via SQL * Net to client
504 bytes encoded ed via SQL * Net From Client
2 SQL * Net roundtrips to/from client
2 sorts (memory)
0 sorts (Disk)
1 rows processed

Through the last repeated tests, we found that Oracle chose the correct execution plan only when the driver sequence and connection mode are forcibly specified as follows.
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
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 cballs
0 dB block gets
11 consistent gets
0 physical reads
0 redo size
1167 bytes sent via SQL * Net to client
504 bytes encoded ed via SQL * Net From Client
2 SQL * Net roundtrips to/from client
1 sorts (memory)
0 sorts (Disk)
1 rows processed