Author: Boris Milrud

Indexes:

Indexes 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.

Oracle - Balanced Trees:

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 Index - Performance Degradation:

Oracle indexes are not self-balancing. They become fragmented after a large number of INSERTs and DELETEs, which may lead to significant performance degradation.

Detecting Index Fragmentation:

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.

Oracle Index - PCTUSED Parameter:

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.

Consequences of Index Fragmentation:

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.

Oracle Index - Splitting:

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.

Oracle Index - Spawning:

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.

Oracle Index - USER_INDEXES View:

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

Index Height:

An index with a height greater than 3 is a good candidate for rebuilding. Generally, tables with height 4 and higher may lead to unnecessary index block reads, and therefore could benefit from rebuilding.

Deleted Leaf Rows:

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.

Dropping and Recreating Index:

Prior to Oracle version 7.3, the only way to rebuild an index was to drop the index and recreate it completely.

Oracle Index - alter index rebuild Command:

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. The syntax of this command is

alter index CUSTOMER_LASTNAME_IND rebuild;

New and Old Indexes:

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;

Oracle Index - Global 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';

Oracle Index - Local Indexes:

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.

alter table Customer

modify partition Customer_K

rebuild unusable local indexes;

Conclusion:

It is very crucial to keep indexes in good shape and to detect fragmented indexes and fix them proactively.

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   DBA Tips for Killing Oracle Idle Session!!
   DBA Tips for Speeding Up Large Table Updates!!
   A Guideline for Oracle Instantiation with RMAN!
   The Power of 11g Snapshot Standby Database for Oracle 10g!!