Optimizer & rownum & view

[Flyby]

Hello,

Field1, part of table3, is indexed.
Any clue why the optimizers chooses a full tablescan when the rownum is part of the view, and an index scan when not?
It looks like it is precalculating all the rownums

Myview AS

select rownum, field1,field2,field3
from (
select field1,field2,field3 from table1 t1 join table2 t2 on t1.id=t2.t1_id join table3 on t3.t2_id=t2.id where Field1 not like 'X%'
union select field1,field2,field3
from table1 t4 join table2 t2 on t4.id=t2.t4_id join table3 on t3.t2_id=t2.id join table1 t1 on t1.id=t4.id_base )

select rownum,field1,field2,field3 from myview where field3='Z' ->Cost 32.000 due full tablescans

*recreate the view without rownum in the definition
select rownum,field1,field2,field3 from myview where field3='Z' ->Cost 20
Scan on Field3

*After reading the workings of rownum
http://asktom.oracle.com/pls/asktom/...D:948366252775
it makes sense (fetch all records and apply rownumbering)
rownum = 1
for x in ( select * from A )
loop
if ( x satisifies the predicate )
then
OUTPUT the row
rownum = rownum + 1
end if;
end loop;



rownum is assigned during the predicate evaluation - but before any sorting/aggregating is performed. That is why:


select * from t where rownum <= 10 order by x;

is very very very different from

select * from (select * from t order by x) where rownum <= 10;