RE: Does an upgrade of the db version affect index organization or number of entries returned from an index in a query?
- From: "Mark W. Farnham" <mwf@xxxxxxxx>
- To: <anelson77388@xxxxxxxxx>, "'Dennis Williams'" <oracledba.williams@xxxxxxxxx>
- Date: Thu, 10 May 2007 14:26:57 -0400
inline
_____
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
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.
Other related posts:
