Cry How To...

Obtain information about an index

General details about the index can also be found by:

Analyze index <index-name> compute statistics;

Select * from user_indexes
    where index_name= ‘<index-name>’;

To obtain further detail about an index:

Analyze index <index-name> validate structure;

The command:

Validate index <index-name>;

Performs the same function.

This places detailed information about the index in the table INDEX_STATS. This table can only contain one row, describing only the one index. This SQL also verifies the integrity of each data block in the index and checks for block corruption.

For example, to get the size of an index:

validate index <index_name>;

select name "INDEX NAME", blocks * 8192 "BYTES ALLOCATED",
    btree_space "BYTES USED",
    (btree_space / (blocks * 8192))*100 "PERCENT USED"
    from index_stats;

This assumes a block size of 8K (i.e. 8192 bytes). It shows the number of bytes allocated to the index and the number of bytes actually used.

Note that it does not confirm that each row in the table has an index entry or that each index entry points to a row in the table. To check this:

Analyze table <table-name> validate structure cascade;

This page represents one page of many pages of notes I made whilst working with Oracle 7 through to 10i. I now no longer work with Oracle databases, but hope that these notes will continue to be of benefit to others.