SQL/MX 2.x Query Guide (H06.04+, J06.03+)

Accessing SQL/MX Data
HP NonStop SQL/MX Query Guide—540437-005
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/MX 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.
A variation of this update problem occurs when newly inserted index rows satisfy the
search condition for the 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. For example:
UPDATE INVNTRY
SET RETAIL_PRICE = 200
WHERE RETAIL_RICE BETWEEN 100 AND 400;
The SQL/MX compiler always avoids these types of update situations. 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.
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.