(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

Does this work?

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

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);

Answer 3

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 4

WITH
base AS
(
    SELECT ROWNUM RNUM, A.*
    FROM (SELECT * FROM some_table WHERE some_condition) A
)
SELECT FLOOR(((SELECT COUNT(*) FROM base) / 15) + 1) TOTAL_PAGES_TO_FETCH, 
       ((ROWNUM - MOD(ROWNUM, 15)) / 15) + 1 PAGE_TO_FETCH,
       B.*
FROM base B

This query will calculate how many groups of pages you will need to fetch, and fetch the data as one query.

From the result set, process 15 rows at a time. The very last set of rows, may be shorter than 15.

Answer 5

Just a suggestion:

You could consider the Google "1-10 of approximately 13,000,000 results" approach - run the COUNT(*) as a quick sample over the original query. I've assumed here that there is at most one XYZ for a given ABC:

SELECT *
FROM (SELECT r.*, ROWNUM RNUM, 
      (SELECT COUNT(*) * 100
       FROM ABC SAMPLE(1) t0
       WHERE (t0.XYZ_ID = 751)
      ) 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 

Obviously, the sample will be quite inaccurate and variable, so it depends on the requirements whether this is appropriate or not.

Answer 6

Another solution would be to create a materialized view that maintains counts for each value of ABC.XYZ_ID - that way you push the burden of getting the count to processes that insert/update/delete rows in the table.