Determining if the Index Is Out-of-Balance and Needs Rebuilding
The first rule is that an index with a height greater than 3 is a good candidate for rebuilding. In my experience I had a tables with 510 million. rows and the height still was equal to 3. Of course, there are exceptions but, generally, tables with height 4 and higher may lead to unnecessary index block reads, and therefore could benefit from rebuilding.
The second rule is that the deleted leaf rows amount (del_lf_rows) should be less than 20 percent of total number of leaf rows (lf_rows). A high number of deleted leaf rows shows that the table has been subjected to heavy DELETE activity. As a result, the index tree becomes unbalanced and the index is a good candidate for rebuilding.
In this example, del_lf_rows equals 2130, lf_rows equals 5237; the ratio is:
(2130*100) / 5237 = 40.7 percent
This index is simply "begging" for rebuilding!
Rebuilding an Index
Prior to Oracle version 7.3, the only way to rebuild an index was to drop the index and recreate it completely. As of Oracle 7.3, you can use the ALTER INDEX REBUILD command to use the existing index as a data source for a new index instead of using that table as a data source, thus improving index creation performance.
Here is the syntax of this command:
alter index CUSTOMER_LASTNAME_IND rebuild;
While the new index is being built, it will exist simultaneously with the old index in the database. Therefore, there must be enough space available to store both the old index and the new index in the database in order to use this command. When the index gets rebuilt, the new index becomes available and the old index gets dropped. Its space is reclaimed by the database.
There are some options available with this command. I usually use the following syntax:
alter index CUSTOMER_LASTNAME_IND rebuild
parallel nologging compute statistics tablespace IndexData;
The PARALLEL clause causes Oracle to select a degree of parallelism equal to the number of CPUs available on all participating instances multiplied by the value of the PARALLEL_THREADS_PER_CPU initialization parameter. The default value of PARALLEL_THREADS_PER_CPU is 2 for Oracle 8i. So, if your database server is a four-CPU box, your index will be rebuilt by eight parallel processes, which would lead to a performance gain.
The NOLOGGING clause allows you to rebuild the index faster because operations will not be logged in the redo log file.
The COMPUTE STATISTICS clause enables you to collect statistics at relatively little cost during the rebuilding of an index.
The TABLESPACE clause specifies the tablespace where the rebuilt index, index partition, or index subpartition will be stored. The default is the default tablespace where the index or partition resided before you rebuilt it. By specifying a different tablespace you can easily move the index to another location.
Let's look at the index statistics again after the CUSTOMER_LASTNAME_IND index has been rebuilt:
analyze index CUSTOMER_LASTNAME_IND validate structure;
select name as IndexName,
Here are the results of the last query:
INDEXNAME HEIGHT LF_ROWS DEL_LF_ROWS
------------------------- ---------- ---------- ---------------
CUSTOMER_LASTNAME_IND 2 3107 0
The results show that the total number of leaves went from 5,237 to 3,107, which is a difference of 2,130 dropped leaf rows.