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

The real question in this situation (and I'll ask it this time) ia always: WHY?

If you insist on using a relational database as a sequntial file, you can expect the performance hits. If you must do this, you might be better off dumping the table to a file and using seek() in a C program to fetch each one in turn.

ROWNUM is assigned by the DB as the record is fetched. (so even oracle doesn't know which row is number 4973 until it has read the 4972 rows before it.) ROWID has no real order (ie rowid is practically UNrelated to the fetch order) If you insist on doing it from that SQL statement, you are stuck. (there's nothing in the ROWID that tells you you are at record 1, let alone record 4793).

In article <37CE191D.EC82612C_at_comp.polyu.edu.hk>,   Jimmy <c6635500_at_comp.polyu.edu.hk> 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
>
>

--
Ed Prochak
Magic Interface, Ltd.
ORACLE services
440-498-3700 <<<NOTE new number

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Wed Sep 01 1999 - 12:17:11 CDT