CODEWITHSUNDEEP
sqloracle-databasepagination
Brent Sandstrom
Brent Sandstrom

Reputation: 839

Pagination of large dataset

I have a query that returns a large (10000+ rows) dataset. I want to order by date desc, and display the first 40 results. Is there a way to run a query like this that only retrieves those 40 results without retrieving all 10000 first?

I have something like this:

select rownum, date, * from table
order by date desc

This selects all the data and orders it by date, but the rownum is not in order so it is useless for selecting only the first 40.

ROW_NUMBER() over (ORDER BY date desc) AS rowNumber

^ Will display a rownumber in order, but I can't use it in a where clause because it is a window function. I could run this:

select * from (select ROW_NUMBER() over (ORDER BY date desc) AS rowNumber,
 rownum, * from table
order by date desc) where rowNumber between pageStart and pageEnd

but this is selecting all 10000 rows. How can I do this efficiently?

Upvotes: 1

Views: 922

Answers (3)

MT0
MT0

Reputation: 167822

Oracle 12c has introduced a row limiting clause:

SELECT *
FROM   table
ORDER BY "date" DESC
FETCH FIRST 40 ROWS ONLY;

In earlier versions you can do:

SELECT *
FROM   ( SELECT *
         FROM   table
         ORDER BY "date" DESC )
WHERE  ROWNUM <= 40;

or

SELECT *
FROM   ( SELECT *,
                ROW_NUMBER() OVER ( ORDER BY "date" DESC ) AS RN
         FROM   table )
WHERE  RN <= 40;

or

SELECT *
FROM   TEST
WHERE  ROWID IN ( SELECT ROWID
                  FROM   ( SELECT "Date" FROM TEST ORDER BY "Date" DESC )
                  WHERE  ROWNUM <= 40 );

Whatever you do, the database will need to look through all the values in the date column to find the 40 first items.

Upvotes: 2

Prakash Nadkarni
Prakash Nadkarni

Reputation: 146

You don't need a window function. See

http://www.techonthenet.com/oracle/questions/top_records.php

for an answer to your problem.

Upvotes: -1

Justin Cave
Justin Cave

Reputation: 231651

SELECT *
  FROM (SELECT *
          FROM table
         ORDER BY date DESC)
 WHERE rownum <= 40

will return the first 40 rows ordered by date. If there is an index on date that can be used to find these rows, and assuming statistics are up to date, Oracle should choose to use that index to identify the 40 rows that you want and then do 40 single-row lookups against the table to retrieve the rest of the data. You could throw a /*+ first_rows(40) */ hint into the inner query if you want though that shouldn't have any effect.

For a more general discussion on pagination queries and Top N queries, here's a nice discussion from Tom Kyte and a much longer AskTom discussion.

Upvotes: 5

Related Questions