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