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

Analyze the usage of the oracle index space 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
Oracle index problems, deleting and re-indexing and Index Analysis

1. It should be feasible. Will it save time and try it.

2. About 400 thousands or 500 thousands of data is stored every month, and only the data of the last four months is saved.

It takes about three or four hours to create these seven indexes each time;

It takes too long to rebuild the index for 2 million of the data. It is strange.

3. It is estimated that the index will be dropped first, and then the create index will avoid the impact of the index on the insertion efficiency when data is inserted.

Analyze indexes and recreate primary key indexes

Validate structure should be used to fill in the index_stats view, while compute statistics fills in user_indexes (this view is later recommended to be replaced by user_ind_statistics in oracle). The former focuses on structure statistics, the latter generally counts the status of the entire index (the difference can be seen from different fields in the two views ).

There is no difference between a primary key index and other indexes, except that a unique constraint and a unique index are added to the column. Therefore, you only need to analyze the index like other column indexes.