Re: When should one rebuild an index?

> If someone explain with examples or provide URLs having examples, i
> would be very much thankful.
> Please help on this by detailed explanation.

Raja, Oracle indexes usually do not need to be rebuilt. If you have many empty blocks, you may want to try coalescing the index, rather then rebuilding it. Jonathan Lewis has a script utilizing an undocumented function sys_op_lbid which measures the distribution of the rows throughout the leaves blocks of an index. The script is not generic and there is some editing required. You should consider coalescing the index if you have a significant percentage of empty or near-empty blocks. However, this should be done only for exceptionally large indexes. Having a range scan in an index with level 3 read 2 more blocks on average is not a valid reason for rebuilding an index. On the other hand, if you have a full index scan which goes through all the leaves, and empty index blocks comprise 20% of the index, then your full scan will have 20% more work to do.

Can you please let me know what motivated you to start inquiring about rebuilding indexes? Rebuilding is a solution in a very few cases and is usually done on the recommendation of the dinosaurs who are used to such things as "defragmenting" and "rebuilding indexes". On the other hand, "rebulding indexes" has recently become a favorite cause for some people to start flame wars.

Also, can you please tell us the version of your database. There are some bugs in 8i (Note: 75216.1) and in 9i (mainly with coalescing function-based indexes).

-- 
Mladen Gogala
http://mgogala.freehostia.com