Honestly, Oracle Version 9 has been dealing with paging before, and it is very simple to have a very simple statement, and there is a problem with Oracle off. . . . I don't know if I have a big improvement in this area. We see a specific example. . . SQL> SET Autot Tracsql> 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' (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 or more statement, only return a row of records, querying a ROWID, the execution plan is the index scan, visible, logical read is very small. However, we will take this statement as a sub-query, and immediately change: 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 dec) 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) = 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 (COST = 2 card = 1678 bytes = 31882) 6 5 INDEX (RANGE SCAN) of 'Ind_forum_top_for_tp_st_id' (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 proped
It actually walked the Hash Join with the child after the full table scan (this result is really vomiting). The subquery returns a RowID, according to this Rowid query Oracle, I don't take the connection of the NESTED LOOP after the RowID scan. At this time, we are using / * rowid (t) * / or / * USE_NL (T) * / prompt that the Oracle's execution plan cannot be changed. However, if we use the original rule's execution plan, it will find that the situation is better.
SQL> SELECT / * RULE * / T.TOPIC_ID, T.TOPIC_TYPIC_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-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_TOPI CS '
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 Cliant 2 SQL * Net RoundTrips To / From Clom Client 2 Sorts (Memory) 0 Sorts (Disk) 1 Rows Processed
Through the final repeated test, it was found that only in the following cases, forcibly specifying the driving order and connection, Oracle finally selected the correct execution plan. 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.RIDEXECUTION 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 (COST = 2 Card = 1678 BYTES = 31882) 4 3 INDEX (RANGE SCAN) of 'Ind_forum_top_for_tp_st_id' (Non -Unique) (CO ST = 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 Cliant 2 SQL * Net RoundTrips To / From Clom Cliant 1 Sorts (Memory) 0 Sorts (Disk) 1 ROWS Processed
