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