Home » SQL & PL/SQL » SQL & PL/SQL » Does order by slow down searches (10g)
Does order by slow down searches [message #304594] Wed, 05 March 2008 15:21 Go to next message
Messages: 37
Registered: February 2007
Hi there. I have a rather big select statement (returns a few thousand rows) that looks at a few tables and returns a lot of columns from each. I need to do a group by as part of my select but will adding order by slow down my search?

I think I can do without ordering it but I wanted to see if order by has an effect on anything.
Re: Does order by slow down searches [message #304596 is a reply to message #304594] Wed, 05 March 2008 15:27 Go to previous messageGo to next message
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Order by has no effect on your search but it will have an additional impact on your output becauase you are asking oracle to order it for you. If you are not interested in the output order then don't use order by.

Also please take some time to read the oracle sql reference manual. It's all explained there in detail.


Re: Does order by slow down searches [message #304646 is a reply to message #304596] Thu, 06 March 2008 00:20 Go to previous message
Messages: 3727
Registered: October 2005
Location: Melbourne, Australia
Senior Member
If the ORDER BY columns are a subset of the GROUP BY columns - even if they are in a different order - then as of 9i, Oracle will combine them into a single sort. No overhead.

However if you ORDER BY a column not present in the GROUP BY (eg. order by an aggregate function), then you will get another SORT which will take longer.

Ross Leishman
Previous Topic: Question on ROWNUM.
Next Topic: what is the difference between "REF CURSOR" AND "SYS_REFCUSOR"
Goto Forum:

Current Time: Sat Jul 22 18:13:10 CDT 2017

Total time taken to generate the page: 0.11833 seconds