HP Neoview Release 2.4 Software - Manual (Page 84)

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 .
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 .
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.
NOTE: The columns used for hash keys appear in the PARTITION_ACCESS operator. The
FILE_SCAN operator does not show the hash keys for the table.
The presence or absence of hash key predicate values in the query text determines the type of
data access strategy, that is, whether one or all partitions are accessed, as follows:
• Predicates on all hash key columns: the full hash key is available, which is used to limit
access to a specific data partition for each row.
• Predicates on no hash key columns: all partitions for the table are accessed.
• Predicates on partial hash key columns: all partitions for the table are accessed because the
full hash key is needed to limit access to a specific partition.
Predicates that appear on primary key columns that are also hash key columns for the table will
appear in the part_key_predicate field of the operator.
84 Tips for Query Plan Performance Improvements