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

Accessing SQL/MX Data

HP NonStop SQL/MX Query Guide—523728-003

2-13

Comparing MDAM With Single Subset Access

•

With CONTROL TABLE, you can control MDAM at the current process level or for

a specific table or index. The * (asterisk) option specifies all tables. If you specify

CONTROL TABLE * MDAM ‘OFF,‘ MDAM is disabled for all tables and indexes. If

you specify CONTROL TABLE * MDAM ‘ON‘, only MDAM access will be tried for

all indexes and tables.

Turn MDAM off only if you find the query runs better without it.

Comparing MDAM With Single Subset Access

Without MDAM, tables are accessed by the single subset access method.

This figure shows single subset access on table T1. The lines in the table indicate the

rows that are being accessed by the predicates on the query. Notice that the entire

range from beginning to end is scanned to return the rows requested. Between the

requested rows are a number of rows that contain information that is not needed.

Single subset access is characterized by scanning a range of values, from begin key to

end key, that results in reading all the rows between the begin and end keys. The cost

associated with single subset access is equal to:

Cost of reading N blocks

+ cost of applying M predicates to R records

+ cost of moving the passed records

The cost of moving the passed records is a fixed cost, so it cannot be changed. The

other costs, however, can be improved.

Note. If you use CONTROL TABLE tablename MDAM ‘OFF‘, the compiler could still use

MDAM for an index of the same table.

TABLE T1

End Value

Begin Value

VST025.vsd

Single Subset Access

Requested Row