SQL/MP Query Guide

Improving Query Performance With Environmental
Options
HP NonStop SQL/MP Query Guide—524488-003
4-9
Understanding Unexpected Access Paths
Index-Only Access Is Not Possible
Index-only access is not used if any of these are true:
•
The columns required by the query are not all included in the index.
•
An exclusive lock mode is selected.
•
OR optimization is done. (For more information, see Writing Efficient Predicates on
page 3-15.)
•
The access is part of a DECLARE CURSOR statement with a FOR UPDATE
clause specifying a column that belongs to the index key, or part of an UPDATE
statement that updates a column of the index key.
SQL does not use index-only access if it estimates that more than a third of the
table is to be updated or deleted.
•
Data is accessed from a protection view defined with a WHERE clause—unless
real sequential block buffering (RSBB) can be used.
For more information on RSBB, see Reducing Messages With Buffering Options
on page 4-21.
When the SELECT statement specifies more columns than can be satisfied by the
index alone (ineffective projection), base-table access is required. The index might be
used to access the base table, but index-only access is not possible.
WHERE Clause Indicates Base-Table Access
The restriction specified by a WHERE clause might not result in a low enough
selectivity to justify alternate-index access. For example, the overall estimated cost
might be higher for alternate-index access than for primary key access. In addition, if
the base table is small the optimizer might choose to read the base table without
accessing the alternate index.
Data Is Not Evenly Distribution
The optimizer operates as if a column contains a uniform distribution of values.
Consequently, the optimizer might not operate appropriately if the values are, in fact,
unevenly distributed.
For an example, consider table T, which has these characteristics:
•
The table contains 2000 rows.
•
There are two indexes on the table: one on column A and one on column B.
•
The values in column A range from 1 to 10.
There are, however, 1,000 rows with A=10. The other values of column A are
evenly distributed from 1 to 9.
•
The values in column B range from 21 to 30 and are evenly distributed.