(Oracle) How get total number of results when using a pagination query?

Question

I am using Oracle 10g and the following paradigm to get a page of 15 results as a time (so that when the user is looking at page 2 of a search result, they see records 16-30).

select *    from  ( select rownum rnum, a.*     from (my_query) a    where rownum <= 30 ) where rnum > 15; 

Right now I'm having to run a separate SQL statement to do a "select count" on "my_query" in order to get the total number of results for my_query (so that I can show it to the user and use it to figure out total number of pages, etc).

Is there any way to get the total number of results without doing this via a second query, i.e. by getting it from above query? I've tried adding "max(rownum)", but it doesn't seem to work (I get an error [ORA-01747] that seems to indicate it doesnt like me having the keyword rownum in the group by).

My rationale for wanting to get this from the original query rather than doing it in a separate SQL statement is that "my_query" is an expensive query so I'd rather not run it twice (once to get the count, and once to get the page of data) if I dont have to; but whatever solution I can come up with to get the number of results from within a single query (and at the same time get the page of data I need) should not add much if any additional overhead, if possible. Please advise.

Here is exactly what I'm trying to do for which I receive an ORA-01747 error because I believe it doesnt like me having ROWNUM in the group by. Note, If there is another solution that doesnt use max(ROWNUM), but something else, that is perfectly fine too. This solution was my first thought as to what might work.

 SELECT * FROM (SELECT r.*, ROWNUM RNUM, max(ROWNUM)  FROM (SELECT t0.ABC_SEQ_ID AS c0, t0.FIRST_NAME, t0.LAST_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 ROWNUM <= 30 GROUP BY r.*, ROWNUM) WHERE RNUM > 15 

--------- EDIT -------- Note, based on the first comment I tried the following that appears to work. I dont know how well it performs versus other solutions though (I'm looking for the solution that fufills my requirement but performs the best). For example, when I run this it takes 16 seconds. When I take out the COUNT(*) OVER () RESULT_COUNT it takes just 7 seconds:

    SELECT * FROM (SELECT r.*, ROWNUM RNUM, )      FROM (SELECT COUNT(*) OVER () RESULT_COUNT,            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 ROWNUM <= 30) WHERE RNUM > 1 

The explain plan changes from doing a SORT (ORDER BY STOP KEY) to do a WINDOW (SORT).

Before:

SELECT STATEMENT ()   COUNT (STOPKEY)       VIEW ()       SORT (ORDER BY STOPKEY)       NESTED LOOPS ()       TABLE ACCESS (BY INDEX ROWID)  XYZ       INDEX (UNIQUE SCAN)   XYZ_ID      TABLE ACCESS (FULL)    ABC 

After:

SELECT STATEMENT ()   COUNT (STOPKEY)       VIEW ()       WINDOW (SORT)         NESTED LOOPS ()       TABLE ACCESS (BY INDEX ROWID)  XYZ       INDEX (UNIQUE SCAN)   XYZ_ID      TABLE ACCESS (FULL)    ABC 

Answer 1

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.

Answer 2

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:

1. 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).
2. 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.
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);