RE: 10gR2 Upgrade .. Watch out

The order is an artifact of the resolution algorithm for grouping. When Oracle only had sort as a mechanism, everything came out in the order associated with the sort (though it might have subtle strangeness if the binary sort order and the character set sort order were different, since without a specified order by Oracle was free to use either.

Now hash algorithms are not new to 10gR2, but trends in the CBO to actually use hash buckets when hash buckets are cheaper than a sort and folks setting up bigger hash caches so more hash sorts are scored as memory operations leads you to see the disappearance of the sort as an artifact more often.

Now why did folks ever leave out the order by?

Because if you notice that it is possible for the binary sort order to be used for group by and a different order for order by, then if you group by and order by Oracle actually has to do the work twice. Since it is nearly the same order by the order by time, the second time is nothing like n log n, but it is a pass over the data. So there was a note in the, ahem, November 1988 DBA guide that it could help performance to leave out unneeded sorts (without getting into details about the binary versus character set sort orders). With the tiny memory areas DBAs could wrench unwillingly from management and sysadmins of that ancient age, those "extraneous" (not really, but you often could live with the results, in fact for a lot data the results are identical) sorts took a lot of extra time on the drives of the day. Now this is long ago enough that I've probably got it slightly wrong in some regards, but that's the way I recall it, and I definitely personally speeded up a bunch of stuff by removing sorts associated with group bys circa 1988. I left warnings behind in the code, and even left in the order bys in as comments. But the onus has *always* been on the person leaving out the sort to assure that the results fit the bill, and even in 1988 the manuals warned you that Oracle could change or add new algorithms in the future.

I think it is a honorable task to alert all our fellows to the danger that the more prevalent use of non-sorting algorithms tends to reveal application choices that were verified to work in the past.

I do not think it is fair to hang this in any way shape or form as a problem on Oracle.

I do think it would be excellent customer service for Oracle to add whole database and session switches to talk to the optimizer and say "always sort after a hash group by", etc., and thereby avoid forcing customers to have to precipitously review all sql everywhere that contains group by syntax to determine whether the application requires sorted output. Certainly everything is in hand at plan creation time to cheaply add the sort, and yet another switch could tell the database whether you want logging of who, what, and where when the database software "fixes" your code dynamically.

In my humble opinion this could set a record for the lowest ratio of cost to develop to savings to customers in the history of computing. No, I'm not joking and there is a ratio you can take to the bank.

Regards,

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of GovindanK
Sent: Wednesday, December 27, 2006 5:54 PM To: Gints Plivna
Cc: oracle-l
Subject: Re: 10gR2 Upgrade .. Watch out

In 9iRel2 (irrespective of what the manual used to say) the order by was taking place.. if the manual says it does not guarantee, it should not then sort it every time and produce the result .. yes i agree that unless "order by" is mentioned it should not sort .. but the 9iRel2 optimizer was doing it (so too 10.1.0) .. this was not happening with 10.2.0 ..

 Remember one thing .. if the manual says the same for 9.2 and 10.2 as  you had mentioned . the default behaviour should not change across  upgrades .. correct .. BUT that is not the case.. Hope this clarifies..

On Thu, 28 Dec 2006 00:45:58 +0200, "Gints Plivna" <gints.plivna_at_gmail.com> said:
> 2006/12/28, GovindanK :
> > 1. Group by was not doing the sort in the default order where as 9.2.0.6
/
> > 9i Rel2 was doing so
> > 2. Same problem with SELECT DISTINCT / SELECT UNIQUE
>
> Mhmmmm what is the default sort order for group by and distinct/unique?
> Docs at least for 9.2 and 10.2 have the same sentence for group by
> "The GROUP BY clause groups rows but does not guarantee the order of
> the result set. To order the groupings, use the ORDER BY clause."
> Cannot find on the spot the same for distinct/unique but I'm sure
> similar sentence applies here as well.
>
> I'm by no means saying that 10.2 has no problems/bugs, but to me these
> two seems quite normal behaviour :)
>
> Gints Plivna
> http://www.gplivna.eu

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



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