![](https://csdnimg.cn/release/download_crawler_static/9954329/bg12.jpg)
If predicates appear in the leading primary key column(s), the scan will limit the rows
searched. This is beneficial for performance, although the number of rows may still be
high, leading to long scan times.
• If subset scan is shown and the mdam token shows “used”, the file scan skips over
rows that do not match predicates on the subordinate primary key columns.
MDAM can be encouraged by ordering the primary key columns so that very low
cardinality columns appear ahead of high cardinality columns. If the query omits a
predicate on the low cardinality column, MDAM can be used to still avoid a full scan.
For query processing, it is best to order the primary key columns to utilize the predicates
as effectively as possible, by limiting the range of rows scanned.
4. Note the cardinality (ROWS OUT) of the FILE_SCAN operator in the EXPLAIN plan output.
a. If the cardinality is very low compared to the table row count, review this section to
determine if improvements can be made. Also, 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, it is unlikely that a
secondary index will be used for access.
Secondary indexes are seldom used for fact tables due to the performance impact on
table loads, however, they can be effective for large dimension tables. No benefit exists
in using secondary indexes on small tables, or for most medium-sized tables.
5. If the table accessed is a fact table and if the scan is used for data aggregation, consider
whether a summary/aggregate table or materialized view could be used to improve
performance.
Materialized views can be used to pre-join data, which eliminates the join cost at runtime.
6. Limit the columns referenced only to those columns required for the query. Avoid the use
of "SELECT * FROM ..." queries.
7. If the table was initially created with the NO PARTITIONS option, verify its size, and consider
re-creating it with partitions. A scan on a large nonpartitioned table could suffer from lack
of parallelism. In most cases, tables with over 100,000 rows should be partitioned.
8. Avoid the practice of applying SQL functions to leading primary key columns used for file
scan operations, as this may cause the optimizer to choose a full scan, regardless of predicate
effectiveness. If in doubt, check the query plan to verify the data access path.
9. Avoid defining large VARCHAR columns when the data content is significantly smaller
than the defined size. When a query selects a VARCHAR column, the column is expanded
to its full size as it is being exchanged throughout the cluster, which can impact performance.
Instead, use the smallest possible VARCHAR size.
10. Check for lock contention with other concurrent queries. Check the lock_mode and
access_mode tokens of the FILE_SCAN operator, or check for lock contention for the query
in the repository or runtime statistics. 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. Also,
HP Restricted18 Tips for Query Plan Performance Improvements