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

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

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.

Advertisement

Answer