Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Retrieve N record from a large table...

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

From: Stephane faroult <sfaroult_at_oriolecorp.com>
Date: Fri, 03 Sep 1999 01:22:12 -0700
Message-ID: <37CF8533.1A67@oriolecorp.com>


Eike J. Heinze wrote:
>
> Hi;
>
> The only way I know of would be a PL/SQL procedure.
> Write a cursor, read and count the rows until you have it.
> But please: WHY this? Did somebody throw a Cobol (or whatever...)
> program and it's data into Oracle? Never worked...
>
> Regards
> Eike
>
> 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
>

If you are to believe in the Gospel according to St Ted Codd, the order of storage in a relational table is totally irrelevant; to a relational purist, I could even say that the idea of 'nth' or even 'next' or 'previous' row is heresy. Many people were burnt to death in the middle-ages for much less.
The notion of 'rownum' is particularly elusive. Indeed, it is nothing else than a counter incremented by Oracle as it fetches rows (but before it sorts them, which explains that trying to display the top 10 values using the rownum doesn't work). This is why WHERE ROWNUM < 2 returns something and WHERE ROWNUM > 1 nothing on a non-empty table. In Jimmy's example, there is a full scan of the table to collect all rownums. In fact 'select a || b || c from AAA where rownum = N' would work, but it implies a full scan of the table until the said Nth row is returned - the higher N the longer (similar to Eike's PL/SQL suggestion). If rows are deleted from your table and reinserted, or if the table is reorganized, or if you add a processor to your machine with the parallel query option, the result of your query may be something one day and something different the next! Is this really what you want? The only proper way to proceed, if the order of insertion matters to you (I presume it is why you want the Nth row) is to add a 'num' column and to have it automatically set by a sequence. The only problem is that if a rollback (or a shutdown, even a clean one) of your database occurs, you will have gaps. If you do not want any 'hole', and assuming that contention will stay bearable, the only safe way to proceed is to have a small table to hold the next 'num' to insert which you increment in the same transaction :

      select val + 1
      into v_next_num
      from next_num
      for update of val;
      insert into AAA
      values (v_next_num, ....);
      update next_num
      set val = v_next_num;
      commit;

This is safer than a select max(num) + 1 which may return the same value for several users since you see the old value as long as a change has not been committed; here locks prevent this (an old Oracle 5 technique ...).
If the 'num' column is indexed, you will then get fast and consistent results ...
--
Regards,

  Stéphane Faroult
  Oriole Corporation
  Performance Tools & Free Scripts



http://www.oriolecorp.com, designed by Oracle DBAs for Oracle DBAs
Received on Fri Sep 03 1999 - 03:22:12 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US