|
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) |
|
