Selectivity from hard-wired assumptions

In all other cases, Derby uses a fixed number that attempts to describe the percentage of rows that will probably be returned; it might not correspond to the actual selectivity of the operation in every case. It is an assumption hard-wired into the Derby system. These assumptions are shown in Selectivity for various operations for index scans when search values are unknown in advance and statistics are not used.

Table 1. Selectivity for various operations for index scans when search values are unknown in advance and statistics are not used
Operator Selectivity
=, >=, >, <=, <, <> when data type of parameter is a boolean .5 (50%)
other operators (except for IS NULL and IS NOT NULL) when data type of parameter is boolean .5 (50%)
IS NULL .1 (10%)
IS NOT NULL .9 (90%)
= .1 (10%)
>, >=, <, <= .33 (3%)
<> compared to non-boolean type .9 (90%)
LIKE transformed from LIKE predicate (see LIKE transformations) 1.0 (100%)
>= and < when transformed internally from LIKE (see LIKE transformations) .25 (.5 X .5)
>= and <= operators when transformed internally from BETWEEN (see BETWEEN transformations) .25 (.5 X .5)
Related concepts
Selectivity from cardinality statistics