Detecting and Fixing Out-of-Balance Indexes

Detecting and Fixing Out-of-Balance Indexes

ndexes are Oracle database objects that provide a fast, efficient method of retrieving data from database tables. The physical addresses of required rows can be retrieved from indexes much more efficiently than by reading the entire table. Effective indexing usually results in significant improvements to SQL performance too.

Oracle’s default index structure is B*-tree, which stands for “Balanced tree.” It has a hierarchical tree structure. At the top is the header. This block contains pointers to the appropriate branch block for any given range of key values. The branch block points either to another branch block, if the index is big, or to an appropriate leaf block. Finally, the leaf block contains a list of key values and physical addresses (ROWIDs) of rows in the database.

Theoretically, any row in a table, even a big one, could be retrieved in a maximum of three or four I/Os (input/output operations): one header block, one or two branch block(s), and one leaf block.

Oracle indexes are not self-balancing. They become fragmented after a large number of INSERTs and DELETEs, which may lead to significant performance degradation. In this 10-Minute Solution I show you how to detect these “out-of-shape” indexes and cure them.

How do you prevent Oracle indexes from becoming fragmented?

Use a PL/SQL stored procedure to detect out-of-balance indexes and rebuild them by utilizing index statistics from the INDEX_STATS view. Run it periodically to keep indexes in your database in good shape.

How Indexes Become Fragmented
The advantages of indexing do not come without a cost. As database objects, indexes are created for tables only and they must be in sync with them: indexes must be updated by the database with every data manipulation language (DML) operation?INSERT, DELETE, or UPDATE. When a row is deleted from a table, the Oracle database will not reuse the corresponding index space until you rebuild the index. That’s the way indexes are implemented in Oracle. The PCTUSED parameter for indexes is always 0, which means that?unlike table blocks?index blocks will not be put on the free list for reuse.

Therefore, indexes are always growing and can become very fragmented over time, especially in tables with dynamic data. As indexes grow, two things happen: splitting and spawning.

Splitting happens when an index node becomes full with keys and a new index node is created at the same level as a full node. This widens the B*-tree horizontally and may degrade performance.

Spawning is the process of adding a new level to an index. As a new index is populated, it begins life as a single-level index. As keys are added, a spawning takes place and the first-level node reconfigures itself to have pointers to lower-level nodes.

Extracting Index Internal Statistics
The USER_INDEXES view contains statistical information that is placed there whenever the ANALYZE INDEX command is issued.

Let’s try an example?the index CUSTOMER_LASTNAME_IND:

analyze index CUSTOMER_LASTNAME_IND compute statistics;
Unfortunately, the USER_INDEXES view is designed to provide information to the Oracle Cost-Based Optimizer (CBO) for SQL execution path determination. It does not keep statistics on the internal status of Oracle indexes. This can be accomplished by using the ANALYZE INDEX command with a VALIDATE STRUCTURE clause. It doesn’t change the statistics in the USER_INDEXES view; therefore it could be executed safely without affecting the CBO.

Here is the syntax:

analyze index CUSTOMER_LASTNAME_IND validate structure;
It populates the SYS.INDEX_STATS view only. The SYS.INDEX_STATS view can be accessed with the public synonym INDEX_STATS. The INDEX_STATS view will hold information for one index at a time: it will never contain more than one row. Therefore you need to query this view before you analyze next index.

Let’s look at the data inside INDEX_STATS:

select name as IndexName,       height,       lf_rows,       del_lf_rows   from index_stats;
Here are the results of that query:
INDEXNAME                     HEIGHT    LF_ROWS     DEL_LF_ROWS————————- ———- ———- —————CUSTOMER_LASTNAME_IND              2       5237            2130
The following INDEX_STATS columns are especially useful:
  • height refers to the maximum number of levels encountered within the index.

  • lf_rows refers to the total number of leafs nodes in the index.

  • del_lf_rows refers to the number of leaf rows that have been marked deleted as a result of table DELETEs.
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,         height,         lf_rows,         del_lf_rows   from index_stats;
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.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:

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


About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist