Analyze index space usage. and whether the index needs to be rebuilt
Analysis of the index under other users need to analyze any permissions
Look at the size of the table and the size of the index before analyzing the index, assuming that the index size is as large as the table size or larger than the table size, then you can infer that the index may be problematic. Need to analyze Index
Query Object Size:
Select owner,segment_name,segment_type,bytes/1024/1024 from Dba_segments ORDER BY 4 desc
1. Analysis Index
Sql> Analyze Index AA. INDEX_AA Validate structure;
Sql>
Index analyzed
Query space usage:
Sql> select name, blocks, Lf_blks, Br_blks, blocks-(lf_blks+br_blks) empty from Index_stats;
NAME BLOCKS lf_blks br_blks EMPTY
------------------------------ ---------- ---------- ---------- ----------
AA 262144 253480 725 7939
--Index empty block reached 7939
2. Query index Delete row and delete scale (general deletion rate of 30% will consider rebuilding the index)
Sql> Select T.name,--index name
2 t.lf_rows,--number of leaf rows (values in the index)
3 T.lf_blks,
4 T.del_lf_rows,--number of deleted leaf rows in the index
5 (T.del_lf_rows/t.lf_rows) *100 ratio--delete scale
6 from Index_stats t
7 where t.name= ' Index_aa ';
NAME lf_rows lf_blks del_lf_rows RATIO
------------------------------ ---------- ---------- ----------- ----------
AA 77318533 253480 0 0
3. View index usage and index depth (consider rebuilding indexes when deep >=4)
sql> SELECT height, NAME, Btree_space, Used_space, pct_used from Index_stats;
HEIGHT NAME btree_space used_space pct_used
---------- ------------------------------ ----------- ---------- ----------
4 Index_aa 2032646380 1231201944 61
--Index depth of 4
The following conditions are met to consider rebuilding the index
The most common justifications given for rebuilding an index is:
-Index becomes fragmented
-Index grows and grows-deleted space is not re-used
-index clustering factor becomes out of sync
Analyze Oracle Index space usage and whether indexes need to be rebuilt
