Oracle pagination ROWNUM column>=value challenge

Question

Having some trouble with oracle pagination. Case:

Table with > 1 billion rows:

• Measurement(Id Number, Classification VARCHAR, Value NUMBER)

Index:

• ON Measurement(Value)

I need a query that gets the first match and the following 2000 matches ordered by Value. I also would like to use the index.

First idea:

SELECT * FROM Measurement WHERE Value >= 1234567890 
AND ROWNUM <= 2000 ORDER BY Value ASC

Result: The query just returns the first 2000 cases it can find in the table, starting from the top, where Value is higher or equal to 1234567890, and then orders that resultset ascending.

Second idea:

SELECT * FROM 
(SELECT * FROM Measurement WHERE Value >= 1234567890 ORDER BY Value ASC)
 WHERE ROWNUM <= 2000

Result: Oracle does not understand that ROWNUM should limit the amount from the inner query, so oracle decides to get all rows where Value is greater or equal to 1234567890 first, and then order that giant resultset before returning the first 2000 rows. Because Oracle is guessing that most of the data in the table will be returned, it ignores any use of index as well.

None of these approaches are acceptable as the first one gives the wrong results, and the second one takes hours.

Is pagination supported at all in Oracle?

Answer 1

I think I have fond a potential solution. However, it's not a query.

declare
cursor c is
SELECT * FROM Measurement WHERE Value >= 1234567890 ORDER BY Value ASC;
l_rec c%rowtype;
begin
    open c;
    for i in 1 .. 2000
    loop
        fetch c into l_rec;
        exit when c%notfound;
    end loop;
    close c;
end;
/

Answer 2

Kindly experiment with more options

SELECT  *
   FROM( SELECT /*+ FIRST_ROWS(2000) */   
          Id,
          Classification,
          Value,
          ROW_NUMBER() OVER (ORDER BY Value) AS rn
     FROM Measurement
     where Value > 1234567889 
       )
   WHERE rn <=2000;

Update1:- Force the use of index on Value.Here IDX_ON_VALUE is the Name of the index on Value in Measurement

SELECT * FROM 
            (SELECT /*+ INDEX(a IDX_ON_VALUE) */* FROM Measurement
             a WHERE value >=1234567890 ) 
                ORDER BY a.Value ASC)
WHERE ROWNUM <= 2000

Answer 3

Not sure if you got the solution for your problem, but to put my two cents:

The first query will not answer your requirements as it will fetch 2000 random records that satisfy your query and then do an order by.

Coming to the second query :

Oracle will first do the execution of the second query and will then only move to the outer query. So, the rownum filter will be applied only after the inner query is executed.

You can try the below approach, to do INDEX FAST FULL SCAN, i have tested it on a table with 2.76 million rows and it is having lesser cost than the other approach:

     SELECT * from Measurement
                where value in ( SELECT VALUE FROM 
                                  (SELECT Value FROM Measurement
                                    WHERE Value >= 1234567890 ORDER BY Value ASC)
                                    WHERE ROWNUM <= 2000)

Hope it Helps

Vishad

Answer 4

You can use the following

SELECT * FROM 
(SELECT Id, Classification, Value, ROWNUM Rank FROM Measurement WHERE Value >= 1234567890)
 WHERE Rank <= 2000
order by Rank

You do not need to order in the sub-query. Simply unnecessary. The above is not pagination but the firs page I would suppose.