Statistics-Based versus Hard-Wired Selectivity
Statistics-Based versus Hard-Wired Selectivity
Selectivity From Cardinality Statistics
Cardinality statistics are computed by the Derby system and stored in the system tables. For information on when these statistics get created or updated, see When Cardinality Statistics Are Automatically Updated.
Derby can use cardinality statistics if:
- the statistics exist
- the relevant columns in the WHERE column are leading columns in an index
- the columns are compared to values using only the = operator
- statistics are not turned off in the system or query
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 Table 3.
Table 3. 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) |
Previous Page
Next Page
Table of Contents
Index