Re: Retrieve N record from a large table...

A copy of this was sent to Jimmy <c6635500_at_comp.polyu.edu.hk> (if that email address didn't require changing) On Wed, 01 Sep 1999 23:29:42 -0700, you wrote:

>Hello all,
>
> I have a table AAA with over 50000 rows. Assume the table structure
>of table AAA is as follows:
>
> a number;
> b varchar2(100);
> c varchar2(100);
>
> Now I want to retireve the N record of this table by using the
>following SQL statement in SQL*Plus:
>
> select whole_str from (select a || b || c whole_str, rownum rowno
>from AAA) where rowno = &N;
>
> It returns a string with A + B + C with record number N in table
>AAA. However, the time to do this query, as I expected, takes so long to
>run.
> (in fact, in my case, the table AAA may have over 30 columns)
>
> How can I rewrite this SQL statement such that it can improve the
>query time? Can I get the same results by using rowid? (I think using
>rowid is the fastest way)
>
>Thanks,
>Jimmy
>

There is no such thing as the N'th row of a table.

This takes a long time because it builds the whole subquery and then finds the row that just happened to get assigned that number you are looking for (don't be surprised when, after some activity on the table, the N'th row changes -- rows are *not* added to the bottom of the table in general).

You should have a column that represents your concept of the N'th row.

short of that, try:

select * from ( select a||b||c whole_str, rownum rowno from aaa WHERE rownum <= &n ) where rowno = &&n;

At least, for small N, this will run faster. it stops building the subquery when its found N rows and then returns the last one.

Be much better if you could just say:

select * from AAA where some_colum_that_means_something_to_you = &n;

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Sep 01 1999 - 12:05:19 CDT