Database manager indexes advised system table
This topic describes the indexes advised system table.
| Column name | System column name | Data type | Description |
|---|---|---|---|
| TABLE_NAME | TBNAME | VARCHAR(258) | Table over which an index is advised |
| TABLE_SCHEMA | DBNAME | VARCHAR(128) | SQL schema containing the table |
| SYSTEM_TABLE_NAME | SYS_TNAME | CHAR(10) | System table name on which the index is advised |
| PARTITION_NAME | TBMEMBER | CHAR(10) | Partition detail for the index |
| KEY_COLUMNS_ADVISED | KEYSADV | VARCHAR(16000) | Column names for the advised index |
| LEADING_COLUMN_KEYS | LEADKEYS | VARCHAR(16000) | 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 | INDEX_TYPE | CHAR(14) | Radix (default) or EVI |
| LAST_ADVISED | LASTADV | TIMESTAMP | Last time this row was updated |
| TIMES_ADVISED | TIMESADV | BIGTINT | Number of times this index has been advised |
| ESTIMATED_CREATION_TIME | ESTTIME | INT | Estimated number of seconds for index creation |
| REASON_ADVISED | REASON | CHAR(2) | Coded reason why index was advised |
| LOGICAL_PAGE_SIZE | PAGESIZE | INT | Recommended page size for index |
| MOST_EXPENSIVE_QUERY | QUERYCOST | INT | Execution time in seconds of the query |
| AVERAGE_QUERY_ESTIMATE | QUERYEST | INT | Average execution time in seconds of the query |
| TABLE_SIZE | TABLE_SIZE | BIGINT | Number of rows in table when the index was advised |
| NLSS_TABLE_NAME | NLSSNAME | CHAR(10) | NLSS table to use for the index |
| NLSS_TABLE_SCHEMA | NLSSDBNAME | CHAR(10) | Schema name of the NLSS table |
| MTI_USED | MTIUSED | BIGINT | The number of times that this specific Maintained Temporary Index (MTI) has been used by the optimizer. The optimizer stops using a matching MTI once a permanent index is created. |
| MTI_CREATED | MTICREATED | INTEGER | The number of times that this specific Maintained Temporary Index (MTI) has been created by the optimizer. MTIs do not persist across system IPLs. |
| LAST_MTI_USED | LASTMTIUSE | TIMESTAMP | 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. The blank field indicates that an MTI which exactly matches this advice has never been used by the queries which generated this index advice. |
| AVERAGE_QUERY_ESTIMATE _MICRO | QRYMICRO | BIGINT | Average execution time in microseconds of the query which drove the index advice |
| EVI_DISTINCT_VALUES | EVIVALS | INTEGER | 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. |
| INCLUDE_COLUMNS | INCLCOL | CLOB(10000) | EVI INCLUDE expressions for index creation. |
| FIRST_ADVISED | FIRSTADV | TIMESTAMP | When this row was inserted. |
| SYSTEM_TABLE_SCHEMA | SYS_DNAME | CHAR(10) | System name of the table schema. |
| MTI_USED_FOR_STATS | MTISTATS | BIGINT | Number of times Maintained Temporary Index was used as a source for optimizer statistics. |
| LAST_MTI_USED_FOR_STATS | LASTMTISTA | TIMESTAMP | The timestamp representing the last time this specific Maintained Temporary Index was used as a source of statistics by the optimizer to improve the performance of a query. |
| DEPENDENT_ADVICE_COUNT | DEPCNT | BIGINT | The number of times this index advice was dependent upon other advice. |