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

Accessing SQL/MX Data
HP NonStop SQL/MX Query Guide—523728-003
2-6
Full Table Scan
Full Table Scan Approximate Cost
A full table scan can be significantly higher in cost than the other methods.
Minimizing Full Table Scans
Use the INTERACTIVE_ACCESS CONTROL QUERY DEFAULT when you want to
minimize the number of expensive full table scans. You might be interested only in the
first few rows of the query result set, such as when using [First N] option. Because
the SQL/MX compiler optimizes queries based on the cost of returning a full result set,
the plan chosen could be inefficient for applications that require only the first few rows.
By setting INTERACTIVE_ACCESS to ON, the optimizer gives a higher preference to
plans that utilize indexes for key lookup over plans that use full table or index scans.
An index key lookup could be in the form of a search key or MDAM key on a primary or
secondary index. Among plans with minimum number of full table or index scans, the
optimizer chooses the plan with the lowest cost.
The following describes the optimizer strategy when INTERACTIVE_ACCESS is set to
OFF or ON:
INTERACTIVE_ACCESS 'OFF' (default)
•
Optimizer considers various plans for query execution.
•
Optimizer chooses the plan with the lowest estimated cost.
INTERACTIVE_ACCESS 'ON'
•
Optimizer considers various plans for query execution.
TABLE employee
End Value
Begin Value
SELECT * FROM TABLE employee;
ROOT
PARTITION_ACCESS
FILE_SCAN
Query Plan
VST024.vsd