Originally posted by shailesh
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
when u use first_rows, optimizer is using index scans and hence u might be seeing ** ordered ** data ( though its not gauranteed unless u use order by )

first_rows will look for optimization of getting first few records fast.. and is index prone.. it will effect the performance if you need complete set of results..

all_rows is ideal and is * kinda * fts prone..

hint : adjsut optimizer_index_cost_adj & optimizer_index_caching per your application needs..

Abhay.