Using Predicates with BETWEEN

For the BETWEEN clause, the optimizer makes the decision depending on the following (among other things):

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.