Re: SQL problem. HELP!

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1997/06/18
Message-ID: <33a823ee.9318266@www.sigov.si>#1/1

On Tue, 17 Jun 1997 21:24:15 +0000, Chrysalis <cellis_at_iol.ie> wrote:

>Arnold Jones wrote:
>>
>> Jerry,
>> The traditional way to find the highest NN rows in a table is:
>>
>> select col1,col2 from tab1 t where NN >= (select count(*) from tab1 tt
>> where t.col2 < tt.col2);
>>
>> Regards,
>> AJ.
>>
>> Jerry Glass wrote:
>> >
>> > (snip)
>
>Why do people use this incredibly inefficient mechanism? This requires

Because it's in pure SQL, whitch is still neaded sometimes.

>a full table scan of tt for each row of tt! Yet it keeps recurring in
>this NG.

I agree, it is terribly inefficient when a table is large, yet it is still one of the few UNIVERSAL sollution written in pure SQL that I've came across. When I say universal I mean it can be implemented for both TOP_N and BOTTOM_N records problems.

>The problem is that SQL*Plus does not give you direct access over the
>number of rows fetched (as opposed to defined), except for the rownum
>mechanism (which is no use for sorted sets). So why use SQL*Plus?

In some circumstances ROWNUM can be very usefull in such queries. For example, if index exists for the column you are ranking, you can very efficiently use ROWNUM and ORDER BY clauses with a single full table/index scan, for both TOP_N and BOTTOM_N problems.

Even if there is no index, yo can very efficiently use ROWNUM for BOTTOM_N queries. With a single merge join (using outer join with a single-row dummy table) you can get almost the same execution times on large tables as with procedural implementation with single full table scan, maybe even better.

>The *only* efficient way to do this is to use a single scan of the
>table. This can be done in a PL/SQL procedure (or a 3GL program) thus:
>
>declare
> cursor C is
> select ...
> from ...
> order by ...;
>
> ctr number := 1;
>begin
> for R in C
> loop
> ctr := ctr+1;
> if ctr > nn then -- required number of rows processed
> exit;
> else
> <process/output current row>
> end if;
> end loop;
>end;
>

As I said, in some circumstances pure SQL implementations can be as good as this one, even better. However, if one is looking for a GENERAL solution, then procedural implementation is the only way to go.

>Chrysalis.

Regards,

---

Jurij Modic                             Republic of Slovenia
tel: +386 61 178 55 14                  Ministry of Finance
fax: +386 61  21 45 84                  Zupanciceva 3

e-mail: jurij.modic_at_mf.sigov.mail.si Ljubljana 1000

---

Received on Wed Jun 18 1997 - 00:00:00 CDT