RE: Index rebuilding

  • From: "Mark W. Farnham" <mwf@xxxxxxxx>
  • To: <breitliw@xxxxxxxxxxxxx>, <steve@xxxxxxxxxxxxxx>
  • Date: Fri, 12 Nov 2004 09:09:44 -0500

Wolfgang's approach is a clear scientific approach to determining what is
going on.

One possible shortcut to carefully tracking it next time: Are you right on
the bubble from one depth to another?

If so, then it is entirely possible that a rebuild makes the index "better"
for a short period of time but minor insert/update/delete even at a steady
state net size make the next analysis a one deeper and the cost is won by an
alternate path.

If that is the case, and if things slow down a lot when the plan tips, then
you need to figure out whether some cost is being mis-described to the
optimizer. If things don't slow down a lot, then the new plan may in fact be
the better plan now that the index is deeper. If the steady state of your
index is very near a depth change, you have to figure out whether your
operational schedule and machine cost to rebuild the index is worth putting
the rebuild on a regularly scheduled cycle. Usually it is not, but that is
something you figure out from actuals. Now I've diverged from the general
case to a shot-in-the-dark possible example. Wolfgang's approach will
terminate in an accurate answer. Sniffing around for hunches can go on
forever, but sometimes terminates very quickly. Balancing your approach with
a quiver of likely quick check hunches is not a problem as long as you
remember to fall back on the scientific approach when you're stumped and you
don't spend an uneconomic amount of time on hunches. As the CBO is improved,
there is less and less chance that it is making insane decisions.



-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Wolfgang Breitling
Sent: Friday, November 12, 2004 7:10 AM
To: steve@xxxxxxxxxxxxxx
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Index rebuilding

I suppose you analyze religiously between the date you rebuilt the index
and the date the CBO decides not to use the index anymore?
Provided this assumption is correct, then
a) can you post the evolution of the statistics (table, index and all
relevant columns, i.e. columns appearing in predicates)
b) what happens if you DON"T analyze after having rebuilt the index? Does
the CBO still switch to a full scan after a few days?

At 03:45 AM 11/12/2004, Steve Jelfs wrote:
>What happens is that we build the index and queries use the
>index.  Then, after a couple of days the CBO reverts to full table
>scans/.  Re-analysing the table and index does not change anything but
>re-building the index does.


Wolfgang Breitling
Centrex Consulting Corporation



Other related posts: