Hi

Shailesh if you need the rows sorted you should be using order by.The plan change is a expected behaviour i do not see anything wrong with it at all.

Does this mean to maintain default sorting of queries we need use optimizer first_rows and not all_rows on 10g?
Nope if you need sorting you should use order by period.
optimzer_method should be what was there in 8i that is first_rows only


Why all_rows does HASH JOIN (OUTER)than NESTED LOOPS (OUTER).

Because all rows means optimize for all rows and hash join is the correct plan.If you make this change in 8i also i beleive you should get HASH JOIN (i would be surprised if you didnt)

Why same data structure has different explain plan on 8i and 10g?
Because of the parameter OPTIMIZER_MODE

and to maintain same query plans of 8i on 10g what we need to do?
set the optimzer_mode to same in 8i and 10g
and also use optimzer_features_enable=8i

But plans will change as with every version of oracle the optimzer is supposed to get smarter..;-)



regards
Hrishy