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.