Thanks Hrishy for your reply.

As I have mentioned in my thread, on Oracle 8i optimizer is CHOOSE and on 10g ALL_ROWS.

On 8i since query uses Nested loop outer join, uses index and so output shown in sorted order.

Same query uses hash join outer on 10g because optimizer is ALL_ROWS, doesn't use index and query output is doesn't appear in sorted order.

If I set optimizer mode first_rows on 10g, I get result in sorted order.

Now since ours is OLTP environment we may benifit if we set optimizer first_rows on 10g, but can anyone elaborate pros/cons of first_rows over all_rows.

Thanks & Regards,

Shailesh