In the view of the Union All, if the traditional use of ROWNUM paging mode, such as WHERE ROWNUM
If we do the following on this view, you can see SQL> Select * from 2 (SELECT ROWNUM LINENUM, ID, NICK FROM 3 (Select ID, Nick from BMW_USERS WHERE NICK = 'Test' Order By ID) 4 Where Rownum < 50) 5 where linenum> = 1;
Execution Plan ------------------------------------------------ ---------- 0 Select Statement Optimizer = Choose (COST = 20385 Card = 49 bytes = 2401) 1 0 View (COST = 20385 Card = 49 Bytes = 2401) 2 1 count (stopkey) 3 2 VIEW (Cost = 20385 Card = 1728633 Bytes = 62230788) 4 3 SORT (ORDER BY STOPKEY) (Cost = 20385 Card = 1728633 Bytes = 62230788) 5 4 VIEW OF 'BMW_USERS' (Cost = 9278 Card = 1728633 Bytes = 62230788) 6 5 UNION-ALL 7 6 TABLE ACCESS (FULL) OF 'MV_BMW_USERS_DB1' (Cost = 4639 Card = 864090 Bytes = 38884050) 8 6 TABLE ACCESS (FULL) OF 'MV_BMW_USERS_DB2' (Cost = 4639 Card = 864543 Bytes = 38904435)
Statistics --------------------------------------------------- --------- 0 recursive calls 0 db block gets 97298 consistent gets 20770 physical reads 0 redo size 518 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 A very simple query, there is index on the NICK, and the table and index are analyzed, actually a full table scan, a very large resource At this time, Oracle has no correct judgment using the index, so the full table is used, which can also be seen from the statistics, which produces a large number of CR reads and disk reads. At this time, it is forced to specify that Hint can also change the Oracle's execution plan. Of course, this is not enough, we must find a valid approach. How can this problem solve? There are two ways, one is still using the UNION All statement in the query, directly querying the base table instead of the view. If the above statement is transformed into: SQL> Select * from 2 (Select Rownum Linenum, ID, Nick from 3 (Select * from 4 (Select ID, Nick from MV_BMW_USERS_DB1 WHERE NICK = 'Test' 5 Union All 6 Select ID, NICK from MV_BMW_USERS_DB1 WHERE NICK = 'Test') 7 Order By ID) 8 WHERE ROWNUM <50) 9 WHERE LINENUM> = 1;
Execution Plan ------------------------------------------------ ---------- 0 Select Statement Optimizer = Choose (COST = 17 Card = 2 Bytes = 98) 1 0 View (COST = 17 Card = 2 Bytes = 98) 2 1 count (stopkey) 3 2 View VIEW (COST = 17 Card = 2 Bytes = 72) 4 3 Sort (ORDER BY Stopkey) (COST = 17 Card = 2 Bytes = 72) 5 4 View (COST = 8 Card = 2 BYtes = 72) 6 5 Union-All 7 6 table access (by index rowid) of 'mv_bmw_users_db1' (COST = 4 card = 1 Bytes = 45) 8 7 INDEX (RANGE SCAN) of 'Ind_MV_BMW_USERS_NICK1' (Non-Unique) (COST = 3 Card = 1) 9 6 Table Access (by index rowid) of 'mv_bmw_users_db1' (COST = 4 card = 1 Bytes = 45) 10 9 INDEX (RANGE SCAN) of 'Ind_mv_BMW_USERS_NICK1' (COST = 3 Card = 1) statistics ---- -------------------------------------------------- ---- 0 Recursive Calls 0 DB Block Gets 8 Consistent Gets 0 Physical Reads 0 Red O Size 553 BYTES SENT VIA SQL * NET To Client 504 BYtes Received Via Sql * Net from Clom Clom Clom Clom Clom Clom Clom Clom Clom Clom Clom Clom Cliant 1 Sorts (Memory) 0 Sorts (Disk) 2 Rows Processed
The statement is basically the same, but this time I query the base table, not a view, the execution plan has changed immediately. This time I can use the index, and the cost has a big reduction, you can see that CR reads decreases 8 blocks, and disk reads 0.
We use the second method, analyze the function, rewrite the statement as SQL> Select * from 1 (Select Row_Number () Over (Order By ID) RN, ID, NICK from BMW_USERS WHERE NICK = 'Test') 2 Where RN <50 and rn> = 1;
Execution Plan ------------------------------------------------ ---------- 0 Select Statement Optimizer = Choose (COST = 13 Card = 1 Bytes = 49) 1 0 View (COST = 13 Card = 1 Bytes = 49) 2 1 Window (Sort Pushed Rank) ( COST = 13 card = 1 Bytes = 45) 3 2 View of 'BMW_USERS' (COST = 4 Card = 1 Bytes = 45) 4 3 Union-all (Partition) 5 4 Table Access (by index rowid) of 'mv_bmw_users_db1' ( COST = 4 card = 1 Bytes = 45) 6 5 INDEX (RANGE SCAN) of 'Ind_mv_BMW_USERS_NICK1' (Non-Unique) (COST = 3 CARD = 1) 7 4 Table Access (By Index RowID) of 'MV_BMW_USERS_DB2' (COST = 4 card = 1 Bytes = 45) 8 7 Index (Range Scan) of 'Ind_mv_BMW_USERS_NICK2' (COST = 3 Card = 1) statistics ----------------- ---------------------------------------- 0 Recursive Calls 0 DB Block Gets 7 Consistent Gets 0 Physical Reads 0 Redo Size 513 BYTES SENT VIA SQL * NET To Client 504 BYTES RECE Ived via SQL * Net from Clom Clism 2 SQL * Net RoundTrips To / from Clom Clism 1 Rows Processed can be seen, the same function, the method of analyzing the function is the simplest, and it can also be correct Use indexes.
The above is a simple example, and we analyze a complex practical statement. The original statement is: select / * ordered use_nl (u1, p2, u2) * / t2. *, U1. nick, u1.user_id, u1.id as userid, u2.nick as user2, u2.user_id AS ID2, U2 .id as userid2, p2.post_username as post_username2, to_char (p2.post_time, 'yyyy-mm-dd hh24: mi: ss') Post_time from (SELECT * FROM (SELECT T1. *, ROWNUM AS LINENum from index (t IND_FORUM_TOPICS_FOR_ID) * / t.topic_id, t.topic_type, t.topic_distillate, t.topic_vote, t.topic_status, t.topic_moved_id, TO_CHAR (t.topic_time, 'YYYY-MM-DD HH24: MI: SS' ) topic_time, t.topic_last_post_id, t.topic_views, t.topic_title, t.topic_replies, t.topic_poster FROM forum_topics t WHERE t.forum_id =? AND t.topic_type <2 AND t.topic_status <> 3 ORDER BY t.topic_type DESC , t.topic_last_post_id DESC) T1 WHERE rownum =?) T2, forum_posts p2, bmw_users u1, bmw_users u2 WHERE T2.topic_poster = u1.user_id AND p2.post_id = T2.topic_last_post_id AND u2.user_id = p2 .poster_id because where BMW_USERS is a view of UNION ALL, so the query also uses a full mete scan of the base table.
If you rewrite the statement of UNION ALL, it will also be an exception complex, such as the UION ALL will be this SELECT * FROM (SELECT / * Ordered USE_NL (U1, P2, U2) * / T2. *, U1. Nick, u1.user_id, u1.id as userid, u2.nick as user2, u2.user_id as ID2, u2.id as userid2, p2.post_username as post_username2, to_char (p2.post_time, 'YYYY-MM-DD HH24: MI: SS ') post_time FROM (SELECT * FROM (SELECT T1 *, rownum as linenum FROM (SELECT / * index (t IND_FORUM_TOPICS_FOR_ID) * / t.topic_id, t.topic_type, t.topic_distillate, t.topic_vote. , t.topic_status, t.topic_moved_id, TO_CHAR (t.topic_time, 'YYYY-MM-DD HH24: MI: SS') topic_time, t.topic_last_post_id, t.topic_views, t.topic_title, t.topic_replies, t.topic_poster FROM Forum_TOPICS T where t.forum_id =: bind0 and topic_type <2 and t.topic_status <> 3 Order by t.topic_type desc, t.topic_last_post_id desc) T1 WHERE ROWNUM <: bind1) Where linenum> =: bind2) T2, Forum_POSTS P2, MV_BMW_USERS_DB1 U1, MV_BMW_USERS_DB1 U2 WHERE T2.TOPIC_POSTER = U1.USER_ID AND P2.POST_ID = T2.TOPIC_LAST_POST_ID AND U2.USER_ID = P2.Poster_ID Union All SELEC T / * Ordered USE_NL (U1, P2, U2) * / T2. *, U1. Nick, u1.user_id, u1.id as userid, u2.nick as user2, u2.user_id as ID2, u2.id as userid2 , p2.post_username as post_username2, TO_CHAR. (p2.post_time, 'YYYY-MM-DD HH24: MI: SS') post_time FROM (SELECT * FROM (SELECT T1 *, rownumas linenum FROM (SELECT / * index (t IND_FORUM_TOPICS_FOR_ID * / t.topic_id, t.topic_type, t.topic_distillate, t.topic_vote, t.topic_status, t.topic_moved_id, to_char (t.topic_time, 'YYYY-MM-DD HH24: MI: SS') Topic_Time, T. Topic_last_post_id, t.topic_views, t.topic_title, t.topic_replies, t.topic_poster from forum_topics t where t.forum_id =: bind3 and t.topic_type <
2 AND t.topic_status <> 3 ORDER BY t.topic_type DESC, t.topic_last_post_id DESC) T1 WHERE rownum <: bind4) WHERE linenum> =: bind5) T2, forum_posts p2, MV_BMW_USERS_DB1 u1, MV_BMW_USERS_DB2 u2 WHERE T2.topic_poster = u1 .user_id and p2.post_id = t2.topic_last_post_id and u2.user_id = p2.poster_id union all select / * Ordered USE_NL (U1, P2, U2) * / T2. *, U1. Nick, u1.user_id, u1.id As UserID, U2.NICK As User2, U2.User_ID AS ID2, U2.ID AS UserId2, P2.post_username as post_username2, to_char (p2.post_time, 'YYYY-MM-DD HH24: MI: SS') Post_time from (SELECT * FROM (SELECT T1. *, rownum as linenum FROM (SELECT / * index (t IND_FORUM_TOPICS_FOR_ID) * / t.topic_id, t.topic_type, t.topic_distillate, t.topic_vote, t.topic_status, t.topic_moved_id, TO_CHAR ( t.topic_time, 'YYYY-MM-DD HH24: MI: SS') topic_time, t.topic_last_post_id, t.topic_views, t.topic_title, t.topic_replies, t.topic_poster FROM forum_topics t WHERE t.forum_id =: bind6 AND t .topic_type <2 and t.topic_status <> 3 ORDER BY T.TOPIC_TYPE DESC, T.TOPIC_LAST _post_id DESC) T1 WHERE rownum <: bind7) WHERE linenum> =: bind8) T2, forum_posts p2, MV_BMW_USERS_DB2 u1, MV_BMW_USERS_DB1 u2 WHERE T2.topic_poster = u1.user_id AND T2.topic_last_post_id = p2.post_id AND u2.user_id = p2. Poster_id union all select / * Ordered USE_NL (U1, P2, U2) * / T2. *, U1. Nick, u1.user_id, u1.id as userid, u2.nick as user2, u2.user_id AS ID2, U2. ID as userid2, p2.post_username as post_username2, to_char (p2.post_time, 'yyyy-mm-dd hh24: mi: ss') post_time from (SELECT * FROM (SELECT T1. *, ROWNUM AS LINENUM FROM (SELECT / *
index (t IND_FORUM_TOPICS_FOR_ID) * / t.topic_id, t.topic_type, t.topic_distillate, t.topic_vote, t.topic_status, t.topic_moved_id, TO_CHAR (t.topic_time, 'YYYY-MM-DD HH24: MI: SS') topic_time, t.topic_last_post_id, t.topic_views, t.topic_title, t.topic_replies, t.topic_poster FROM forum_topicst WHERE t.forum_id =: bind9 AND t.topic_type <2 AND t.topic_status <> 3 ORDER BY t.topic_type DESC, t.topic_last_post_id DESC) T1 WHERE rownum <: bind10) WHERE linenum> =: bind11) T2, forum_posts p2, MV_BMW_USERS_DB2 u1, MV_BMW_USERS_DB2 u2 WHERE T2.topic_poster = u1.user_id AND p2.post_id = T2.topic_last_post_id AND u2.user_id = P2.poster_id) ORDER by Topic_Type DESC, Topic_Last_Post_id DESC, however, we use the analysis function, which will look very simple, and correct use index select / * ordered use_nl (u1, p2, u2) * / t2. *, u1.nick , u1.user_id, u1.id as userid, u2.nick as user2, u2.user_id as id2, u2.id as userid2, p2.post_username as post_username2, to_char (p2.post_time, 'yyyy-mm-dd hh24: mi : Ss') Post_time from (SELECT * FROM (SELECT / * INDEX (T Ind_forum_topics_for_id) * / row_number () over (order by t.topic_type DESC, t.topic_last_post_id DESC) rn, t.topic_id, t.topic_type, t.topic_distillate, t.topic_vote, t.topic_status, t.topic_moved_id, TO_CHAR (t.topic_time, ' YYYY-MM-DD HH24: MI:? SS ') topic_time, t.topic_last_post_id, t.topic_views, t.topic_title, t.topic_replies, t.topic_poster FROM forum_topics t WHERE t.forum_id = AND t.topic_type <2 AND t .topic_status <> 3) T1 WHERE RN =?) T2, Forum_POSTS P2, BMW_USERS U1, BMW_USERS U2 WHERE T2.TOPIC_POSTER = u1.user_id and p2.post_id = t2.topic_last_post_id and u2.user_id = p2. Poster_id
