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

The 9.2.0.8 upgrade ran too fast for it to have a bunch to do with stuff out side of the sys and system schemas. I have been looking at quite a few query plans. Some join strategies have been re-costed so that very different explain plans come out. Views being referenced in the from clauses also seem to have "benefitted" from the CBO upgrade. Some explain plans where the old CBO had materialized a view of its own are missing in 9.2.0.8. It really seems like quite a bit of code in CBO changed.

Allan

On 5/10/07, Mark W. Farnham <mwf_at_rsiz.com> wrote:
>
> 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