SQL/MX 2.x Query Guide (G06.24+, H06.03+)

Accessing SQL/MX Data
HP NonStop SQL/MX Query Guide—523728-003
2-2
Index-Only Access
Storage-Key Approximate Cost
The cost of retrieving information through a storage key depends on how many blocks
of data you must access.
Index-Only Access
Index-only access refers to an index that fully satisfies a query without accessing the
base table. That is, all columns that the query references can be found in the index.
For sequential access, an index-only scan can be superior to storage-key access of
the base table. The index row sizes are usually considerably smaller than the base
table row sizes (resulting in many more rows being retrieved per physical I/O).
An index contains one or more columns plus the columns that make up the clustering
key. An index benefits the query most when all the columns needed by the query are
located in the index. Although indexes can improve scan performance, they have a
significant cost during updates, deletes, and inserts. Indexes usually use keys other
than on the primary key, so you have alternate access paths to the data.
The optimizer is likely to choose access through an index when any of these conditions
are true:
•
The SELECT and WHERE clauses reference columns from the index.
•
All 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.
empnum first_name last_name deptnum jobcode salary
TABLE employee
93
Base Table
SELECT * From employee WHERE empnum = 93;
ROOT
PARTITION_ACCESS
FILE_SCAN_UNIQUE
QUERY PLAN
VST021.vsd