A PL/SQL Procedure to Detect and Rebuild Out-of-Balance Indexes
The procedure I wrote (called RebuildUnbalancedIndexes, see Listing 1
) takes care of both global and local indexes. Global index information is extracted into the csrGlobalIndexes cursor from the USER_INDEXES view WHERE value in Partitioned column is 'NO':
cursor csrGlobalIndexes is
select index_name, tablespace_name
where partitioned = 'NO';
Local indexes are created for partitioned tables and are available starting with Oracle8. They are equi_partitioned with the table they are based on. That means that for each index entry residing in partition CUSTOMER_K, there is a row of the table CUSTOMER that also resides in that same partition CUSTOMER_K.
Local index information is extracted into the csrLocalIndexes cursor from the USER_IND_PARTITIONS view WHERE value in Status column is 'USABLE':
cursor csrLocalIndexes is
select index_name, partition_name, tablespace_name
where status = 'USABLE';
If local indexes are UNUSABLE as a result of TRUNCATE or MOVE partition operations, they need to be rebuilt. Even though it's not a topic of this 10-Minute Solution, and it's not part of our procedure, here is the syntax for that case, for your reference:
alter table Customer
modify partition Customer_K
rebuild unusable local indexes;
For each index (global or local), the procedure generates statistics using the ANALYZE INDEX command with the VALIDATE STRUCTURE clause; extracts it from the INDEX_STATS view; checks for the index height and number of deleted leaf rows; and decides whether to rebuild the index or not.
|When one index got corrupted, it slowed execution time of a procedure down from 14 seconds to more than 4 hours! Only rebuilding it made it finally run normally. Read on... |
I defined both threshold values of maximum height and percentage of deleted leaf rows as parameters, so you could pass your own values. In my experience, I've found that performance could degrade substantially if the percentage of deleted leaf rows exceeds 810 percent (see the sidebar, "Amazing Results"). Most authors recommend 20 percent.
I usually call the RebuildUnbalancedIndexes stored procedure (see Listing 1) and use 10 as the parameter for pMaxLeafsDeleted:
After you run RebuildUnbalancedIndexes, your indexes will consume a minimum amount of space and their trees will be well-balanced. They are back in shape and ready to serve your SQL queries efficiently.
I think the "Amazing Results" story should convince you how crucial it is to keep indexes in good shape and, therefore, how important it is to detect out-of-balance indexes and fix them proactively.