If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

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

Hi,

Can someone help me to find a way to reterive first 500 records from a query having 3 or more then 3 joins. By default query is returing more then 60000 records,and taking enough time to execute.
Is there any way in which oracle work to reterive first 500 records and should not work for rest of the records, so that query execution time can be much faster.

Thanx for reply, rownum is a solution, but rownum will first make join for all the records (let say in my case more then 60,000) and then it will filter out first 500. so the query execution time will remain same. Pls correct me if i am wrong?

Thanx for reply, rownum is a solution, but rownum will first make join for all the records (let say in my case more then 60,000) and then it will filter out first 500. so the query execution time will remain same. Pls correct me if i am wrong?

How much work you save, and hence the performance improvement, would depend on the join mechanism of course. It wouldn't save you much on a hash join or a sort-merge, but on a nested loop it will. The first-rows hint could well change the join mechanism to one that will promote savings due to the rownum predicate.

Will this query give me exact results every time in the same order?
I think oracle will get the result in defined order and then will return me first 500 records. But this is not the optimise way i believe.
Is there any way to do this.