How to check index fragmentation and re-create an index in the Oracle database

When the index fragmentation is too large, the query execution speed will be affected, thus affecting our work efficiency. At this time, the most favorable measure is to re-build the index. This article mainly introducesOracle DatabaseMediumCheck Index fragmentationAndRe-IndexingNext we will introduce this process.

The procedure for re-indexing is as follows:

1. Confirm Basic Information

Log on to the database and find the tablespace that stores the index. In this tablespace, all the index owner is tax. the index is stored in an independent tablespace, which is separated from the tablespace of a data table. It is a common database design method.

2. Find which indexes need to be rebuilt

You can use the anlyze index... validate structure command to analyze a single specified index and store the analysis results of a single index to index_stats. The general judgment is based on:

 
 

  
  
height >4  
  
  
 
  
  
pct_used < 50%  
  
  
 
  
  
del_lf_rows / lf_rows +0.001 > 0.03 
 
 

3. googleAll index traversal scripts are downloaded.

Anlyze index found .... validate structure can only fill in the analysis information of a single index, So google runs a Loop script on the Internet to traverse all the index names in the index space, in addition, you can store the analysis information of all indexes in a user table created by yourself.

4. anlyze index lock index

It is found that the downloaded script is not easy to use. We should strive for an exclusive lock for the anlyze index before analyzing the index and lock the index. Obviously, some indexes are being used by the application system, so running anlyze fails. The lesson learned here is to do this at night as much as possible. However, I prefer to go home on time, so I add Exception Handler to the statement and throw the index names that failed anlyze index execution to make the script run properly. Manually perform the index Analysis Based on the index name printed to the foreground.

5. Summary

Although 522 of the 160 indexes match the above judgment. However, the index is not large, and those indexes with millions of leaf do not meet the above judgment conditions. Therefore, the conclusion is that index rebuild online is not required.

6. When can I rebuild the index?

Rebuild index online is helpful for large indexes with a large number of DML operations. You can build a rebuild for a large index every quarter. Usually, even rebuild index online will cause I/O contention, so it is of little significance to have it online. You can put it in 3-5 nights and execute rebuild index in batches to lock the index, when no user is allowed to wait) and the paralle 8 keyword is added, the database server should have eight cpu processors.

This article describes how to check index fragmentation and re-Indexing in the Oracle database. We hope this will help you.