HP Neoview Release 2.5 Software - Manual (Page 102)

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.
3. If the table accessed is a fact table and if the scan is used for data aggregation, consider
whether the use of a summary/aggregate table or materialized view could improve
performance.
4. Include additional predicate values in the query text for the leading primary key columns
of the table to limit the scan, if possible.
5. Limit the columns referenced only to those columns required for the query. Avoid the use
of "select * ..." queries. If the table was initially created with the NO PARTITIONS option,
consider recreating it with partitions. A scan on a large nonpartitioned table could suffer
from lack of parallelism.
6. Check for lock contention with other concurrent queries. Check the lock_mode and
access_mode fields of the operator. Queries could block on rows locked by other queries.
Improve Index Scan Performance
Follow these guidelines to improve index scan performance:
1. If possible, include all of the columns required for a query from the base table in the secondary
index to enable index-only scans. (This is also called a "covering index.")
Index-only scans can make a substantial improvement in performance for queries that
involve a large join between the index and the base table. However, this can consume large
amounts of disk space for large base tables that require many columns in the index. In
addition, an index on a base table will reduce the load speed.
Because of these reasons, covering indexes tend to be used only for dimension tables, and
not fact tables.
2. Follow the guidelines for improving file scan performance for base tables. See “Improve
File Scan Performance” (page 101) .
3. A secondary index can use the same hash key as the base table, or can use a different hash
key. Each method has different benefits and should be reviewed for optimal performance.
See
“Significance of Hash Key Columns” (page 102) .
Significance of Hash Key Columns
The hash key columns serve as a subset of the primary key columns and distribute rows across
the table partitions. During loads or insert operations, the values associated with the hash key
columns of a row are hashed. The hashed value produces a partition number that identifies the
partition into which the row is inserted.
102 Tips for Query Plan Performance Improvements