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

Try this:

declare
cursor c is
  select /*+ FIRST_ROWS */ title
    from news
 begin
  for c1 in c
  loop
    dbms_output.put_line(c1.title);
    exit when c%rowcount = 20;
  end loop;
end;

This is how oracle recommends you do these sort of .... It is very fast and I use it a lot in WEB Based Apps. The above exapmle returns the first 20 records but it can easily be adapted to your needs.

Hope this helps.

"For the best jobs in IT please visit http://www.jobs-it.net"

Jimmy 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
Received on Wed Sep 01 1999 - 17:59:53 CDT