SQL/MP Query Guide

Improving Query Performance With Environmental
Options
HP NonStop SQL/MP Query Guide—524488-003
4-5
Alternate-Index Access
Primary Key
Access through a primary key means reading a portion of the base table derived from
the primary-key value.
Using a primary-key access to retrieve a row is usually cheaper than these other
methods:
•
A sequential scan
•
Base-table access through an index (described under “Alternate-Index Access,”
following)
The latter is usually less efficient because scanning a range of values in primary-key
order is faster than the random I/O required to read base table rows when scanned
through an index.
The optimizer might choose access through a primary key when
•
An index-only access is not possible.
•
The estimated cost of the primary-key access is less than the estimated cost of the
alternate-index access.
For example, suppose that the following:
°
The primary access reads 100 rows from 5 blocks.
°
The index access reads 10 rows from 10 blocks plus the index.
In this case, it is cheaper to read 5 blocks (through the primary key) than 10 blocks
plus the index (through the alternate index).
In general, a scan is more efficient if you request more than about five percent of the
rows in the table. If sequential cache is chosen by the optimizer, the percentage drops
to approximately one percent.
Alternate-Index Access
There are two types of alternate-index access:
•
Index-only access
•
Base-table access through an index
The optimizer is likely to choose access through an index when any of these are true:
•
All of the information can be retrieved from the index (index-only access) at less
cost than accessing the base table.
•
ORDER BY, GROUP BY, or DISTINCT is specified and can be satisfied by using
the index (and reading the index is less expensive than reading the base table and
performing the sort).