How to solve the paging problem in the View of Union all

In the view of Union all, if the traditional rownum paging method is used, for example
Where rownum <m)
Where linenum> = N
Because Oracle will be overwhelmed when it runs to linenum> = N, leading to execution plan disorder. For example, assume that 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 perform the following operations on the view, we can see that
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 cballs
0 dB block gets
97298 consistent gets
20770 physical reads
0 redo size
518 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

For a very simple query, Nick has an index, and both tables and indexes have been analyzed. It is actually a full table scan, which consumes a lot of resources. At this time, oracle is no longer able to correctly judge the use of indexes, so the full table is used incorrectly. From the statistics, we can see that this query produces a large number of Cr reads and disk reads. At this timeForcibly specifying hint cannot change the execution plan of oracle.Of course, this is not feasible. We must find an effective method.

How can this problem be solved? There are two ways: one is to use the Union all statement to directly query the base table rather than the view in the query. If the preceding statement is transformed:
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)
10 9 index (range scan) of 'ind _ mv_bmw_users_nick1 '(NON-UNIQUE) (cost = 3 card = 1)

Statistics
----------------------------------------------------------
0 recursive cballs
0 dB block gets
8 consistent gets
0 physical reads
0 redo size
553 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)
2 rows processed

The statement is basically the same, but this time the base table is queried, not the view, and the execution plan changes immediately. This time the index can be used, and the cost is greatly reduced, we can see that CR reads are reduced to only 8, and the disk reads are 0.

We use the second method to analyze the function and rewrite the statement
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 '(NON-UNIQUE) (cost = 3 card = 1)

Statistics
----------------------------------------------------------
0 recursive cballs
0 dB block gets
7 consistent gets
0 physical reads
0 redo size
513 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
We can see that the same function, the method of analyzing the function is the simplest, and the index can also be used correctly.

The above is a simple example. We will analyze a complex actual 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 the view of Union all, this query also uses the full table scan of the base table. If you rewrite it to the Union all statement, it will also be an exception and complicated. For example, this would be the case if you write the Union all statement.
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, using the analysis function is very simple and the index is used correctly.
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