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