SQL/MP Query Guide

Improving Query Performance With Environmental
Options
HP NonStop SQL/MP Query Guide—524488-003
4-11
Specifying an Access Path
One way to avoid the Halloween problem is to ignore the index on the column being
updated (RETAIL_PRICE in the previous example) and choose another index as the
access path, but this can result in an inefficient access plan. There are, however,
instances when it is appropriate to use the index; for example, the index on
RETAIL_PRICE is appropriate for this query, even though RETAIL_PRICE is being
updated:
UPDATE INVNTRY
SET RETAIL_PRICE = 200
WHERE RETAIL_PRICE BETWEEN 300 AND 400
If there is no other index for the INVNTRY table and the index on RETAIL_PRICE is
not used, the whole table must be read. If the table is large, using the index is much
more efficient.
SQL considers using the index on a column being updated if any of these conditions
are satisfied:
•
No key column in the index is being updated
•
All key columns in the index are specified with equal (=) predicates, as follows, and
OR optimization is not being considered:
UPDATE INVNTRY
SET RETAIL_PRICE = RETAIL_PRICE * 1.1
WHERE RETAIL_PRICE = 20 AND ITEM = 7 ;
•
No column is referenced on the right-hand side of the SET clause, and the index
selectivity of the index is less than 20 percent, as follows:
UPDATE INVNTRY
SET RETAIL_PRICE = 20
WHERE RETAIL_PRICE > 80 AND ITEM > 10 ;
The less-than-20-percent restriction for index selectivity ensures that not too many
rows are updated more than once. (For more information about selectivity, see
Section 5, Selectivity and Cost Estimates.)
A variation of the Halloween problem occurs when newly inserted index rows satisfy
the search condition for a query. In this case, some rows are updated twice, causing
the reported “number of rows updated” to exceed the actual number of rows that
satisfy the search condition.
Specifying an Access Path
You can use the CONTROL TABLE ACCESS PATH option to specify the primary
access path or a specific alternate index. The ACCESS PATH option applies only to
DML (DELETE, SELECT, UPDATE, and the SELECT portion of an INSERT-SELECT)
statements. Sample directives are
CONTROL TABLE EMPLOYEE ACCESS PATH INDEX DEPTNUM ;
CONTROL TABLE EMPLOYEE ACCESS PATH SYSTEM ;