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

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

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

--

http://www.freelists.org/webpage/oracle-l Received on Thu May 10 2007 - 10:19:17 CDT