(Oracle) How get total number of results when using a pagination query?
No, you can't do it without either running the query twice, or running it once and fetching and caching all the rows to count them before starting to display them. Neither is desirable, especially if your query is expensive or potentially returns a lot of rows.
Oracle's own Application Express (Apex) tool offers a choice of pagination options:
- The most efficient just indicates whether or not there are "more" rows. To do this it fetches just one more row than the current page maximum (e.g. 31 rows for page showing rows 16-30).
- Or you can show a limited count that may show "16-30 of 67" or "16-30 of more than 200". This means is fetches up to 201 (in this example) rows. This is not as efficient as option 1, but more efficient than option 3.
- Or you can, indeed, show "16-30 of 13,945". To do this Apex has to fetch all 13,945 but discard all but rows 15-30. This is the slowest, least efficient method.
The pseudo-PL/SQL for option 3 (your preference) would be:
l_total := 15;
for r in
( select *
from
( select rownum rnum, a.*
from (my_query) a
)
where rnum > 15
)
loop
l_total := l_total+1;
if runum <= 30 then
print_it;
end if;
end loop;
show_page_info (15, 30, l_total);
I think you have to modify your query to something like this to get all the information you want on a "single" query.
SELECT *
FROM (SELECT r.*, ROWNUM RNUM, COUNT(*) OVER () RESULT_COUNT
FROM (SELECT t0.ABC_SEQ_ID AS c0, t0.FIRST_NAME, t1.SCORE
FROM ABC t0, XYZ t1
WHERE (t0.XYZ_ID = 751)
AND t0.XYZ_ID = t1.XYZ_ID
ORDER BY t0.RANK ASC) R)
WHERE RNUM between 1 and 15
The reason is that the COUNT(*) OVER()
window function gets evaluated after the WHERE
clause, hence not giving the total count of records but the count of records that satisfy the ROWNUM <= 30
condition.
If you cannot accept the performance ot this query, or of executing 2 separate queries, maybe you should think about a solution like the one proposed by FrustratedWithFormsDesigner in his/her comment about caching the count of records.
If you work with databases on a regular basis I recommend you get a copy of SQL Cookbook. It is an exceptional book with lots of useful tips.