SQL/MP Query Guide

Selectivity and Cost Estimates
HP NonStop SQL/MP Query Guide—524488-003
5-17
The Effect of the MultiDimensional Access Method
(MDAM) on Costs
The Effect of the MultiDimensional Access Method (MDAM) on
Costs
SQL estimates costs for MDAM according to Table 5-3, but the cost estimation is
divided into two parts: reading the data for a single access and counting the total
number of accesses. The cost for reading the data is multiplied by the number of
accesses. The result is the cost for a single predicate set. For information about
predicate sets, see Transformation of Key Column Predicates and Predicate Sets on
page 3-4.
Estimating MDAM Costs for a Single Predicate Set
These factors are considered when estimating costs for a single predicate set:
•
The number of unique values within a predicate set
•
Whether the data is sparse or dense
•
Whether a column is the last-used column in a key
The Number of Unique Values Within a Predicate Set
A positioning is done for each unique value within the predicate set. For example, in a
range, SQL does a positioning for each unique value within the range. For the range B
Primary key Equality predicates do
not specify all key
columns.
I/Os for blocks, (index
selectivity × the number
of nonempty blocks in
the primary key file) ×
(the number of rows in
the primary key file)
The cost of physical I/O
plus the cost of record
overhead
Index Equality predicates do
not specify all key
columns, but all
requested columns are
in the index.
Same as above Same as above
Index Equality predicates do
not specify all key
columns, and a physical
I/O is required for each
row in the index.
I/Os for blocks, (index
selectivity × number of
nonempty blocks in the
primary key file) × (the
number of rows in the
primary key file), + a
physical I/O required for
each qualifying row in
the index.
The cost of physical I/O
plus the cost of record
overhead
Table 5-3. Costs for Indexes With Predicates (page 2 of 2)
Type of
Access Predicates Physical I/O Cost
Approximate
Index Cost