Oracle Tool Class SQL Analysis index fragmentation rate

Parsing statements:
Sql>analyze index <index_name> validate structure online;

Analysis Table:
Analyze table TableName compute statistics;

Analyze index IndexName compute statistics;

To view the index fragmentation situation:
Sql>select Name,del_lf_rows_len,lf_rows_len, (Del_lf_rows_len/lf_rows_len) *100 from Index_stats;
Index fragmentation rate: (Del_lf_rows_len/lf_rows_len) *100
If the index fragmentation rate exceeds 20%,oracle, the index fragmentation is considered to be very serious and the index fragmentation needs to be collated.

2. Index defragmentation
Index defragmentation consists of two strategies:
(1) Rebuild Index (rebuild)
Sql>alter index <index_name> rebuild;
(2) compression index (COALESCE)
Sql>alter index <index_name> coalesce;

Oracle recommends a strategy for rebuilding indexes (rebuild) after periodic analysis.

Oracle Tool Class SQL Analysis index fragmentation rate