Consider these expressions, where
col_name is an indexed column:
val1OR ... OR
Each expression is true if
is equal to any of several values. These comparisons are
equality range comparisons (where the “range” is
a single value). The optimizer estimates the cost of reading
qualifying rows for equality range comparisons as follows:
If there is a unique index on
col_name, the row estimate for
each range is 1 because at most one row can have the given
Otherwise, the optimizer can estimate the row count for each range using dives into the index or index statistics.
With index dives, the optimizer makes a dive at each end of a
range and uses the number of rows in the range as the
estimate. For example, the expression
has three equality ranges and the optimizer
makes two dives per range to generate a row estimate. Each
pair of dives yields an estimate of the number of rows that
have the given value.
col_name IN (10, 20,
Index dives provide accurate row estimates, but as the number of comparison values in the expression increases, the optimizer takes longer to generate a row estimate. Use of index statistics is less accurate than index dives but permits faster row estimation for large value lists.
system variable enables you to configure the number of values
at which the optimizer switches from one row estimation
strategy to the other. To disable use of statistics and always
use index dives, set
0. To permit use of index dives for comparisons of up to
N equality ranges, set
N + 1.
To update table index statistics for best estimates, use