Re: Order By Bogs down the Statement

In article <19981105202414.19650.00000018_at_ng134.aol.com>,   markp28665_at_aol.com (MarkP28665) wrote:
> From: "Rich McGillicuddy" >>
> These queries run real fast except if I add an order by to the SQL Statement.
> Why is this and how can I speed it up. <<
>
> When you run a query without an order by clause Oracle can start spooling the
> results back to you as soon as Oracle determines that a row meets all
selection
> requirements. When you add an Order by clause Oracle can not send any results
> to the calling application or screen until it has located all rows and sorted
> them.
>
> So, 1) Make sure you are timing the actual completion of the query and not
just
> the start of spooling of the results
> then,
> 2) look at your init.ora parameter setting for the amount of memory Oracle
> allocates to user processes for sorting: sort_area_size and
> sort_area_retained_size. You should have at least 64K. You do not need a
huge
> retained sort area since every session reserves this space weather it sorts or
> not, and does not release it. What you want is for 99+% of all sorts to take
> place in memory. Large sorts are going to have to go to disk on most systems
> so...
> 3) If you do not have a seperate sort tablespace create one
> 4) Check that the initial and next extent are set equal with a pctincrease of
> 0 and that they are set to a decent size, 512k or larger. Later you can
> research declaring the tablespace temporary.
>
> I hope these suggestions help, and since these queries are running under a
> personal Oracle you can probably use a meg for sort_area, but if the queries
> will be moved into a production instance you may not want to do that.
>
> Mark Powell -- Oracle 7 Certified DBA
> - The only advice that counts is the advice that you follow so follow your own
> advice -
>
>

Hi Rich and Mark,

one other aspect to consider is that the order by clause may cause your query to not use indexes anymore.

order by clauses must meet to rigid requirements if indexes should still be used :

1. all of the columns that make up the order by clause must be contained within a single index in the same order
2. all of the columns that make up the order by clause must be defined as NOT NULL in the table. NULL values are not contained within an index

Regards

--
 Oliver Willandsen
European Commission
http://europa.eu.int

All comments represent my own opinion and may not in any circumstance be regarded as stating an official position of the European Commission

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Nov 06 1998 - 03:23:49 CST