Get total count of rows in pagination query

Question

I have the following query for record pagination

SELECT *
  FROM (SELECT e.*, 
               ROWNUM row_num
          FROM (SELECT emp_no,
                       emp_name,
                       dob 
                  from emp) outr
          WHERE ROWNUM < ( (pagenum * row_size) + 1))
 WHERE  row_num >= ( ( (pagenum  - 1) * row_size) + 1)

I would like to get the count of rows as well in same query and for this I have tried using

COUNT(*) OVER ()

however I am not getting accurate results when I am paginating to next set of page and rows.

How can I use COUNT(*) OVER () efficiently?

You should place the COUNT(*) in the innermost query: SELECT emp_no, emp_name, dob, COUNT(*) FROM emp. Of course, this way you'll have to count all the rows everytime you fetch the records for the next page. I suppose you don't want to fetch the number of the rows once? — Przemyslaw Kruglej
– Przemyslaw Kruglej, Commented Oct 15, 2013 at 10:41
@PrzemyslawKruglej Could you kindly explain what exactly you meant by I suppose you don't want to fetch the number of the rows once? — Jacob
– Jacob, Commented Oct 15, 2013 at 12:23
What I meant is to first fetch the number of the rows, and do not do it again - keep the value. Of course, this way you may fail to show some the records that could be added while the user is going through the pages of results, but if the table from which you fetch the data is large, counting the records every time may impact performance. — Przemyslaw Kruglej
– Przemyslaw Kruglej, Commented Oct 15, 2013 at 12:41
@PrzemyslawKruglej Yes now I got your point. Thanks. So if table is large, count(*) over () would have impact on performance isn't it? So best solution is by doing in two separate queries? — Jacob
– Jacob, Commented Oct 15, 2013 at 13:11

Vincent Malgrat · Accepted Answer · 2013-10-15 10:41:20Z

12

A typical pagination query with the total number of rows would be:

SELECT *
  FROM (SELECT outr.*,
               ROWNUM row_num
          FROM (SELECT emp_no,
                       emp_name,
                       dob,
                       count(*) over () total_nb
                  FROM emp
                 ORDER BY ...) outr
         WHERE ROWNUM < ((pagenum * row_size) + 1))
 WHERE row_num >= (((pagenum - 1) * row_size) + 1)

Don't forget the ORDER BY.

answered Oct 15, 2013 at 10:41

Vincent Malgrat

67.9k9 gold badges122 silver badges176 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Jacob Over a year ago

Vincent, By using count(*) over () does has any impact on performance?

Vincent Malgrat Over a year ago

compared to what? compared to not having the total, sure: you ask Oracle to retrieve the count so it basically has to fetch all rows. Compared to another method? well this will probably perform nicely in most cases, I guess it would depend upon the other method.

Jacob Over a year ago

I used to have two different sql queries, one to fetch the records and another to have count of rows. So I was wondering to use one query to get all records and count of rows.

Vincent Malgrat Over a year ago

In most cases, running one query should outperform running the two independent queries, but not by much since the amound of work is approximately the same.

CodePT Over a year ago

This should be tested because I tried a 2nd query just for count vs this and running two separate queries runs alot faster. Just try it out.

Collectives™ on Stack Overflow

Get total count of rows in pagination query

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related