SQL/MX Release 2.0 Best Practices

MDAM 28
MDAM
MDAM Query Technology Design Considerations
To exploit the capabilities of MDAM query technology, first you must know the data distributions and
unique values for each key column in the table. Generally, you should order key columns to provide the
most efficient and direct access into the table, based on the columns most frequently used by the
queries. If you have a choice in determining the order of the key columns, order them by their unique
value count, from least to greatest. For example, assume that the columns and unique entry (values)
counts of the table are:
Column Unique Entry Count
Customer key 1,000,000
Store key 500
Date key 1,825
Product key 50,000
In this table, the existing order of key columns might be appropriate if queries always provide the
customer key when accessing this table, perhaps through a dimension table lookup. If the customer key
and date key were supplied, but not the store key, MDAM query technology might probe through the
store key values to access the qualifying data. However, if the customer key was not supplied, a full table
scan might be used.
For a detailed description of the functionality of the SQL/MX MultiDimensional Access Method (MDAM)
facility, refer to the document Efficient Search of Multidimensional B-Trees, written by the SQL/MP
development group.
Forcing MDAM Query Technology
MDAM query technology can provide substantial performance benefits when used properly. However,
MDAM cannot resolve all performance issues. Do not force MDAM query technology on all queries. The
optimizer normally chooses the correct optimizations. Forcing MDAM query technology when the
optimizer chooses otherwise may degrade query performance. Sometimes forcing MDAM query
technology improves queries; however, before you take that action, you should have a thorough
understanding of the data relationships and queries, and always perform extensive testing.