How to create an efficient index

Index Creation Guide :

1. Frequent occurrences in the WHERE clause

2. Frequent in the Join association field

3. Select a key with high selectivity

4. Do not use the B-tree index on keys that have very few different values. Such keys or expressions often have poor selectivity, so there is no more optimizations for performance,

5, unless the value that is often selected is lower than the frequency of other values. If this is the case, the bitmap index can be used effectively, but if the bitmap index is frequently altered in a highly concurrent OLAP system, do not use a bitmap index.

6. Do not create an index on a field that is often modified.

7. Do not Jianjian the index of a character in a function or expression that appears only in the WHERE clause, or build a function index

8. You can consider indexing on foreign keys .

9. When you create an index, evaluate whether the performance optimizations that the index brings to the query are more worthwhile than the performance degradation caused by insert,update,delete operations and the space occupied by the index.

10. Composite Index

Increased selectivity (sometimes the composition selectivity is higher than the selectivity of a single column)

Reduce I/O (if the lock is queried by the selected columns in the composite index, Oracle can return the value by accessing the index without needing to access the table.) ）

Often appear in the WHERE clause by combining with and, and especially after the combination is more selective than a single key, the combination can consider common composite indexes

If the columns of the query are a subset of the combined columns, consider building a composite index based on these keys

The order of the indexes:

The index enables the WHERE clause to form the leading column of the index

If there are several keys in the WHERE clause that are very high-frequency, you can just build a composite index on these few column keys

This column can be used as the first key of a composite index if all of the listed current where is the same frequency and the data is physically sorted based on one of the columns.

Using non-unique indexes to achieve uniqueness:
You can enforce uniqueness with a non-unique index on a unique constraint field or PRIMARY KEY constraint field that already exists on the table. The advantage of this is that even if the constraints are disable,

The index is still valid. Therefore, a disabled unique or primary key can be re-enable without the need to rebuild the uniqueness index. This allows the enable of the Big data table

Save a lot of time during the operation.

This article is from the "Database Siege Lion" blog, please make sure to keep this source http://liu16.blog.51cto.com/6494747/1690557

How to create an efficient index