How to troubleshoot paging issues in the view of union ALL

In the view of union all, if you are using the traditional rownum paging method, such as
WHERE RowNum < M)
WHERE LineNum >=n
Because when Oracle executes to LineNum >=n, it will be overwhelmed, causing the execution plan to mess up. For example, suppose Bwm_users is a view of union all.
The code is as follows:
SELECT *
From MV_BMW_USERS_DB1
UNION ALL
SELECT *
From MV_BMW_USERS_DB2

If we do the following on this view, we 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) ' mv_bmw_users_db1 ' (cost=4639 card=864090 bytes=38884050)
8 6 TABLE ACCESS (full) ' 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, Nick is indexed, and the table and index are analyzed, it is a full table scan, consuming very large resources, this time, Oracle can not correctly judge the use of the index, so the wrong use of the whole table, from statistics can also be seen, This query produces a large amount of CR read and disk reads. This time, isforced designation of hint does not alter Oracle's execution plan, of course, this is not going to work, and we have to find an effective way.

How to solve this problem? There are two ways, one is to still use the UNION ALL statement in the query, directly query the base table instead of the view. If the above statement is modified to:
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 (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)
9 INDEX (RANGE SCAN) of ' Ind_mv_bmw_users_nick1 ' (non-unique) (cost=3 card=1)

Statistics
----------------------------------------------------------
0 Recursive calls
0 db Block gets
8 Consistent gets
0 physical Reads
0 Redo Size
553 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)
2 rows processed

The statement is basically the same, but this time the base table was queried, not the view, the execution plan changed immediately, the index was used this time, and the cost was greatly reduced, the CR read reduced to only 8 blocks, and the disk read 0.

We use the second method, analyze the function, and rewrite the statement as
Sql>select * FROM
1 (select Row_number () 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 ' (non-unique) (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 received via sql*net from client
2 sql*net roundtrips To/from Client
1 Sorts (memory)
0 Sorts (disk)
1 rows processed
As you can see, the same function, the method of analyzing the function is the simplest, but also the correct use of the index.

The above is a simple example, 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
(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 =?
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 <?)
WHERE linenum >=?) 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 Bmw_users is a view of union all, the query also uses a full table scan of the base table. If you rewrite it as a UNION ALL statement, it will also be unusually complex, such as the union all will be this way
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 T.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
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.*, 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 take advantage of analytic functions that will be very simple and correct to use the 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 <? and 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