HP Neoview Release 2.4 Software - Manual (Page 83)

8 Tips for Query Plan Performance Improvements
Determine the Base Table Access Strategy
Through the FILE_SCAN operator, the query plan provides the information you need to
understand the type of access used to obtain data from a table. The FILE_SCAN operator always
runs within the disk process. The scan_type field of the FILE_SCAN operator indicates the type
of access made to the table partitions. The choices are:
Description scan_type Field Choices
Indicates the table or partition is scanned completely and sequentially.
This usually means that the query text primary key (or secondary
index) predicates were not useful for limiting the scan. full scan
Indicates the table or partition was partially scanned, usually due to
the presence of beginning or ending primary key predicates, or
MDAM predicates. subset scan
Indicates that exactly one row is accessed (see FILE_SCAN_UNIQUE
operator). unique scan
Indicates whether the scan was in forward or reverse order, based
upon the ordering of the table's primary key columns and the order
specified in the query text. No performance penalty exists for a reverse
order scan. scan direction
Indicates Multi-Dimensional Access Method (MDAM). MDAM is an
access strategy used by the disk process to prevent full table/partition
scans by "probing" through the data and skipping groups of unneeded
rows, which improves performance. Typically, MDAM is used only
when predicates are absent from low cardinality primary key columns
(often the left-most columns), but present for other columns. mdam
Contains the predicate values specified for the primary key columns,
if any. If no predicate values are present for a column, these fields
contain the terms <min> or <max>, indicating that the entire domain
range of values for the column is selected by the query. begin_key/end_key
When to Change the Default Aggregate Function
You might choose to change the default aggregate function if you want to check whether
processing skew might exist. Add the Processing Node Utilization graph three times to the
workspace. For one graph, apply the minimum aggregate function. For the second graph, apply
the maximum aggregate function. For the third graph, apply the average aggregate function.
See if there is a huge discrepancy in the values. If so, this could be indicative of problems, including
skewed data. For more information about aggregate functions, see
“Aggregate Functions”
(page 23) .
Improve File Scan Performance
Follow these guidelines to improve file scan performance:
1. Ensure that tables have been reorganized after loads/inserts of large amounts of data.
Reorganizing tables keeps data blocks full with rows and physically adjacent, which improves
scan performance.
2. Note the cardinality of the FILE_SCAN operator. See Cardinality in the Glossary .
a. If the cardinality is very low compared to the table row count, consider whether the
use of a secondary index could limit the scan and improve performance.
b. If the cardinality is very high and close to the table row count, then it is unlikely that a
secondary index will be used for access.
Determine the Base Table Access Strategy 83