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

Accessing SQL/MX Data
HP NonStop SQL/MX Query Guide—523728-003
2-5
Full Table Scan
Alternate Index Access Approximate Cost
Because alternate index access relies on a join between an index and a base table,
the cost associated with alternate index access can be high and is chosen only when
the cost of a full table scan is even higher.
Full Table Scan
In a full table scan, SQL reads the entire base table from beginning value to end value
in storage-key order. (If necessary, SQL can also read the table in reverse order.) Full
table scans can be quite costly in terms of performance; response time is directly
proportional to the number of rows or blocks processed.
The optimizer might choose to scan the entire table when:
•
Small tables are being processed.
•
No suitable index is available.
•
The estimated cost of reading the index and the corresponding base table rows
exceeds the cost of reading the entire table.
To avoid or minimize table scans:
•
Define a new index consisting only of required columns on frequently used queries.
•
Use the CONTROL QUERY DEFAULT INTERACTIVE_ACCESS. See Minimizing
Full Table Scans on page 2-6.
•
Do not disable MDAM. For further information, see MultiDimensional Access
Method (MDAM) on page 2-12.
To check for full table scans, use the EXPLAIN function. If in your EXPLAIN output for
the scan node, the entries for the begin and end key contain the minimum and
maximum values for each key column, the scan is reading the entire table. The
maximum value of a key column depends on the data type of that column.