For the BETWEEN clause, the
optimizer makes the decision depending on the
following (among other things):
The range of the BETWEEN.
The range of the HIGH and LOW values of the index column(s).
For example, if the range in the BETWEEN predicate is very wide relative to
the HIGH and LOW values stored in the index, the optimizer expects
a large number of rows to be returned
and is therefore more likely to choose a serial
scan. (Note that the optimizer assumes a uniform
distribution of values.) In the case
of BETWEEN predicates with host variables, the actual range of values
cannot be known until run time, so the optimizer uses default selectivity
values.
