SQL/MX 2.x Query Guide (G06.24+, H06.03+)

Accessing SQL/MX Data
HP NonStop SQL/MX Query Guide—523728-003
2-11
Understanding Unexpected Access Paths
The rows will either get changed, in which case the selection condition does not
qualify for the newly updated row, or the rows will get updated to the same exact
value as before, in which case the index row does not change.
•
No column is referenced on the right-hand side of the SET clause; as follows:
UPDATE INVNTRY
SET RETAIL_PRICE = 20
WHERE RETAIL_PRICE > 80 AND ITEM > 10;
Because the columns are being updated to a constant value, the Halloween
update problem is avoided.
A variation of this update 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.
Forcing an Access Path
You can use the CONTROL QUERY SHAPE statement to force the access path for a
query. The CONTROL QUERY SHAPE statement applies to any DML statement.
Check the EXPLAIN output to see if the access path used is the path that you want. If
the access path is not what you want, you can force your query execution plan with the
CONTROL QUERY SHAPE statement. Follow the directions in Section 5, Forcing
Execution Plans, to specify a new access path for your query execution plan.
If you think that you might benefit from the use of one of the CONTROL QUERY
SHAPE options, check your application with and without forcing the plan by using
actual statistics from production data.
If you use one of the options, you might want to change the forced shape later for
reasons such as:
•
The query might not be able to use a more efficient index that might be created in
the future.
•
The query might not be able to benefit from future enhancements to SQL.
•
Changes to the database structure (such as dropping an index) can require
recompilation when the option is in use.
Therefore, make occurrences of CONTROL QUERY SHAPE easy to find and change
by using one or more of these alternatives:
•
Make sure the forced shape applies only to the statement and table intended. Turn
the forced shape off as soon as you are finished (CONTROL QUERY SHAPE
OFF).
•
Isolate this forced shape in its own section and perform it from the inline
application code.