As Oracle indexes are used, they adjust. For this reason, it is important to monitor index activity. Let us examine the two ways in which indexes change, and how this can cause index imbalance.
Index Splitting and Spawning
The first sign of index change is splitting and spawning. As table rows are updated, new index levels may be created.
Let us look at an example.
1) When an index node splits at the same level to accept new rows splitting occurs.
2) As a level becomes full, part of the tree may spawn, creating a new index level.
Splitting an index
Creation of deleted Leaf Nodes
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.
DBA_INDEXES
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):
Using the 'analyze index' command
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
validate structure;
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 Oracle index
To find values that identify an out-of-balance index, we use the analyze_index command. We will use the index name Gould.PRODUCT_IDX.
Enter the command to populate the index.
This analyzes the index structure and returns the message Index analyzed. Now select for height, del_lf_rows and blks_gets_per_access from index_stats, and hit Enter.
You will see that the results of this query efficiently list values for the height, the number of deleted leaf rows, and the number of blocks per access; these values indicate how efficiently your index uses space.
CREATE INDEX:
CREATE INDEX stores the timestamp of the most recent function used in the function-based index. This timestamp is updated when the index is validated. When performing tablespace point-in-time recovery of a function-based index, if the timestamp on the most recent function used in the index is newer than the timestamp stored in the index, then the index is marked invalid. You must use the
ANALYZE INDEX...VALIDATE STRUCTURE
statement to validate this index.
Monitoring Space Use of Indexes
If key values in an index are inserted, updated, and deleted frequently, the index can lose its acquired space efficiently over time. Monitor index efficiency of space usage at regular intervals by first analyzing the index structure, using the
ANALYZE INDEX...VALIDATE STRUCTURE
statement, and then querying the INDEX_STATS view:
SELECT PCT_USED FROM INDEX_STATS WHERE NAME = 'index';
The percentage of index space usage varies according to how often index keys are inserted, updated, or deleted. Develop a history of average efficiency of space usage for an index by performing the following sequence of operations several times:
Analyzing statistics
Validating the index
Checking PCT_USED
Dropping and rebuilding (or coalescing) the index
When you find that index space usage drops below its average, you can condense the index space by dropping the index and rebuilding it, or coalescing it.
Rebuilding Oracle indexes
Analyzing index performance
SQL> analyze index burleson.PRODUCT_IDX validate structure;
Index analyzed.
SQL> select height, del_lf_rows, blks_gets_per_access
from index_stats;
HEIGHT DEL_LF_ROWS BLKS_GETS_PER_ACCESS
------ ----------- --------------------
3 0 3
This shows the results of the analyze index query.
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.
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.
