SQL/MP Query Guide

Selectivity and Cost Estimates
HP NonStop SQL/MP Query Guide—524488-003
5-7
Table Selectivity
If index predicates are present, their selectivity is combined with the selectivity of the
begin-key and end-key predicates. You can access this information using the EXPLAIN
utility, under the titles “Pred. selectivity” and “Index selectivity.”
Consider this query:
SELECT ITEM_NAME, RETAIL_PRICE
FROM INVNTRY
WHERE ITEM_NO = 20 ;
Suppose that there is a unique index on ITEM_NO in the INVNTRY table. If the index
is chosen to evaluate the query, then the predicate (ITEM_NO = 20) is used as both
the begin-key and the end-key predicate. Only one row must be examined because the
index is unique. If there are 100 items, the index selectivity is 1 percent, or .01.
In contrast, if there is no index on ITEM_NO, then the predicate is classified as a base-
table predicate. Because there is no begin-key or end-key predicate, it is necessary to
examine the entire table, so the index selectivity is 100 percent.
Table Selectivity
Table selectivity is the estimated percentage of rows in the table that satisfy all of the
predicates in a query. The difference between index and table selectivity can be
viewed as the difference between the selectivity of positioning predicates and the
selectivity of all (positioning and nonpositioning) predicates. You can also view this
difference as the difference between rows accessed by the disk process and rows
actually returned by the disk process to the file system.
Consider this query:
SELECT ITEM_NAME, RETAIL_PRICE
FROM INVNTRY
WHERE ITEM_NO > 10 ;
Because the query has only one predicate (WHERE ITEM_NO > 10), and 90 out of
100 items in the table satisfy the search condition, the table selectivity is also 90
percent. More than one predicate can be specified in a query, however, so table
selectivity is not always equal to predicate selectivity.
Table selectivity is the combined selectivity of all the predicates that participate in a
scan operation. The combined selectivity is estimated as follows:
1. First, the optimizer uses the value estimated for the index selectivity—the
combined selectivity of begin-key and end-key predicates for the access path.
2. Then, if there are other kinds of predicates involved, the optimizer multiplies the
value estimated for the index selectivity with the values estimated for the other
Note. If your selectivity estimate is high but you know that the number of rows to be processed
is small, consider using the CONTROL TABLE ACCESS PATH feature, described in Section 4,
Improving Query Performance With Environmental Options. For example, this feature might be
useful if you have a large table with an index on a column that has many duplicate values.