Oracle's collation of whether indexes need to be rebuilt on a regular basis

Do indexes in an Oracle database need to be rebuilt on a regular basis? If you do not need to rebuild regularly, what is the reason? If you need to rebuild regularly, what are the reasons? Also, if you need to rebuild regularly, the indexes that meet those criteria need to be rebuilt. On this issue, there are a lot of controversy on the internet, but also always let me a little confused, because always a bit not the feeling of the truth, until last week to see some information, so organized in this, convenient later to read:

First look at the reasons or criteria on which the index needs to be rebuilt on the Web:

One: Analysis (analyze) after the specified index, query the value of the height field of index_stats, if this value >=4, it is best to rebuild (rebuild) this index. Although this rule is not always correct, the index does not need to be rebuilt if the value is always constant.

Second: After the analysis (analyze) specifies the index, query the value of Index_stats del_lf_rows and Lf_rows, if (del_lf_rows/lf_rows) *100 > = 20, then the index needs to be rebuilt.

On this argument, we find the official information that can be verified for Various aspects of fragmentation (document ID 186826.1) , which has such a

Along with the REBUILD clause of the ALTER INDEX, Oracle8i introduces the

COALESCE clause as another to address fragmentation issues.

In the following cases, it is worthwhile to rebuild the index:

--the percentage of the space used is bad-lower than 66%: pct_used

--deleted leaf blocks represent more than 20% of total leaf blocks:del_lf_rows

-the height of the tree is bigger than 3:height or Blevel

There are also some reasons or arguments for rebuilding the index, as follows: but this data is very old, Oracle 8i, Oracle 9i ERA of data, of course, the index depth of more than 4 levels and deleted index entries occupy at least 20% of the total number of existing index entries, The idea of rebuilding the index in these 2 scenarios is clearly not outdated, but the idea of rebuilding the index on pct_used below 66% is a bit inappropriate for the current mainstream version.

1. Oracle's B-tree index becomes unbalanced over time (false awareness)

2. Index fragmentation is increasing

3, the index is increasing, the deleted space is not reused (wrong understanding)

4, index clustering factor (cluster factor) is out of sync, can be repaired by reconstruction (wrong understanding)

In fact, Oracle official documents have a discussion about the necessity and impact of index reconstruction, and the official document is the necessity and impact of index reconstruction (document ID 1525787.1)

--------------------------------------------------------------------------------------------------------------- ------

Suitable for :

Oracle database-enterprise Edition-Version 8.1.7.0 and later

The information contained in this document applies to all platforms

Use

This article focuses on the various effects of rebuilding an index. We usually rebuild the index on a regular basis, but in fact, judging whether index rebuilding is useful is generally not based on statistics and rarely preserves the rebuild history of the index.

Scope of application

The target audience for this article is the database administrator.

More information

There are many discussions about the usefulness of rebuilding indexes. In general, it is very rare to rebuild a B-tree index, basically because B-tree indexes can be largely self-managing or self-balancing.

The most common reasons to think of rebuilding an index are:

-Index fragmentation is constantly increasing

-The index is increasing and the deleted space is not reused

-Index clustering factor (cluster factor) is not synchronized

In fact, most indexes can be balanced and complete, because idle leaf entries can be reused. Insert/Update and DELETE operations do cause fragmentation of the free space around the index block, but in general these fragments are reused correctly.

The clustering factor cluster factor can reflect the sorting of data in the table corresponding to the given index key value. Rebuilding an index does not affect the cluster factor, and changing the cluster factor can only be done by reorganizing the table's data.

In addition, the impact of rebuilding the index is obvious, please read the following instructions carefully:

1. Most scripts rely on index_stats dynamic tables. This table is populated with the following commands:

Analyze index ... validate structure;

Although this is a valid index-checking method, it obtains exclusive table locks when it parses the index. Especially for large indexes, the impact of this is enormous, because DML operations on tables are not allowed during this period. Although this method can be run online without locking the table, it may take extra time.

2. The immediate result of rebuilding the index is that REDO activities may increase and overall system performance may be affected.

The Insert/update/delete operation causes the index to evolve as the index splits and grows. After rebuilding the index, it will connect more tightly, but as you continue to perform DML operations on the table, you must split the index again until the index is balanced. As a result, the redo activity increases, and the index segmentation is more likely to have a direct impact on performance because we need to use more I/O, CPU, and so on for index rebuilds. After a period of time, the index may encounter a "problem" again, and therefore may be marked as rebuilt, thus falling into a vicious circle. Therefore, it is usually better to have the index in a natural balance and/or at least prevent the index from being rebuilt periodically.

3. The index coalesce (index merge) is usually preferred, rather than rebuilding the indexes. Index merging has the following advantages:

-No need to occupy nearly twice times the space of disk storage space

-Can be operated online

-instead of rebuilding the index structure, merge the index leaf blocks as quickly as possible to avoid excessive overhead, see explanation in 2nd.

Note: For example, to transfer an index to another tablespace, you need to rebuild the index.

In summary, it is strongly recommended that you do not rebuild indexes on a regular basis, but should use the appropriate diagnostic tools. See the following article, which lists the scripts that you can use to analyze the index structure. It does not use the "Analyze Index validate Structure" command, but estimates the index size based on the current table and index statistics.

Note 989186.1-Script to investigate a B-TREE index structure

Read the official document above, presumably you have an understanding of whether the index needs to be rebuilt regularly, but do you always feel that this is a conclusive statement, always feel that it is not deep enough, lack of arguments to support it, Well, here's the guy. This document (Index-internals-rebuilding-the-truth) on this aspect of the analysis, introduction, is definitely I have seen the most detailed, the most in-depth introduction of the internal knowledge of the index. If the link cannot be viewed, download index-internals-rebuilding-the-truth.pdf from the attachment

Resources:

Https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=529590097581429&id=1525787.1&_ afrwindowmode=0&_adf.ctrl-state=mztcpsxax_149
Https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=529957019657481&id=1525787.1&_ afrwindowmode=0&_adf.ctrl-state=mztcpsxax_198
Http://www.it-matters.be/doc/index-internals-rebuilding-the-truth.pdf
https://support.oracle.com/epmos/faces/documentdisplay?_afrloop=531414583432488&id=186826.1& displayindex=1&_afrwindowmode=0&_adf.ctrl-state=mztcpsxax_247

Oracle's collation of whether indexes need to be rebuilt on a regular basis