(1) no, that isn't the reason, the rownum is not, will not, be pushed into the inner query.
that query says "get a distinct list of values, return the first three"
maybe when you generate the distinct list of values, it generates "a,d,c,b,e,f,z" and maybe when it does it for me, it generates "z,f,b,e,c,a,d". Your first three are a,d,c, mine are z,f,b
distinct doesn't have to sort, the list of returned items doesn't have to be in the same order every time
2) semantically, they are the same, but the group by currently can be better optimized. for example:
ops$tkyte%ORA11GR2> drop table t;
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create table t as
ops$tkyte%ORA11GR2> select rownum x, a.* from all_objects a;
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> create index t_idx on t(x);
ops$tkyte%ORA11GR2> exec dbms_stats.gather_table_stats( user, 'T' );
tkprof will show:
select * from (select distinct x from t where x > 1) where rownum <= 3
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.06 0.06 0 169 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.06 0.06 0 169 0 3
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
3 3 3 COUNT STOPKEY (cr=169 pr=0 pw=0 time=63797 us)
3 3 3 VIEW (cr=169 pr=0 pw=0 time=63776 us cost=271
3 3 3 SORT GROUP BY STOPKEY (cr=169 pr=0 pw=0 time=6
72940 72940 72940 INDEX FAST FULL SCAN T_IDX (cr=169 pr=0 pw=0
********************************************************************************
select * from (select x from t where x > 1 group by x) where rownum <= 3
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 3 0 3
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 3 0 3
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
3 3 3 COUNT STOPKEY (cr=3 pr=0 pw=0 time=68 us)
3 3 3 VIEW (cr=3 pr=0 pw=0 time=58 us cost=2 size=39
3 3 3 SORT GROUP BY NOSORT (cr=3 pr=0 pw=0 time=53 u
4 4 4 INDEX RANGE SCAN T_IDX (cr=3 pr=0 pw=0 time=44
the group by stopped reading the index because the sort group by nosort step can start feeding the count stopkey step right away, whereas the distinct chose a sort group by stopkey - which processed all of the rows and then returned the first three it had. Some day in the future, the optimizer might look at those two queries and say 'they are the same', currently it does not.