RE: 10gR2 Upgrade .. Watch out
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <gkatteri@xxxxxxxxxxx>, "Gints Plivna" <gints.plivna@xxxxxxxxx>
- Date: Wed, 27 Dec 2006 18:30:53 -0500
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]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@xxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
