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

Accessing SQL/MX Data
HP NonStop SQL/MX Query Guide—523728-003
2-14
How MDAM Processes Queries
As shown in the next figure, the scan of the single subset access starts with the begin
value and finishes at the end value when the last row is read. To reduce the cost of
reading N blocks, you could break the table up into a series of smaller ranges with a
high potential for hits. By reducing the number of blocks read, you also reduce the cost
of applying the predicates to the records, because fewer records are scanned.
With MDAM, access is based on the key-column predicates, and the table is accessed
in a series of smaller begin and end ranges. This scenario requires more key
positionings.
With MDAM, the begin and end key range is determined at run time. The executor
does the key positionings based on information provided by the optimizer.
The cost associated with MDAM access is determined through this formula:
Cost of reading N blocks (covered by ranges)
+ cost of applying M predicates against R records
+ cost of moving the passed rows (again, a fixed cost)
+ number of key positions required
This formula works best for low selectivity columns (low unique entry count (UEC) =
fewer key positionings).
How MDAM Processes Queries
When processing a query, MDAM:
•
Enables range predicates on leading or intervening key columns
•
Accommodates missing predicates on leading or intervening key columns
•
Performs general OR optimization
•
Handles IN lists on multiple key columns
•
Eliminates redundant and contradictory predicates
TABLE T1
VST26.vsd
MDAM Access
Rows Requested
Rows Not
Requested
Begin
End
Begin
End
Begin
End
Begin
End
Begin
End