SQL/MP Query Guide

Selectivity and Cost Estimates
HP NonStop SQL/MP Query Guide—524488-003
5-8
Example Combining Predicate, Index, and Table
Selectivity
predicates in this order: index predicates, base-table predicates, executor
predicates.
The combined value is the estimated value for the table selectivity.
Table selectivity strongly influences the estimated cost for subsequent steps of the
query execution plan. For example, suppose that the estimated selectivity for a table
with 1,000 rows is 20 percent. Consequently, it is expected that 200 rows will be
retrieved. This number is used to calculate subsequent cost. For example, if the rows
need to be sorted after the scan, the expected number of rows strongly influences the
sort cost estimate.
For an example of table selectivity with join operations, see Cost of Join Operations on
page 5-15.
If there are no predicates, selectivity is 100 percent because all rows are selected for a
full table scan.
Example Combining Predicate, Index, and Table Selectivity
This example selects data from EMP_TABLE. An index is defined on EMP_TABLE that
consists of the columns EMP_DEPT, EMP_MGR, and EMP_START. The SELECT
statement has six predicates, four of which are used as index predicates:
SELECT ADDRESS, PHONE, SPOUSE_NAME
FROM EMP_TABLE
WHERE EMP_DEPT = :dept-num AND
EMP_MGR = :mgr-num AND
EMP_START > :min-start AND
EMP_START < :max-start AND
EMP_SALARY <> :min-salary AND
EMP_SALARY < :max-salary ;
The optimizer assigns a predicate selectivity to each combination of column, operator,
and value, such as EMP_DEPT, =, and :dept-num:
Predicate
Selectivity
SELECT ADDRESS, PHONE, SPOUSE_NAME
FROM EMP_TABLE
WHERE EMP_DEPT = :dept-num AND .0100
EMP_MGR = :mgr-num AND .0100
EMP_START > :min-start AND .3333
EMP_START < :max-start AND .3333
EMP_SALARY <> :min-salary AND .9900
EMP_SALARY < :max-salary ; .3333