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: selecting a column according to a minimum

Re: selecting a column according to a minimum

From: Martin Dachselt <dachselt_at_firstgate.de>
Date: 20 Oct 2004 10:37:16 -0700
Message-ID: <bdafd998.0410200937.6a7356dc@posting.google.com>


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

Original text of this message

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