Analyze the oracle index space usage and whether the index needs to be rebuilt

Analyze the oracle index space usage and whether the index needs to be rebuilt
Analyze the index space usage and whether the index needs to be rebuilt

The analyze any permission is required to analyze indexes of other users.
Check the table size and index size before analyzing the index. If the index size is the same as the table size or greater than the table size, you can determine that the index may be faulty. You need to analyze the 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 blocks reached 7939

2. query the proportion of rows to be deleted from an index. (If the deletion rate reaches 30%, re-create an 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 ratio
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 the index usage and index depth (if the depth is greater than or equal to 4, re-build the index)
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
-- The index depth is 4.
Consider re-indexing if the following conditions are met
The most common justifications given for rebuilding an index are:
-Index becomes fragmented
-Index grows and grows-deleted space is not re-used
-Index clustering factor becomes out of sync