SQL/MP Query Guide

Selectivity and Cost Estimates
HP NonStop SQL/MP Query Guide—524488-003
5-12
Cost of Accessing Tables
Cost of Accessing Tables
The cost of using an index to access a table in a query that references only one table
is as follows:
Cost(index) = Cost(physical I/O)
+ Cost(record overhead)
+ Cost(data transfer)
+ Cost(message)
+ Cost(sub-query)
+ Cost(sort)
If the cost of a component is less than one physical I/O, the cost of the component is
truncated to zero.
If a query references multiple tables, the optimizer also considers the different
combinations in which the tables can be joined. Each of these combinations is also
assigned a numeric cost.
These paragraphs describe the elements used in the calculation of index cost and join
cost.
Cost of Physical I/Os
The cost of physical I/O is the estimated number of physical I/Os that must be
performed to retrieve all the rows that satisfy the predicates of the query. This estimate
includes all physical I/Os to retrieve the requested data.
Consider the query:
SELECT ITEM_NUMBER, ITEM_NAME, RETAIL_PRICE
FROM INVNTRY
WHERE RETAIL_PRICE > 100
Suppose that there is an index on RETAIL_PRICE of the INVNTRY table. INVNTRY
contains 10,000 rows, and each row is 100 bytes. Assuming a block size of 4 KB,
INVNTRY has approximately 250 blocks. ITEM_NUMBER is the primary-key column
and is 4 bytes, and RETAIL_PRICE is also 4 bytes. Therefore, the index row has a size
of 10 bytes (key tag plus four plus the primary key size), and the index has about 25
blocks. Finally, suppose that 100 rows or 1 percent of the rows will satisfy the
predicate.
If the query is to be evaluated using the primary key, 250 pages must be read from
disk, and the cost of physical I/O would be 250. If the query is to be evaluated with the
index, 102 pages must be read (two index pages + one data page for each qualifying
index row), and the cost of physical I/O would be 102.