Home » SQL & PL/SQL » SQL & PL/SQL » Order by is killing performance
Order by is killing performance [message #313490] Sun, 13 April 2008 02:56 Go to next message
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 #313497 is a reply to message #313490] Sun, 13 April 2008 03:38 Go to previous messageGo to next message
mm_kanish05
Messages: 487
Registered: January 2007
Location: Chennai
Senior Member

hi

Send your Workings.

Have you any index on your table.

kanish
Re: Order by is killing performance [message #313501 is a reply to message #313490] Sun, 13 April 2008 04:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Enlarge your sort area size.

Regards
Michel
Re: Order by is killing performance [message #313548 is a reply to message #313497] Sun, 13 April 2008 12:13 Go to previous messageGo to next message
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 #313550 is a reply to message #313490] Sun, 13 April 2008 12:36 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW posting guidelines as stated in above URL.
Re: Order by is killing performance [message #313553 is a reply to message #313548] Sun, 13 April 2008 12:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64109
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I am trying to look into other ways to improve performance.


Michel Cadot wrote on Sun, 13 April 2008 11:20
Enlarge your sort area size.

Regards
Michel


Re: Order by is killing performance [message #313613 is a reply to message #313553] Sun, 13 April 2008 23:38 Go to previous message
rleishman
Messages: 3724
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
Previous Topic: Need INSERT Query
Next Topic: i need result in concat ,manner
Goto Forum:
  


Current Time: Mon Dec 05 09:10:14 CST 2016

Total time taken to generate the page: 0.04714 seconds