RE: 10gR2 Upgrade .. Watch out

On Wed, 2006-12-27 at 18:30 -0500, Mark W. Farnham wrote:

> 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.

Mark, apparently Oracle did this already. They know how developers either do the knowledgeable thing as you described, or follow the ignorant cheap way of taking group by sorts for granted. From a post of Jonathan Lewis, back in July this year (subject: 8i to 10g migration.)

<quote>

Considering only the optimizer the most likely problems on a migration from 8i to 10g come from:

<snip>

hash aggregation - if you have code that gets the data in the right order after doing a group by without an order by, you may now find the data coming out in the wrong order because oracle has switch to "hash group by" instead of "sort group by". Add order by clauses - or disable hash aggregation by setting

   _gby_hash_aggregation_enabled = false

</quote>

Best regards,

Carel-Jan Engel

===
If you think education is expensive, try ignorance. (Derek Bok) ===

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