| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Retrieve N record from a large table...
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
 |
 |