SQL/MP Query Guide

Selectivity and Cost Estimates
HP NonStop SQL/MP Query Guide—524488-003
5-16
The Effects of Indexes and Predicates on Costs
After the cost of accessing each table in the join has been determined, the cost of the
join can be determined. For a nested join of two tables, the cost is
cost (a join b) = cost (a) + n x cost(b)
N is the number of rows that satisfy the nonjoin predicates on table A. (N is the
number of times the inner loop must be performed.) For example, suppose that DEPT
is the outer table of the join, EMPLOYEE is the inner table of the join, and 1,000
employees are in departments with department number less than 100. If the cost of
accessing DEPT is 10 and the cost of accessing EMPLOYEE is 20, the cost of (DEPT
join EMPLOYEE) is (10 + 1000 * 20), or 20,010.
The optimizer calculates separate plans for different access paths and join orders,
evaluates whether to use index-only access, chooses a specific buffering option and
method, if applicable, and takes partitioning into account.
The Effects of Indexes and Predicates on Costs
Because the complete row is not stored in an index, the cost of using an index is
different than the cost of scanning the table. Predicates also play an important role in
determining the cost associated with an index, because some predicates can be used
as a begin-key or end-key for one index but not for other indexes.
This subsection describes the cost formulae when different indexes and predicates are
available. SQL considers six different situations when computing the cost of using an
index; these situations are listed in Table 5-3.
Table 5-3. Costs for Indexes With Predicates (page 1 of 2)
Type of
Access Predicates Physical I/O Cost
Approximate
Index Cost
Primary key Equality predicates
(column = value) specify
all key columns; can
use key positioning.
Number of index levels
minus 1
The cost of physical I/O
(assumes root of file is
in cache)
Index Equality predicates
specify all key columns;
can use keyed read on
index followed by keyed
read on base table.
Number of index levels
of index minus 1, plus
the number of index
levels in the primary file
minus 1
The cost of physical I/O
-or- Equality predicates
specify all key columns;
can use keyed read on
index followed by keyed
read on base table, and
all requested columns
are in the index.
Same as above Same as above