The second sign of index change is when, as table rows are deleted, index nodes become logically deleted.
To save processing time when a series of rows are deleted, the Oracle indexes will leave the dead index tree nodes in the index and flag them as deleted.
These are called deleted leaf nodes.
Maintaining an index
With frequent inserts, updates, and deletions, indexes that have new levels or a number of deleted leaf nodes may or may not use their acquired space efficiently over time. Because these indexes require additional I/O to navigate, it is sometimes necessary to rebuild them. The goal of this lesson is to provide methods to identify and resolve these out-of-balance indexes. Although in theory, you could also drop and re-create, the solution is to rebuild.
Guidelines for rebuilding an Index
Oracle provides several index statistics
for analysis. An Oracle index may need to be rebuilt when:
The index has spawned to more than 3 levels.
There are a large number of deleted leaf nodes.
The block GETS_PER_ACCESS value is greater than 5.
Oracle Index Statistics
Oracle lists the following index statistics in the DBA_INDEXES, USER_INDEXES, and ALL_INDEXES data dictionary views:
Exact depth of the index from root block to leaf blocks
Number of leaf blocks
Clustering factor (the order of the rows in relation to indexed values)
Number of distinct index values
Average number of leaf blocks per index value
Average number of data blocks per index value (for an index on a table)
Querying Index Information
Several data dictionary views are available to query information about indexes. This section covers certain views and their columns that you should be familiar with before taking the exam.
The DBA_INDEXES, USER_INDEXES, and ALL_INDEXES views are the primary views used to query for information about indexes (IND is a synonym for USER_INDEXES). The views have the following information (the columns that can be used in the query are provided in parentheses):
The way to identify these values is to first analyze the index structure and then query the INDEX_STATS view.
To analyze indexes, Oracle provides the following command:
analyze index demo
In the example above, this command populates a table called INDEX_STATS with the validation information. Let us try one. It is good SQL practice to be case-consistent. Analyzing Orracle Index
Analyzing index performance
The results are shown above. Here we see that the index has only three levels, no deleted leaf rows, and three block gets per access.
Our index is fine, and does not require re-building. Use the analyze index command and query the INDEX_STATS view regularly to monitor how efficiently your index uses space.
The index_stats table is re-populated after each command, so you must view it each time you run an index analyze.
Rebuilding Oracle indexes
Once we identify indexes for re-building, it is easy to re-build the index in-place: Alter index index_name tablespace tablespace_name rebuild;
This command is completely safe because it rebuilds the index with temporary segments in the target tablespace, and only drops the index if the new tree has been successfully created.
Always use the tablespace parameter when rebuilding indexes. If you do not specify the tablespace name, Oracle will re-build the index in your default tablespace. The next lesson looks at how the high water mark for a table affects the performance of a full-table scan.