Index Advisor Surfaces Maintained Temporary Indexes Activity on DB2 for i5/OS
Web Doc
Note: This is publication is now archived. For reference only.
DB2® for i5/OS® on V5R4 provides features and tooling to define, test, and improve index strategies. With the addition of Maintained Temporary Index (MTI) detail, the user can better understand temporary indexes and their effect on system performance.
Written by Scott Forstie
SQL development leader
IBM® DB2 UDB for i5/OS
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 name for the table. |
Short 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 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 | The keys at the beginning of the KEY_COLUMNS_ADVISED field that could be reordered and still satisfy the index being advised. |
Index Type Advised | Possible values:
|
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 stop increasing when 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 | Coded reason why index was advised. Possible values:
|
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 that 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 the table for which the index is being advised, for the last time this index was advised. |
NLSS Table Advised | The sort sequence table in use by the query that generated the index advice. For more detail about sort sequences: http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/rzajq/usesortseq.htm and http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/db2/rbafzmstsortsequence.htm#sortsequence |
NLSS Schema Advised | The library of the sort sequence table. |
MTI USED | The number of times that this specific MTI has been used by the optimizer. The optimizer will stop using a matching MTI when a permanent index is created. |
MTI CREATED | The number of times that this specific 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 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 that exactly matches this advice has never been used by the queries that generated this index advice. |
The following table 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 | CHAR(10) | 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 that 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 | BIGINT | 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) | Library name of the NLSS table |
MTI_USED | MTIUSED | BIGINT | Number of times an MTI that matched the advised definition was used by the database because a matching permanent index did not exist |
MTI_CREATED | MTICREATED | INT | Number of times this specific index advice was used by the database to create a MTI |
LAST_MTI_USED | LASTMTIUSE | TIMESTAMP | Last time an MTI was used by the database because a matching permanent index did not exist |
The material included in this document is in DRAFT form and is provided 'as is' without warranty of any kind. IBM is not responsible for the accuracy or completeness of the material, and may update the document at any time. The final, published document may not include any, or all, of the material included herein. Client assumes all risks associated with Client's use of this document.