How Oracle Databases Rebuild indexes

When the index is too fragmented, it affects the speed at which the query is executed, which affects our productivity. The most advantageous measure at this time is to rebuild the index. This article mainly introduces the process of examining index fragments and rebuilding indexes in an Oracle database, and then we begin to introduce the process.

The steps for rebuilding an index are as follows:

1. Confirm Basic information

Log in to the database, locate the tablespace that specializes in index, and the owner of all index under this tablespace is tax. The index is stored exclusively in an independent tablespace, separated from the tablespace of the datasheet, is a common method of database design.

2. Find which index needs to be rebuilt

The Anlyze index ... validate structure command can parse a single specified index and store the results of a single index analysis in index_stats attempt. The basis of general judgment is:

Height >4 pct_used < 50% del_lf_rows/lf_rows +0.001 > 0.03 g)

3. Google downloads up and down all index scripts

found that Anlyze index ... validate structure can only populate a single index analysis, so Google went down a loop script from the Web, traversing all the indexed names in the index space, And you can put all the analysis information of the index into a user table that you set up.

4. Anlyze Index Lock Index

found that the downloaded script is not good to use, should be Anlyze index in the analysis before trying to win exclusive lock, lock index, it is obvious that some index is being used by the application system, so run Anlyze failed. The lesson here is to do it as much as possible at night. But I prefer to go home on time, so add exception Handler in the statement, throw the index name that Anlyze index failed, so that the script runs out of order. and manually perform those index analyses based on the index name that is printed to the foreground.

5. Summary

Although it was found that 160 of the 522 index matches the basis of the above judgment. But the index is small, and those with the million leaf index does not match the above criteria, so the conclusion is no index rebuild online. No fragments.

6. When can rebuild index?

The Rebuild index online is useful for large indexes that have a large number of DML operations. You can do a rebuild for a larger index every month of the quarter. Usually even if rebuild index online will also cause I/O contention, so there is no online significance, can be put to 3-5 nights, batch execution rebuild index, lock index, do not let users (no user, etc.), Plus the Paralle 8 keyword, the discovery database server should have 8 CPU processors.

The knowledge about the Oracle database checking index fragmentation and rebuilding the index is here, I hope to be helpful to you.