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?

The first query definitely wont work. The second query, remove hte order by, and move that to the outer select. Try adding a index hint on the inner query and run an explain plan. — OldProgrammer, Dec 13 '14 at 19:23
Is your table partitioned? The first one gives the incorrect results 'cause the logic is incorrect. The second is slow 'cause you're doing the correct thing... What is the explain plan of the query? You have no need to paginate 1bn rows. No human will ever read them so simply don't... — Ben, Dec 13 '14 at 19:23
You can't move the ORDER BY to the outer SELECT @OldProgrammer; you'd get the same results as the first query. — Ben, Dec 13 '14 at 19:24
Ben: Essentially I would like to have a look at the measurements right above and including the 1234567890 measurment. But limited to 2000 rows. Doesn't seem like an unusual case imo. — Toby, Dec 13 '14 at 19:39
If you only want the top 2k records you don't need to paginate! The full-scan is not what I'd expect at all. At the very least I'd expect a count stop-key due to the ROWNUM limitation so are you sure? Do you have partitioning enabled?` — Ben, Dec 13 '14 at 19:43
No, the table is not partitioned. Does this affect whether oracle decides to use index or whether to limit the subselect? — Toby, Dec 13 '14 at 19:57
PX BLOCK, TABLE ACCESS (FULL), Filter predicates Value >= 1234567890 — Toby, Dec 13 '14 at 19:58
It depends, but generally yes. You don't need to select from the entire table but from the partition only. It's an Enterprise extension only so you may not be able to get it and depending on your other queries it may actively hurt you of course... see: http://docs.oracle.com/cd/E11882_01/server.112/e25523/partition.htm — Ben, Dec 13 '14 at 20:02
@Ben, in my case what would you suggest portioning on? The value column has no predictable volume for any range of values. — Toby, Dec 13 '14 at 20:04
If there's no logic behind it then they probably won't be that helpful. The other thing you can try is an ordered index (use the hint first). — Ben, Dec 13 '14 at 20:39
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/66827/discussion-between-toby-and-ben). — Toby, Dec 13 '14 at 20:41
Here you state that you are looking for an SQL which would perform pagination. On another comment you are looking for procedures. State you case correctly before posting. The clear the question is the better results you shall receive. — szakwani, Dec 21 '14 at 04:36

score 0 · Answer 1 · answered Dec 18 '14 at 09:23

0

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.

answered Dec 18 '14 at 09:23

szakwani

386
3
14

This query has the same weakness as the second idea. The inner query results in potentially hundreds of millions of rows which are put into temporary table first, before the outer select orders it and returns 2000 rows. – Toby Dec 18 '14 at 10:24
You have a billion records! You will have to face performance issue. It is a solution, although might not be the best in your terms – szakwani Dec 18 '14 at 11:19
Many databases support real limiting. In combination with an index databases shouldn't need to do more then 2000 reads in order to produce the result. All you should need is a cursor which iterates through 2000 values in the index. – Toby Dec 18 '14 at 11:38

psaraj12 · Answer 2 · 2014-12-18T13:27:56.867

0

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

edited Dec 18 '14 at 13:27

answered Dec 18 '14 at 09:59

psaraj12

4,772
2
21
30

We tried with /*+ FIRST_ROWS(2000) */ but it seems to have no effect. Oracle will still gather too many results from the inner select before actually limiting the resultset. We will try ROW_NUMBER() OVER (ORDER BY Value) and see how it might have an affect. I predict this query will be slow, as the limitiation happens on the outer query. – Toby Dec 18 '14 at 10:21
/*+ FIRST_ROWS(2000) */ use this hint while testing with ROW_NUMBER() – psaraj12 Dec 18 '14 at 10:27
It seems that the inner select still gathers all values greater then 1234567890 in the whole table. – Toby Dec 18 '14 at 11:40
The value occurs somewhere in the middle, +/- 10% – Toby Dec 18 '14 at 11:57
Again, the problem here is that the inner select returns large portions of the table. – Toby Dec 18 '14 at 13:13

score 0 · Answer 3 · answered Dec 18 '14 at 12:05

0

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

answered Dec 18 '14 at 12:05

vishad

1,134
7
11

This is definitivly an improvement as the the inner select uses the index and only returns a single column. However, it still potentially reads hundreds of millions of index values more then it needs to. Also the Oracle IN statement is limited to 1000 entries. – Toby Dec 18 '14 at 12:10
The Limit on 'IN' is there if you are passing literals not if you are passing the values by using an inner query. Also, you can try exists instead of IN but the gerneal approach will remain the same. – vishad Dec 18 '14 at 12:12
Still, the desired solution would make oracle do the following: 1. Go to first match based on index search. 2. Loop through the next 2000 values in the index (which is allready sorted). 3. For each of the 2000 values in the index, return the row it points to. – Toby Dec 18 '14 at 12:14
It should be. But don't think that will be possible with the current structure. Had a quick question, is the 'VALUE' column a unique key in this table? – vishad Dec 18 '14 at 12:17
No it's not unique. The same value might occur many times. Also the distribution of values is unknown so that the value 1234567890 has unknown neighbouring values. – Toby Dec 18 '14 at 12:20

score 0 · Accepted Answer · answered Dec 18 '14 at 13:20

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

Oracle pagination ROWNUM column>=value challenge

4 Answers4