Hi Tom,
We are using the following query in java application, for getting the query results in the pagination. To speed up the task we are executing 15 queries at the same time. The queries are being executed on different machines but targeting the same Table in the database. Please Note: In our case, we are only paginating those Tables having the primary key and we are not using any joins between Tables.
SELECT
subquery.id, subquery.column1 ...
FROM
(
SELECT
large_rows.id, large_rows.column1, ... ROW_NUMBER()
OVER
(ORDER BY id ASC)
AS
row_number
FROM
<table_name>
)
subquery
WHERE
row_number
BETWEEN
<offset+1>
AND
<lenght>; when these queries are being executed against large Table having the more than 2 billion records in that then we are getting the following error:
ORA-01652: unable to extend temp segment in tablespace
And when we are trying to execute the query on SQL DEVELOPER, it seems to be taken a long time, even for the starting pages so we changed the above query by the following query:
SELECT /*+ ordered use_nl(p s) */
s.id, s.column1...
FROM
(
SELECT
ROWNUM RN,
RD
FROM
(
SELECT
ROWID RD
FROM
<table_name>
ORDER BY
<table_name>.id
) T
WHERE
ROWNUM < 2000
) P,
large_rows S
WHERE
RN > 1
AND P.RD = S.ROWID; This query executes faster then the previous one.
Can you please suggest will this query work against ORA-01652? Is any better query then the above in this situation? Can we paginate query results without using order by clause (thinking because of the order by clause may cause this issue again)?
We need to know what execution plans are being used.
This video will show you what to look for