Oracle primary index learning Summary

Indexes are common database objects. The purpose of creating indexes is to increase the record retrieval speed. Whether or not it is set properly affects the performance of Database applications and databases. Although there are a lot of materials about indexing usage, DBAs and developers often deal with it, there are still many people who have misunderstandings about it, for example, I am not very clear about the principles. So I would like to make a summary and ask the experts to check your experience.

Generally, a single column index is used as long as this column is included in the Where condition of the Select, Delete, and Update statements.

For multi-column indexes, if the primary key (ID, Name, and Age fields in a table are indexed together ), the index is used only when the Where condition contains the first or several columns of the index.

Such as the ID condition, combination condition of ID and Age, combination condition of ID, Name, and Age, but only the Name and Age conditions, no ID condition will use the index. Because this index is first sorted by ID, the same ID is then sorted by Name, and the same ID and Name are then sorted by Age.

In general, indexes should be created if the following conditions are met:

1. columns are often used in the Where condition or connection condition.

2. The column data is scattered, that is, there are not many duplicate values

3. A column contains a large number of null values.

4. Several columns are often used together in the Where condition or join condition (joint index)

5. Most searches only return a small part of the records in a large table (2% ~ 5%)

Indexes should not be created in the following situations:

1. The table data volume is small.

2. columns are rarely used in query conditions.

3. A large amount of data is returned for most searches.

4. frequent Update operations on tables

However, the index is not always the best choice. If Oracle does not use the index when there is an index, this is not an optimizer error. In some cases, Oracle does select Full Table Scan instead of Index Scan ). These situations usually include:
1. The table is not statistical, or the statistics are outdated, leading to Oracle misjudgment.
2. According to the number of records and data blocks in the table, full table scan is faster than index scan.

However, whether or not Oracle actually uses indexes and whether indexes are actually valid must be tested in the field. It is reasonable to make an execution plan (explain) on the product database before writing the abbreviated complex SQL statement into the application ). Explain will get Oracle's plan for the SQL statement, and you can clearly see how Oracle optimizes the SQL statement.

If you often explain, you will find that it is not a good habit to write the SQL statement, because the resolution plan of overly complicated SQL statements is often unsatisfactory. In fact, if the database table structure is properly designed, we usually seldom need to write complicated SQL statements. Splitting complex SQL statements can greatly improve efficiency sometimes because they can be well optimized.

Generally, querying data through indexes is faster than scanning the entire table. Using indexes when joining multiple tables can also improve efficiency. Another advantage of using an index is that it provides uniqueness verification of the primary key. However, you must pay attention to the cost when using indexes. The index requires space for storage and regular maintenance. The index itself is also modified whenever a record is increased or decreased in the table or the index column is modified. This means that the Insert, Delete, and Update operations for each record will pay 4 or 5 more disk I/O. Because indexes require additional storage space and processing, unnecessary indexes will slow the query response time.

Oracle has two access modes for indexes:

Unique Index Scan)

Index Range Scan)

Applicable to two scenarios: 1. One-range-based search 2. Non-unique index-based search

When the where clause contains multiple index columns and contains non-"=", Oracle will discard the non-"=" index.

When there are multiple index columns in the where clause and all of them are not "=", Oracle will only use one index. For which index to use, this depends on the situation.

If two or more of them have the same level, but we only want to use one of them (through it, the number of records retrieved is small), we can use the following method:

 Ename  ENo DNo EType

If we only want to use the index on ENo (the number of records in the other two conditions is small, and merging is not cost-effective), we can adapt it:

 EName  ENo DNo   

 

Similarly, if we want to use the index of a column, we cannot perform operations on this column.

   SAL

The index on the SAL Column cannot be used. It can be adapted:

   SAL

The Oracle index is not very in-depth, just superficial understanding, and more research will be used in the future.