Oracle Performance Optimization Operation 7: Index improves query efficiency when data distribution is uneven, and oracle Performance Optimization

Oracle Performance Optimization Operation 7: Index improves query efficiency when data distribution is uneven, and oracle Performance Optimization

Index selection is low, but the data value distribution is very different, you can still use the index to improve efficiency.

A. In special cases where the data distribution is uneven, the index with low selectivity should also be created.

The ServiceInfo table contains a large amount of data. Assume there are 1 million rows, with a DisposalCourseFlag field. The value range is [,].

According to the index rules described above, "the index should not be created for fields with low selectivity ",

This field has only eight values, the index value repetition rate is very high, and the index selectivity is very low, so no index is created.

However, the distribution of data values in this field is very special, as shown in the following table:

Percentage of data in the value range
1 ~ 5 1%
6 98%
7 1%

In addition, in common queries, the query of DisposalCourseFlag <6 is both frequent and frequent. If an index can be created and applied,

This will greatly improve the query efficiency in this case. Therefore, we need to create an index on this field.