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

Accessing SQL/MX Data
HP NonStop SQL/MX Query Guide—523728-003
2-9
Understanding Unexpected Access Paths
Another plan with no full table or index scans would be:
NJ
/ \
T4_c NJ
/ \
T1_c T1
Understanding Unexpected Access Paths
Sometimes the optimizer does not choose the preferred or expected access path. If
this happens, check:
•
Does a WHERE clause refer to base table columns that do not have corresponding
indexes?
•
Do too many rows in the base table have to be randomly retrieved to justify using
alternate-index access? (See WHERE Clause Indicates Base-Table Access on
page 2-9.)
•
Was index-only access not possible because of the columns that must be
retrieved?
•
Does the request UPDATE index columns? (See How the Compiler Avoids the
Halloween Update Problem on page 2-9.)
•
Did a CONTROL QUERY SHAPE statement instruct the compiler to take a
different access path? (See Forcing an Access Path on page 2-11.)
•
Did a CONTROL QUERY DEFAULT statement influence the compiler to take a
different access path?
WHERE Clause Indicates Base-Table Access
The restriction specified by a WHERE clause might not result in a low enough
selectivity to justify alternate-index access. The overall estimated cost might be higher
for alternate-index access than for storage-key access.
How the Compiler Avoids the Halloween Update Problem
An alternate index might be ignored if a cursor or stand-alone SET UPDATE is
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 after
the current row position. This updated row might then be encountered again and
updated again and again (the so-called Halloween update problem, named after the
holiday on which it was discovered).
For example, suppose that PRICE is the first column of an index and is being
incremented by 10 percent. As the column is updated, the row is inserted after its