RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


Detecting and Fixing Out-of-Balance Indexes-4 : Page 4

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
      from user_indexes
      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
      from user_ind_partitions
      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.

Amazing Results

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:

   RebuildUnbalancedIndexes(3, 10);
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.

Boris Milrud has 10 years of experience as a software developer. He is a Senior Database Engineer with Callidus Software, Inc. located in San Jose CA. He is specializing in all aspects of Oracle database software development including database design, programming, optimization and tuning.
Email AuthorEmail Author
Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date