SQL/MP Query Guide

Improving Query Performance With Environmental
Options
HP NonStop SQL/MP Query Guide—524488-003
4-4
Optimizing the Access Path
Optimizing the Access Path
An access path is the method by which data is accessed. Access can be one of these:
•
Primary access (table scan or primary key)
•
Alternate-index access
Different access paths provide different degrees of efficiency in accessing a table.
Primary Access
There are two types of primary access: table scan and primary key.
Table Scan
In a table scan, SQL reads the entire base table from beginning to end in primary-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
•
Processing small tables
•
Processing a large percentage of rows in a table
•
Using parallel execution
•
There is no suitable index available
•
The estimated cost of reading the index and the corresponding base table rows
exceeds the cost of reading the entire table
In general, OLTP queries should not invoke full table scans unless the table is quite
small, consisting of a few blocks only.
To avoid table scans, do the following:
•
Specify a starting position by using a >= predicate on a key column.
•
Include in the select list only those columns that appear in an index and primary
key.
•
Define a new index.
•
Do not disable MDAM.
To check for full table scans, use DISPLAY STATISTICS (if row count is available) or
the EXPLAIN utility. If your EXPLAIN SCAN output says that 100 percent of the table is
being accessed, or if there is no entry for begin or end key in the EXPLAIN plan, the
scan is reading the entire table. For details, see Section 6, Analyzing Query
Performance.