Friday, June 17, 2011

Obtaining Information about Index in Oracle

General details about the index can also be found by:

Analyze index  compute statistics;
Select * from user_indexes
    where index_name= ‘’;
To obtain further detail about an index:
Analyze index  validate structure;
The command:
Validate index ;
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 ;
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 validate structure cascade;

No comments:

Post a Comment