Re: SELECTing N rows

From: Rod Corderey <Lane_Associates_at_compuserve.com>
Date: Sun, 23 Aug 1998 10:11:03 +0100
Message-ID: <35DFDCA7.8B10133A_at_compuserve.com>

Hi Lan

You may have already had an answer to this, but the following is a repost of an answer I supplied to someonelse some time ago.

---

It all depends on the size of your table. Consider the following, using Scott/Tiger EMP

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.

But a big but, if your table was very large, say 1M plus rows, then 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 of them and not just the ones that you want.

Running the above against a table of 14,000 records with a three column concatenated key on a low spec pentium pc, NT4, 7.3, took approx 6.5 secs to retrieve 12 rows from the 1200th up, most of this time would be absorbed by the redundant activities of the innermost query happily processing the full 14,000.

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  

> Lan H. Tran wrote:
>
> > could someone help me write a query that will
> > 1. return rows 1 - 100 from a table,
> > 2. return rows 101 - 200 from a table,
> > 3. etc..
> >
> > please reply to my e-mail as well as this group. thanks in advance.
> >
> > Lan
> >
> > ------------------------------------------------------------------------
> >
> > Lan Tran <ltran_at_netscape.com>
> > Consultant
> > Netscape Communications
> >
> > Lan Tran
> > Consultant <ltran_at_netscape.com>
> > Netscape Communications HTML Mail
> > Work: 301-571-3924
> > Home: 703-256-2413
> > Netscape Conference Address
> > Netscape Conference DLS Server
> > Additional Information:
> > Last Name Tran
> > First Name Lan
> > Version 2.1
Received on Sun Aug 23 1998 - 11:11:03 CEST