Asktom:Index Rebuild and analyze

You Asked

Hello Tom ,

I have a query regarding Index rebuild .
what according to you should be time lag between index rebuilds.
We are rebuilding indexes every week .but we found it is causing lot of fragmentation.
is there any way we could find out whether we should rebuild the indexes and also what 
indexes we should rebuild instead of rebuilding all the indexes.

Please also suggest whether it is good to drop and recreate the indexes ( to avoid 
fragmentation ) or to rebuild them.
 

and we said...The time lag between index rebuilds should be approximately FOREVER.

Tell me -- why why why WHY are you rebuilding them on a schedule?  What is the scientific 
basis in reality that is driving you to do this????  

Here is an email I got once:


....
HELP!!!!  Riddle me this batman...

Why does an rebuilding an index cause increased redolog generation AFTER the index has 
been built?

I have a table 35 million rows and an index (nothing is partitioned) Transactions against 
this table are constant.  It's always 500,000 rows per day. This generally creates 10 
logs a day

Once a month the indexes are rebuit.  (Alter index rebuild)
On the day following the indexes rebuild 50 logs are created
On the following days 45...40...35...30....25....down to 10  at 10 logs this remains 
constant at 10 Mining the logs we see that we have increase INTERNAL INDEX UPDATES

Why does this happen??   Is this always the case??


So, what do you think was happening here?  Well, the fact is that indexes, like people, 
have a certain "weight" they like to be at.  Some of us are chubby -- some skinny -- some 
tall -- some short.  Sure, we can go on a diet -- but we tend to gravitate BACK to the 
weight we were.  The same is true for indexes -- what happened to this person is their 
index wanted to be wide and fat and EVERY MONTH they rebuilt it (put it on a diet).  It 
would spend the first half of the month then getting fat again and generating gobs of 
redo due to the block splits it was undergoing to get there.

In this case, rebuilding the index on their system had these effects:

o the system would generate 4.5 times the redo 
o the system would run slower
o the system would consume more resources (CPU, IO, latching, etc)
o the system would not be able to handle the same user load

until the system got back to where the system actually wanted to be.  And then -- AND 
THEN -- they (the dba's) would do it all over again!!!! They would destroy the 
equilibrium that the system worked so hard to get to.  Bravo!!


(can you tell what my opinion is on regularly scheduled index rebuilds???  I despise 
them).


If you are suffering from fragmentation -- your problem is not the index rebuilds.  It 
would be because you are using DICTIONARY managed tablespaces.  Switch over to locally 
managed tablespaces and you'll never have fragmentation again.  So, my suggestion -- one 
last index rebuild:

o create a locally managed tablespace
o alter index <iname> rebuild tablespace LMT_FROM_ABOVE storage ( initial 1k );

and then forget about them.  You will rebuild an index in response to IDENTIFIED and 
DEGRADED performance -- period.  You will not rebulid indexes based on a schedule.  
Period.

It is the RARE index that needs to be rebuilt.  
It is not a rule that indexes need to be rebuilt.

There are extreme cases, particularly with regards bitmapped indexes after mass data 
loads, where rebuilding is suggested.  But in a day to day system that is transactional 
-- the need to rebuild indexes is sooooo rare (that I've never actually done it myself in 
15 years except to MOVE an index from one physical location to another -- and even then, 
we don't need to do that very much today at all with logical volumes and their like)

Don't know how to say it better -- the index wants to be big and fat with extra space.  It is on a 
column you update -- moving the index entry from place to place in the index.  One day the row has 
a code of "A", the next day the code is "G", then "Z" then "H" and so on.  So the index entry for 
the row moves from place to place in the index.  As it does so, it needs space -- will, if the 
space isn't there, we split the block into two -- and make space.  Now the index is getting fat.  
Over time the index is 2-3x the size it was when you started and is "half or more empty"  But that 
is OK since you move rows around.  Now when we move the rows around, we no longer have to split 
blocks to make room -- the room is already available.

Then you come along and rebuild or drop and recreate the index (which have the same effects -- just 
the rebuild is "safer" -- doesn't stand a chance of losing the index and can be faster as the index 
can be rebuilt by scanning the existing index instead of scanning the table and sorting and 
building a fresh index).  Now, all of that nice space is gone.  We start the process of splitting 
the blocks all over again -- getting us right back to where we started.  

You saved no space.
The index is right back the way it was.
You would just be wasting your time to rebuild it again causing this vicious cycle to repeat 
itself.