Re: Next Pages!!!!!??????

Just a further thought, you could cheat a little with the first time query . By using MOD function you can arrive at the nth value of the primary key, where n is greater than the initial &top_limit, and use this to constrain the inner query where &mode = 'START'.

regards

Rod  

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

Rod Corderey wrote:
>
> Hi,
>
> this is an answer I posted some time ago to a similar question.
>
> The translation of rownum to lineno is simply to allow the rownum value
> to be used in an expression.
>
> ....................................................
>
> It all depends on the size of your table. Consider the following,
>
> select *
> from emp
> where empno in ( select empno
> from (select empno, rownum line_no
> from ( select empno
> from emp
> 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 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.
>
> To travel backwards and forwards from the current point in the data set,
> then the following variation can be used, which progressively improves
> the performance a little as the user traverses a previously retrieved
> set.
>
> select *
> from emp
> where empno in ( select empno
> from (select empno, rownum line_no
> from ( select empno from emp
> where (&mode = 'START'
> or ( &mode = 'FORWARD'
> and empno > &last_empno
> )
> or ( &mode = 'BACK'
> and empno < &first_empno
> )
> )
> group by empno
> )
> )
> where line_no between &bottom_limit and &top_limit
> )
> ;
> &bottom_limit and &top_limit being progressively adjusted to suit.
>
> Hope its helpful
>
> Rod Corderey
>
> Lane Associates
> Lane_Associates_at_Compuserve.com
> http://www.Lane-Associates.com
>
>
>
>
> Chenping Tsou wrote:
> >
> > Is there a way that I could fetch the first 25 records of a infinite
> > sized table, then issue a command to fetch the next 25 records through
> > SQL command?
> > Basically I want to display data usgin Java Servlet to a browser
> > interface. The first procedure
> > would be to get the first 25 records, then if the user pressed next
> > page, it will display the next 25 records. I don't want to load the
> > whole table as this would be very time consuming. I only want to load
> > the records that I am displaying.
> >
> > Thanks, Ping.
Received on Wed Mar 10 1999 - 00:00:00 CET