Index misunderstanding in oracle Learning

Index misunderstanding in oracle Learning

We all know that indexes can increase the speed to eliminate sorting, but they are often abused in actual use, which not only wastes resources, but also plays a negative role. This error is often invisible to everyone, leading to production system faults. Common indexes: common indexes are easy to use, but there are still some minor mistakes.

1. Problem: count (1) avg () sum () min () max () the query takes the index only when the index is not empty.

Solution: if an index has a null value during count (), it cannot be solved. When avg () sum () min () max () is processed, if the index is not null, the result is the same, however, when the index is not null, the index scanning speed is greatly improved.

2. At the same time, min () and max () query takes the index only when the index is not empty.

Solution: add the index is not null, and the max () min () is more efficient for separate queries.

3. Problem: table access by index rowid is generated when a non-index column is returned)

Solution: if you do not need to query non-index columns, avoid table access by index rowid.

4. Question: Can indexes increase the speed? Is the more indexes, the better?

Solution: the more indexes, the better, the more indexes should be created. Otherwise, the efficiency of insertion, update, and deletion is very low. If a column has a common index and a composite index, consider whether to drop a common index if the first column of the composite index is a common index.

In addition, you can consider disabling the index, and then wait until the operation is completed to make the index take effect. Of course, the operation of the production database should be performed whenever possible when there are few businesses to avoid occupying resources and putting pressure on the system.