Re: selecting a column according to a minimum

From: Holger Baer <holger.baer_at_science-computing.de>
Date: Wed, 20 Oct 2004 13:01:53 +0200
Message-ID: <cl5gj1$6i2$1@news.BelWue.DE>

Martin Dachselt wrote:
> "Agoston Bejo" <gusz1_at_freemail.hu> wrote in message news:<cl2m1n$tbk$1_at_news.caesar.elte.hu>...
>

>>Actually, here is what I could come up with:
>>
>>select i from
>> (SELECT i, j FROM T WHERE [condition] ORDER BY j )
>>WHERE ROWNUM = 1
>>
>>Is this very inefficient?
>>

>
>
> According to oracle doc the rownum is computed before the "order by" statement
> is applied.

Not if the order by is in a inline view:

baer_at_DEMO10G>create table test (i integer, j integer);

Table created.

baer_at_DEMO10G>begin

   2 for x in 1..100 loop
   3 insert into test values (x, 100-x);    4 end loop;
   5 end;
   6 /

PL/SQL procedure successfully completed.

baer_at_DEMO10G>commit;

Commit complete.

baer_at_DEMO10G>select i from (select i,j from test order by j)

   2 where rownum=1;

          I

---

        100

baer_at_DEMO10G>select min (j) from test;

     MIN(J)

---

          0

baer_at_DEMO10G>select i from test where j=0;

          I

---

        100

>
> Perhaps better:
>
> select i,j from
> (select i,j,row_number() over (order by j) rn)
> where rn=1;
>

Not without additional input for the optimizer:

baer_at_DEMO10G>select i,j from

   2 (select i,j,row_number() over (order by j) rn from test)    3* where rn=1

Execution Plan

---

    0      SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=100 Bytes=390
           0)

    1    0   VIEW (Cost=3 Card=100 Bytes=3900)
    2    1     WINDOW (SORT PUSHED RANK) (Cost=3 Card=100 Bytes=500)
    3    2       TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=2 Card=100
            Bytes=500)

baer_at_DEMO10G>select i from (select i,j from test order by j) where rownum=1;

Execution Plan

---

    0 SELECT STATEMENT Optimizer=CHOOSE (Cost=3 Card=1 Bytes=13)     1 0 COUNT (STOPKEY)

    2    1     VIEW (Cost=3 Card=100 Bytes=1300)
    3    2       SORT (ORDER BY STOPKEY) (Cost=3 Card=100 Bytes=500)
    4    3         TABLE ACCESS (FULL) OF 'TEST' (TABLE) (Cost=2 Card=1
           00 Bytes=500)

Note the appearence of STOPKEY: The optimizer nows that with rownum=XY you will likely not take all of the result. This might lead the optimizer to favour a first_rows plan.

Regards,

Holger Received on Wed Oct 20 2004 - 06:01:53 CDT