How to return first 500 records from a query having 3 or 4 joins

[sukhveer_ebix]

Hi Thomas,

I agree with you, but in above solution inner sub query will fatch all 60,000 records (WITH ORDER BY) and then will filter out only 500 records.

Is there any way in which oracle work just for 500 records (in a particular order), and should not work (fatch) additional 59,500 records?

Thanx in advance.
Sukhveer Singh

[Thomasps]

woopss... How Oracle do an ORDER BY without READ ?????

[slimdave]

I agree with you, but in above solution inner sub query will fatch all 60,000 records (WITH ORDER BY) and then will filter out only 500 records.

Is there any way in which oracle work just for 500 records (in a particular order), and should not work (fatch) additional 59,500 records?

If Oracle can work out a way of reading the records in the required order by using an index then it can do it, as long as it knows that that is what you want. The first_rows hint will help with that. Probably though it will not, so the entire result set will have to be read and sorted, hence there will be little performance benefit to restricting the query to the first 500 rows.