| Order by is killing performance [message #313490] |
Sun, 13 April 2008 02:56  |
hedonist123
Messages: 119
Registered: August 2007
|
Senior Member |
|
|
Hi,
I have a query which takes 7 seconds to execute without order by and 230 seconds to execute if order by clause is added.
However, the business requirement states that the order by clause is reqeuired.
Is there some way it could be implemented without such an impact on performance?
Thanks,
Sharath
|
|
|
|
|
|
|
|
| Re: Order by is killing performance [message #313548 is a reply to message #313497] |
Sun, 13 April 2008 12:13  |
hedonist123
Messages: 119
Registered: August 2007
|
Senior Member |
|
|
Kanish,
The explain plan of the query shows that there is a full table access on the table with the largest number of rows in the query.
There is a join on a column on this large table, however an index on this column does not improve performance as all the values in this column are the same.
A few others told me that the Oracle optimiser is selecting the full table access way out as it feels it is the best way.
As a result, I am trying to look into other ways to improve performance.
Sharath
|
|
|
|
|
|
|
|
| Re: Order by is killing performance [message #313613 is a reply to message #313553] |
Sun, 13 April 2008 23:38 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
Are you sure it takes just 7 seconds without the ORDER BY to return EVERY row? Or is it just 7 secons until the FIRST row appears?
With an ORDER BY, Oracle must retrieve the entire result set before it can show the first row. This can make it SEEM much longer, whereas the end-to-end is not much different.
Trace both sessions and check TK*Prof to get the true performance comparison.
Ross Leishman
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
