I am new to the database. I would like to implement pagination on large tables using rowid
but what I found that rowid
can be alphanumeric digits and not necessarily they are in any order so I wonder if we can use them to paginate the tables. The table from which I would like to get the records in pagination is very large table having more than 2 billion records and if we execute the query based on offset and length then that comes up with an error Ora1652 unable to extend temp segment in tablespace temp. So is there way to get the results in pagination using rowid
.
Any help would be appreciated thank you in advance.
Edited:
Let's suppose I have a Table say LARGE_ROWS
having ID
and RANDOM
two columns whereas ID
is the primary key column.
SELECT /*+ ordered use_nl(p s) */
*
FROM
(
SELECT
ROWNUM RN,
RD
FROM
(
SELECT
ROWID RD
FROM
large_rows
ORDER BY
large_rows.id
) T
WHERE
ROWNUM < 1000
) P,
large_rows S
WHERE
RN > 900
AND P.RD = S.ROWID;