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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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_at_gmail.com> 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_at_gmail.com>
> 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