Re: selecting a column according to a minimum

>
> baer_at_DEMO10G>select i from test where j=0;
>
> I
> ----------
> 100

Yeah, you are right.
>

> 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.
>

Strange: costs and execution time seems bo be equal, even for big tables.
I thought the optimizer is guessing disk and cpu usage.

select * from (
select id,to_char(datetime,'yyyy-mm-dd hh24:mi:ss') from fdetailrecord order by id
) where rownum < 5;

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=82560 Card=4 Bytes=2
          10937680)

   1    0   COUNT (STOPKEY)
   2    1     VIEW (Cost=82560 Card=8789070 Bytes=210937680)
   3    2       SORT (ORDER BY STOPKEY) (Cost=82560 Card=8789070 Bytes
          =123046980)

   4    3         TABLE ACCESS (FULL) OF 'FDETAILRECORD' (Cost=61711 C
          ard=8789070 Bytes=123046980)

select * from (
select id,to_char(datetime,'yyyy-mm-dd hh24:mi:ss'),row_number() over (order by id) rn from fdetailrecord
) where rn < 5;

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=82560 Card=8789070 B
          ytes=325195590)

   1    0   VIEW (Cost=82560 Card=8789070 Bytes=325195590)
   2    1     WINDOW (SORT PUSHED RANK) (Cost=82560 Card=8789070 Bytes
          =123046980)

   3    2       TABLE ACCESS (FULL) OF 'FDETAILRECORD' (Cost=61711 Car
          d=8789070 Bytes=123046980)

One advantage of the solution with analytic functions, is that you can do:
select * from (
select id,to_char(datetime,'yyyy-mm-dd hh24:mi:ss'),row_number() over (order by id) rn from fdetailrecord
) where rn between 5 and 10;

Cu

Martin Received on Wed Oct 20 2004 - 12:37:16 CDT