Index advisor column descriptions
Displays the columns that are used in the 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 value 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. |
| System Schema | System name of the schema. |
| System Name | System table name on which the index is advised |
| Partition | Partition detail for the index. Possible values:
|
| Keys Advised | Column names for the advised index. The order of the column names is important. The names are listed in the same order as in the CREATE INDEX SQL statement. An exception is when 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 ceases to increase once a matching permanent index is created. The row of advice remains in this table until the user removes it |
| Estimated Index Creation Time | Estimated time in seconds 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 | 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. MTIs do not persist across system IPLs. |
| 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. A blank field indicates that an MTI which exactly matches this advice has never been used by the queries which generated this index advice. |
| EVI Distinct Values | Recommended value to use when creating the advised EVI index. This value is n within the WITH n DISTINCT VALUES clause on the CREATE INDEX SQL statement. |
| First Advised | The date/time when a row is first added to the Index Advisor table for this advice. |
| MTI Used for Stats | The number of times that this specific Maintained Temporary Index (MTI) has been used by the optimizer. |
| MTI Last Used for Stats | The timestamp representing the last time this specific Maintained Temporary Index (MTI) was used as a source of statistics by the optimizer to improve the performance of a query. The MTI Last Used field can be blank. |
| Dependent Advice Count | Dependent implies that this advised index is
dependent on the creation of other dependent advised indexes and all
of the other dependent indexes must be created in order for a index
ORing bitmap implementation can be costed and utilized.
|