Re: Order By Performance Problem on View

I notice that the two plans appear to be the same expect for the second plan having the sort operation. When you add an order by you force Oracle to find all the data and sort it prior to returning any data to the application. The first query can start returning data as soon as it finds any even though the query is still running on the db.

So how are you measuring the run time? You should be measuring it for the last row being returned, not the first to get the true difference. The difference is the cost of the sort.

Tuning the query or eliminating the sort are your only performance options. What is the hash area size set to? If less than what you calculate for the size of the hash set then you could try to bump it up. You could also force the use of nested loops instead of a hash and see it that is better or worse.

That is the only general advice I can think of without really trying to figure out your query but without more knowledge of the data, the available indexes etc... I do not want to make any specific query rewrite recommendations.

HTH -- Mark D Powell -- Received on Fri Mar 18 2005 - 16:50:20 CET