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

  • From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Sat, 26 May 2007 17:06:45 +0100

Very late entry into this discussion:

From other comments that Allan has made it looks
as if 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.


Jonathan Lewis

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ

----- Original Message ----- ---

Date: Fri, 25 May 2007 15:10:14 -0400
From: rjamya <rjamya@xxxxxxxxx>
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.


Other related posts: