The query optimizer analyzes the row selection in the query and determines, based on default values, if creation of a permanent index would improve performance. If the optimizer determines that a permanent index would be beneficial, it returns the key columns necessary to create the suggested index.
The index advisor information can be found in the Database Monitor logical files QQQ3000, QQQ3001 and QQQ3002. The advisor information is stored in columns QQIDXA, QQIDXK and QQIDXD. When the QQIDXA column contains a value of 'Y' the optimizer is advising you to create an index using the key columns shown in column QQIDXD. The intention of creating this index is to improve the performance of the query.
In the list of key columns contained in column QQIDXD the optimizer has listed what it considers the suggested primary and secondary key columns. Primary key columns are columns that should significantly reduce the number of keys selected based on the corresponding query selection. Secondary key columns are columns that may or may not significantly reduce the number of keys selected.
The optimizer is able to perform index scan-key positioning over any combination of the primary key columns, plus one additional secondary key column. Therefore it is important that the first secondary key column be the most selective secondary key column. The optimizer will use index scan-key selection with any of the remaining secondary key columns. While index scan-key selection is not as fast as index scan-key positioning it can still reduce the number of keys selected. Hence, secondary key columns that are fairly selective should be included.
Column QQIDXK contains the number of suggested primary key columns that are listed in column QQIDXD. These are the left-most suggested key columns. The remaining key columns are considered secondary key columns and are listed in order of expected selectivity based on the query. For example, assuming QQIDXK contains the value of 4 and QQIDXD specifies 7 key columns, then the first 4 key columns specified in QQIDXK would be the primary key columns. The remaining 3 key columns would be the suggested secondary key columns.
It is up to the user to determine the true selectivity of any secondary key columns and to determine whether those key columns should be included when creating the index. When building the index the primary key columns should be the left-most key columns followed by any of the secondary key columns the user chooses and they should be prioritized by selectivity.
| Note: | After creating the suggested index and executing the query again, it is possible that the query optimizer will choose not to use the suggested index. |