Index advisor columns

Index advisor columns

Displays the columns that are used in the Index advisor window.
Table 1. Columns used in Index advisor window
Column name Description

Table for Which Index was Advised The optimizer is advising creation of a permanent index over this table. This is the long name for the table. The advice was generated because the table was queried and no existing permanent index could be used to improve the performance of the query.
Schema Schema or library containing the table
Short Name System table name on which the index is advised
Partition Partition detail for the index. Possible values:

<blank>, which means For all partitions

For Each Partition

specific name of the partition

Key Advised Column names for the advised index. The order of the column names is important. The names should be listed in the same order on the CREATE INDEX SQL statement, unless the leading, order independent key information indicates that the ordering can be changed.
Leading Keys Order Independent Leading, Order Independent keys. the keys at the beginning of the KEY_COLUMNS_ADVISED field which could be reordered and still satisfy the index being advised.
Index Type Advised Radix (default) or EVI
Last Advised for Query Use The timestamp representing the last time this index was advised for a query.
Times Advised for Query Use The cumulative number of times this index has been advised. This count should cease to increase once a matching permanent index is created. The row of advice will remain in this table until the user removes it
Estimated Index Creation Time Estimated time required to create this index.
Reason advised Reason why index was advised. Possible values are:
Row selection

Ordering/Grouping

Row selection and Ordering/Grouping

Logical Page Size Advised (KB) Recommended page size to be used on the PAGESIZE keyword of the CREATE INDEX SQL statement when creating this index.
Most Expensive Query Estimate Execution time in seconds of the longest running query which generated this index advice.
Average of Query Estimates (seconds) Average execution time in seconds of all queries that generated this index advice.
Rows in Table when Advised Number of rows in table for the last time this index was advised.
NLSS Table Advised The sort sequence table in use by the query which generated the index advice. For more detail on sort sequences:
NLSS Schema Advised The schema of the sort sequence table.
MTI Used The number of times that this specific Maintained Temporary Index (MTI) has been used by the optimizer.
MTI Created The number of times that this specific Maintained Temporary Index (MTI) has been created by the optimizer. MTI's do not persist across system IPL's.
MTI Last Used The timestamp representing the last time this specific Maintained Temporary Index (MTI) was used by the optimizer to improve the performance of a query. The MTI Last Used field can be blank, which indicates that an MTI which exactly matches this advice has never been used by the queries which generated this index advice.

Parent topic:
Query optimizer index advisor

Column name	Description
Table for Which Index was Advised	The optimizer is advising creation of a permanent index over this table. This is the long name for the table. The advice was generated because the table was queried and no existing permanent index could be used to improve the performance of the query.
Schema	Schema or library containing the table
Short Name	System table name on which the index is advised
Partition	Partition detail for the index. Possible values: <blank>, which means For all partitions For Each Partition specific name of the partition
Key Advised	Column names for the advised index. The order of the column names is important. The names should be listed in the same order on the CREATE INDEX SQL statement, unless the leading, order independent key information indicates that the ordering can be changed.
Leading Keys Order Independent	Leading, Order Independent keys. the keys at the beginning of the KEY_COLUMNS_ADVISED field which could be reordered and still satisfy the index being advised.
Index Type Advised	Radix (default) or EVI
Last Advised for Query Use	The timestamp representing the last time this index was advised for a query.
Times Advised for Query Use	The cumulative number of times this index has been advised. This count should cease to increase once a matching permanent index is created. The row of advice will remain in this table until the user removes it
Estimated Index Creation Time	Estimated time required to create this index.
Reason advised	Reason why index was advised. Possible values are: Row selection Ordering/Grouping Row selection and Ordering/Grouping
Logical Page Size Advised (KB)	Recommended page size to be used on the PAGESIZE keyword of the CREATE INDEX SQL statement when creating this index.
Most Expensive Query Estimate	Execution time in seconds of the longest running query which generated this index advice.
Average of Query Estimates (seconds)	Average execution time in seconds of all queries that generated this index advice.
Rows in Table when Advised	Number of rows in table for the last time this index was advised.
NLSS Table Advised	The sort sequence table in use by the query which generated the index advice. For more detail on sort sequences:
NLSS Schema Advised	The schema of the sort sequence table.
MTI Used	The number of times that this specific Maintained Temporary Index (MTI) has been used by the optimizer.
MTI Created	The number of times that this specific Maintained Temporary Index (MTI) has been created by the optimizer. MTI's do not persist across system IPL's.
MTI Last Used	The timestamp representing the last time this specific Maintained Temporary Index (MTI) was used by the optimizer to improve the performance of a query. The MTI Last Used field can be blank, which indicates that an MTI which exactly matches this advice has never been used by the queries which generated this index advice.