RE: Does an upgrade of the db version affect index organization or number of entries returned from an index in a query?

inline  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Allan Nelson
Sent: Thursday, May 10, 2007 12:41 PM
To: Dennis Williams
Cc: oracle-l
Subject: Re: Does an upgrade of the db version affect index organization or number of entries returned from an index in a query?  

Oh, I'm sure the CBO changed alrighty, I have bunches of queries that got mugged. My question was about factual issues:

Does the upgrade from one version of the db to another touch indexes, or other objects, outside the data dictionary?  

>>> rarely, and usually announced with great fanfare such as the change in
the rowid storage definition and the deprecation of an earlier method of storing LOBs out of line. They don't do this lightly.

>>> I expect the next big one to be repairing the identity of empty strings
with the NULL information value so Cary will stop puking a little bit in his mouth when he thinks of it. (Me too, and oddly we both like spicy >>> condiments. Of course that one will have to be optional so 90% of Oracle queries and applications don't break, but that is part of the "puking a little bit in my mouth" bit.

and
Can index organization affect the number of entries used in a query execution plan.  

>>> Not sure exactly what you mean, but if, for example, an index rebuild
removed a lot of honeycomb space in the leaves to the point that it reduced the blevel there should be a significant change in the cost >>>evaluation for various range accesses and the like. Don't interpret this as advice to do so in a particular situation, but it is possible. But the upgrade you noteshould not have done anything to your indexes,

>>> anyway. As you already figured out the CBO changed, to favor hash joins
more if I recall - but Wolfgang Breitling and probably Jonathan Lewis have details on the CBO point change effects at their fingertips >>> whereas I do not, so maybe they'll fill you in on the pain points and joy points of your particular upgrade. 9206 to 9208 was it, right?

I think Oracle is telling me stories.

>>> It seems like the game is to see who can get you off the phone fastest
and give you advice that delays your return call as long as possible. I miss Randy Baker and Ray Lane.  

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