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

Very late entry into this discussion:

From other comments that Allan has made it looks as if 9.2.0.8 has a few extra tricks for using complex view merging. This could change the join order in some execution plans - and if the IOTs were being accessed earlier in the plan the number of predicates available for access could have been reduced, leading an increased number of row accesses with filtering at a later table in the join.

Plan B - there were some cases where predicates in 8i that referenced index columns had to be operated as filter predicates. 9i introduced enhanced code that made it possible in these cases to use the predicates as access predicates. Maybe the change has been lost in the upgrade.

For both cases, check the access predicates and filter predicates of the execution plans very carefully.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- ---
  >
  > Date: Fri, 25 May 2007 15:10:14 -0400
  > From: rjamya <rjamya_at_gmail.com>
  > Subject: Re: Does an upgrade of the db version affect index organization or
  > num
  >
  > We brought back one database to 9204 from 9208 for performance issues. There
  > is a tar open where we proved that when you go from 9204 to 9208, db reads
  > went up almost 4 times as it can be seen in statspack, oracle is working it.
  > rjamya
  >
  

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