RE: 10g ORDER BY Performance (not sure about that subject line)

First, I'm a bit surprised that disabling hash aggregation didn't force sort
aggregation which should produce an ordered set of groups as a side effect
(not to be confused with valid SQL and specifically warned against being
relied on by Oracle since at least November 1988.) I would check the plan
generated and v$parameter to be certain the setting is being effectively set
simultaneously with getting an unordered result set.

Did I get it correct from the original post that these all involve group by
statements?

If verifying the plan and parameter setting yields no joy, then it seems
likely that a sed, awk or perl script to pass across your script library and
generate a ORDER BY that matches the GROUP BY is not too much of a stretch.
Since you have over 1000 scripts it seems likely this is less error prone
than attempting hand repairs.


-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Peter Barnett
Sent: Friday, August 24, 2007 1:49 PM
To: Oracle-l
Subject: Re: 10g ORDER BY Performance

We already have that set for another problem and still
have the unordered sort issue.  

This is really a coding issue but we dno't have time
to fix it.

--- Ghassan Salem <salem.ghassan@xxxxxxxxx> wrote:

> It's not the new sort that doing this, it's the
> 'hash group by'
> try disabling it using
> alter ... set "_gby_hash_aggregation_enabled"=false;
> 
> 
> On 8/24/07, goran bogdanovic <goran00@xxxxxxxxx>
> wrote:
> >
> >
>
http://jonathanlewis.wordpress.com/2007/06/03/sorting/
> >
> > you may set it for a session too:
> >
> > alter session set "_newsort_enabled"=false;
> >
<snip>



--
http://www.freelists.org/webpage/oracle-l


Other related posts: