Common oracle index creation rules, oracle index creation rules

Common oracle index creation rules, oracle index creation rules

1. The table's primary key and foreign key must have an index;

2. Indexes should be available for tables with more than 300 of the data volume;

3. For tables that are often connected to other tables, indexes should be created on the connection fields;

4. fields that frequently appear in the Where clause, especially those in large tables, should be indexed;

5. The index should be built on highly selective fields;

6. Indexes should be built on small fields. Do not create indexes for large text fields or even extra-long fields;

7. The establishment of a composite index requires careful analysis. Try to replace it with a single field index:

A. correctly select the primary column field in the composite index, which is generally A highly selective field;

B. Do the fields of the composite index often appear in the Where clause in the AND mode? Are there very few or even no single-field queries?

If yes, a composite index can be created; otherwise, a single field index is considered;

C. If the fields contained in the composite index are often contained in the Where clause, they are divided into multiple single-field indexes;

D. If the composite index contains more than three fields, consider the necessity and reduce the composite fields;

E. If there are both single-field indexes and composite indexes on these fields, you can delete composite indexes;

8. Do not create too many indexes for tables that frequently perform data operations;

9. Delete useless indexes to avoid negative impact on execution plans