SQL/MP Query Guide

Improving Query Performance With Environmental
Options
HP NonStop SQL/MP Query Guide—524488-003
4-10
Understanding Unexpected Access Paths
If you specify this query:
SELECT * FROM T
WHERE A > 9 AND B > 28 ;
The optimizer operates as if these were true:
•
10 percent of the table will be selected through index A.
•
20 percent of the table will be selected through index B.
Based on this assumption, the optimizer will choose index A. However, because there
are 1,000 rows with A = 10, index A will actually access 50 percent of the table.
The Halloween Problem Could Occur
An alternate index is ignored if a cursor or standalone set update was specified for a
column that is part of the alternate index. An update of an alternate index column might
result in the deletion and reinsertion of the alternate index row following the current row
position. This updated row might then be encountered again and updated again and
again (the so-called Halloween problem, named after the holiday on which it was
supposedly discovered).
For example, suppose PRICE is the first column of an index and is being incremented
by 10 percent. As the column is updated, the row is inserted following its original
position. The cursor or set update will once again encounter the row and increment it
by 10 percent.
Selecting an index for an UPDATE query could result in a query plan that does not
terminate when executed. Consider this query:
UPDATE INVNTRY SET RETAIL_PRICE = RETAIL_PRICE * 1.1
The query requests that the price of all items in the INVNTRY table be increased by 10
percent. Suppose that there is a nonunique index on RETAIL_PRICE and that the
index contains these rows before the update:
RETAIL_PRICE
------------
10
40
Suppose that the index on RETAIL_PRICE is the chosen access plan for a query
requesting rows that satisfy the predicate:
RETAIL_PRICE > 20
The system finds the row with a retail price of 40 and updates it to 44. When the
system looks for the next row that satisfies the predicate, it finds the same row, but with
a value of 44 for RETAIL_PRICE. This process goes on forever.
The SQL compiler avoids Halloween situations whenever possible. If the use of a given
index causes a Halloween problem and a CONTROL directive instructs the compiler to
use this index, SQL issues an error message.