Re: getting rownum 500000 - 500050 from a database

Hi Marc,

the following is part of an answer I supplied to someone last year, for a 7.3 database

Consider the following,

select *
from emp
where empno in ( select empno

                 from (select empno, rownum line_no 
                       from ( select empno
                              from emp
			      where empno > -1
                              group by empno
                            )
                       )
                 where line_no between &bottom_limit and &top_limit
                )

 if &bottom_limit is 3 and &top_limit is 6, then the result of this will be to select all rows from emp from the third empno to the sixth in empno order, regardless of the values of empno.

The trick is to only use the primary key in the innermost query so that only the index is used without any table access and to rely on the implicit ordering of group by.

Of course if your table is very large the inner-most query could take some time even allowing for the fact that it is only processing the primary index, as of course it handles all the rows and not just the ones that you want.

Even on very large tables it is surprisingly fast.

Given that an alternative is to use an cursor and 'throw away' the rows you don't need up to the first that you do need, then the pluses and minuses are about table size and required set size as a proportion of table size and how far through the table you need to start and possibly the degree of data fragmentation in the table.

Hope it's helpful,

Rod Corderey

Lane Associates
Lane_Associates_at_Compuserve.com
http://www.Lane-Associates.com  

Marc Parkinson wrote:
>
> I need to get row numbers 500000 through 500050 from a database.
> I also need to do an order by clause before I retreive the data.
>
> Any ideas on how to do this other then reading the fir 500000 rows of
> data??
>
> thanks
>
> Marc Parkinson
> marcpark_at_uswest.net
Received on Tue Nov 16 1999 - 11:24:07 CST