SQL/MX 3.2.1 Query Guide (H06.26+, J06.15+)

Accessing SQL/MX Data
HP NonStop SQL/MX Release 3.2.1 Query Guide—663851-003
2-11
MultiDimensional Access Method (MDAM)

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;
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.
MultiDimensional Access Method (MDAM)
The MultiDimensional Access Method (MDAM) provides optimal access to certain
types of information when predicates contain key columns. Based on the predicates
you specify, MDAM reads the minimal set of records and retrieves rows in key order,
allowing the optimizer to avoid sorts when it can and to do merge joins. MDAM is
costed by the optimizer whenever a key predicate is contained in the query and
statistics exist for the key. The decision to choose MDAM is based on whether the
access is less expensive than single subset access.
Use MDAM to:

Save indexes. With MDAM, you need fewer indexes and so can save the
maintenance and space associated with extra indexes.

Save resources.
TP663851.fm Page 11 Wednesday, January 30, 2013 5:37 PM