Does an upgrade of the db version affect index organization or number of entries returned from an index in a query?
- From: "Allan Nelson" <anelson77388@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 10 May 2007 10:19:17 -0500
I'm working on an SR with Oracle because a number of key queries got their
execution plans mugged in an upgrade from 9206 to 9208. I got the following
in the SR:
After having upgraded to 9.2.0.8, the optimizer is going for hash joins and
also indexes are accessing more rows than
expected. It could be due to the fragmentation that could occur during
upgrade.
So you could rebuild the indexes involved or atleast rebuild the following
inde
xes.
MI_WSH_DELIVERY_DETAILS_N1
MTL_SYSTEM_ITEMS_B_N14
MTL_SYSTEM_ITEMS_TL_N1
MTL_UNITS_OF_MEASURE_TL_U2
WSH_DELIVERY_DETAILS_N3
--alter index <index_name> rebuild;
I'm confused by two aspects of this statement. First, I thought an upgrade
from one version of the db to another affected the data dictionary but not
other tablespaces. So I don't see how these indexes from E-Business suite
would have been touched. Is my understanding defective? Next, I would not
think that the organization of an index would affect the number of entries
returned from it. It might affect the optimizer choosing the index in the
first place but I would think for a given query that the predicates in the
query plus the join order would be what would affect the number of index
entries used to resolve a query. Again, is my understanding defective?
Allan
Other related posts:
