SQL/MX 2.x Query Guide (G06.24+, H06.03+)
Accessing SQL/MX Data
HP NonStop SQL/MX Query Guide—523728-003
2-10
Understanding Unexpected Access Paths
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 RETAIL_PRICE has a nonunique index 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 SQL compiler 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 always avoids this update situation. One solution 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. However, instances remain 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 no other index for the INVNTRY table exists 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 will likely use an alternate index for the scan operation of the update if any of
these conditions are true:
•
The alternate index contains no column that will be updated.
•
The scan is guaranteed to be a unique access, in which case the compiler will not
evaluate the same row more than once.
•
All columns to be updated have an equality predicate in the WHERE clause. For
example:
UPDATE INVNTRY
SET RETAIL_PRICE = RETAIL_PRICE * 1.1
WHERE RETAIL_PRICE = 20 AND ITEM = 7;