Merging two cursors in Oracle 19C in optimal way

Question

I have two complex stored procedures for fetching data for pagination. Along with other input parameters, both have startIndex and PageSize which will give fixed number of records. Now I have a requirement where I need to merge the result of both procedures and store in a single cursor. Basically show paginated data from both procedure, but in single cursor. I did the following based on various posts I found -

1. Called Proc_First(...params, startIndex=0, pageSize=INF, out_cursor_1);
2. Called Proc_Second(...params, startIndex=0, pageSize=INF, out_cursor_2);
3. Used loop to iterate over out_cursor_1 and out_cursor_2 and stored in a table type, say R1 & R2
4. Opened final cursor as: select * from (select * from table(R1) union select * from table(R2)) where rownum >= startIndex AND rownum <= (startIndex + pageSize);

Now I have a doubt that I am basically fetching entire data from both procedures and then fetching the paginated data. It may cause performance issues later. One way I thought of was, to use the queries in both procedures and put union there itself. But those queries are too old and complex that rewriting them will be a big mess now.

I want to handle cases like - Proc_1 has 8 records and Proc_2 has 5 records. If pageSize is 10, it should show me data in page 1 (10 records) and page 2 (3 records) without missing any record. Is there a better approach to achieve this?

Answer 1

One way I thought of was, to use the queries in both procedures and put union there itself.

That is the way to go.

But those queries are too old and complex that rewriting them will be a big mess now.

Then don't rewrite then, copy them and wrap them in a sub-query factoring clause (WITH clause) and just re-use them:

WITH query1 ( list_of_column_aliases ) AS (
  your_first_query
),
query2 ( list_of_column_aliases ) AS (
  your_second_query
),
combined_query ( list_of_column_aliases ) AS (
  SELECT * FROM query1
UNION ALL
  SELECT * FROM query2
ORDER BY something
),
paged_query ( list_of_column_aliases, rn ) AS (
  SELECT cq.*, ROWNUM
  FROM   combined_query cq
  WHERE  ROWNUM < start_index + page_size
)
SELECT list_of_column_aliases
FROM   paged_query
WHERE  rn >= start_index;