Shulou(Shulou.com)06/01 Report--

Index creation guidelines:

1. It appears frequently in where clauses

2. Frequently in the associated fields of join

3. Select the bond with high selectivity

4. Don't use B-tree indexes on keys that have very few different values. Such keys or expressions are often less selective, so there are no more performance optimizations.

5. Unless frequently selected values appear less frequently than other values. In this case, bitmap indexes can be used effectively, but if bitmap indexes are changed frequently in highly concurrent OLAP systems, do not use bitmap indexes.

6. Do not index fields that are often modified.

7. Do not index fields in functions or expressions that only appear in where clauses, or build functional indexes

8. you can consider building an index on the foreign key.

9. When creating an index, evaluate whether the performance optimization that the index brings to the query is more worthwhile than the performance degradation of the INSERT,UPDATE,DELETE operation and the space occupied by the index.

10. Composite index

Improved selectivity (sometimes the selectivity of combinations is higher than that of individual columns)

If the columns selected by the query are in the composite index, Oracle can return the value by accessing the index without having to access the table. )

It often occurs in WHERE clauses with the combination of AND, and especially after the combination, it is more selective than a single bond, and the common compound index can be considered on this combination.

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 index:

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

If there are several keys in the WHERE clause that are very high frequency, you can build a compound index on only these column keys

If all the columns appear in the WHERE with the same frequency and the data is physically sorted based on one of the columns, you can use this column as the first key of the composite index.

Use non-unique indexes to achieve uniqueness:

You can enforce uniqueness with existing non-unique indexes on UNIQUE constrained fields or PRIMARY KEY constrained fields of the table. The advantage of this is that even if you constrain DISABLE

The index is still valid. Therefore, the UNIQUE or PRIMARY KEY of a DISABLED can be re-ENABLE without the need to rebuild the unique index. This can be used in the ENABLE of the big data table.

Save a lot of time in the operation.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.