The ADVISE_INDEX table represents the recommended indexes.
Column Name | Data Type | Nullable? | Key? | Description |
---|---|---|---|---|
EXPLAIN_REQUESTER | VARCHAR(128) | No | No | Authorization ID of initiator of this Explain request. |
EXPLAIN_TIME | TIMESTAMP | No | No | Time of initiation for Explain request. |
SOURCE_NAME | VARCHAR(128) | No | No | Name of the package running when the dynamic statement was explained or name of the source file when static SQL was explained. |
SOURCE_SCHEMA | VARCHAR(128) | No | No | Schema, or qualifier, of source of Explain request. |
SOURCE_VERSION | VARCHAR(64) | No | No | Version of the source of the Explain request. |
EXPLAIN_LEVEL | CHAR(1) | No | No | Level of Explain information for which this row is relevant. |
STMTNO | INTEGER | No | No | Statement number within package to which this explain information is related. |
SECTNO | INTEGER | No | No | Section number within package to which this explain information is related. |
QUERYNO | INTEGER | No | No | Numeric identifier for explained SQL statement. For dynamic SQL statements (excluding the EXPLAIN SQL statement) issued through CLP or CLI, the default value is a sequentially incremented value. Otherwise, the default value is the value of STMTNO for static SQL statements and 1 for dynamic SQL statements. |
QUERYTAG | CHAR(20) | No | No | Identifier tag for each explained SQL statement. For dynamic SQL statements issued through CLP (excluding the EXPLAIN SQL statement), the default value is 'CLP'. For dynamic SQL statements issued through CLI (excluding the EXPLAIN SQL statement), the default value is 'CLI'. Otherwise, the default value used is blanks. |
NAME | VARCHAR(128) | No | No | Name of the index. |
CREATOR | VARCHAR(128) | No | No | Qualifier of the index name. |
TBNAME | VARCHAR(128) | No | No | Name of the table or nickname on which the index is defined. |
TBCREATOR | VARCHAR(128) | No | No | Qualifier of the table name. |
COLNAMES | CLOB(2M) | No | No | List of column names. |
UNIQUERULE | CHAR(1) | No | No | Unique rule:
|
COLCOUNT | SMALLINT | No | No | Number of columns in the key plus the number of include columns if any. |
IID | SMALLINT | No | No | Internal index ID. |
NLEAF | BIGINT | No | No | Number of leaf pages; -1 if statistics are not gathered. |
NLEVELS | SMALLINT | No | No | Number of index levels; -1 if statistics are not gathered. |
FIRSTKEYCARD | BIGINT | No | No | Number of distinct first key values; -1 if statistics are not gathered. |
FULLKEYCARD | BIGINT | No | No | Number of distinct full key values; -1 if statistics are not gathered. |
CLUSTERRATIO | SMALLINT | No | No | Degree of data clustering with the index; -1 if statistics are not gathered or if detailed index statistics are gathered (in which case, CLUSTERFACTOR will be used instead). |
AVGPARTITION_ CLUSTERRATIO | SMALLINT | No | No | Degree of data clustering within a single data partition. -1 if the table is not table partitioned, if statistics are not gathered, or if detailed statistics are gathered (in which case AVGPARTITION_CLUSTERFACTOR will be used instead). |
AVGPARTITION_ CLUSTERFACTOR | DOUBLE | No | No | Finer measurement of the degree of clustering within a single data partition. -1 if the table is not table partitioned, if statistics are not gathered, or if the index is defined on a nickname. |
AVGPARTITION_PAGE_ FETCH_PAIRS | VARCHAR(520) | No | No | A list of paired integers in character form. Each pair represents a potential buffer pool size and the corresponding page fetches required to access a single data partition from the table. Zero-length string if no data is available, or if the table is not table partitioned. |
DATAPARTITION_ CLUSTERFACTOR | DOUBLE | No | No | A statistic measuring the "clustering" of the index keys with regard to data partitions. This field holds a number between zero and one, with one representing perfect clustering and zero representing no clustering. |
CLUSTERFACTOR | DOUBLE | No | No | Finer measurement of degree of clustering, or -1 if detailed index statistics have not been gathered or if the index is defined on a nickname. |
USERDEFINED | SMALLINT | No | No | Defined by the user. |
SYSTEM_REQUIRED | SMALLINT | No | No |
|
CREATE_TIME | TIMESTAMP | No | No | Time when the index was created. |
STATS_TIME | TIMESTAMP | Yes | No | Last time when any change was made to recorded statistics for this index. Null if no statistics available. |
PAGE_FETCH_PAIRS | VARCHAR(520) | No | No | A list of pairs of integers, represented in character form. Each pair represents the number of pages in a hypothetical buffer, and the number of page fetches required to scan the table with this index using that hypothetical buffer. (Zero-length string if no data available.) |
REMARKS | VARCHAR(254) | Yes | No | User-supplied comment, or null. |
DEFINER | VARCHAR(128) | No | No | User who created the index. |
CONVERTED | CHAR(1) | No | No | Reserved for future use. |
SEQUENTIAL_PAGES | BIGINT | No | No | Number of leaf pages located on disk in index key order with few or no large gaps between them. (-1 if no statistics are available.) |
DENSITY | INTEGER | No | No | Ratio of SEQUENTIAL_PAGES to number of pages in the range of pages occupied by the index, expressed as a percent (integer between 0 and 100, -1 if no statistics are available.) |
FIRST2KEYCARD | BIGINT | No | No | Number of distinct keys using the first two columns of the index (-1 if no statistics or inapplicable) |
FIRST3KEYCARD | BIGINT | No | No | Number of distinct keys using the first three columns of the index (-1 if no statistics or inapplicable) |
FIRST4KEYCARD | BIGINT | No | No | Number of distinct keys using the first four columns of the index (-1 if no statistics or inapplicable) |
PCTFREE | SMALLINT | No | No | Percentage of each index leaf page to be reserved during initial building of the index. This space is available for future inserts after the index is built. |
UNIQUE_COLCOUNT | SMALLINT | No | No | The number of columns required for a unique key. Always <=COLCOUNT. < COLCOUNT only if there a include columns. -1 if index has no unique key (permits duplicates) |
MINPCTUSED | SMALLINT | No | No | If not zero, then online index defragmentation is enabled, and the value is the threshold of minimum used space before merging pages. |
REVERSE_SCANS | CHAR(1) | No | No |
|
USE_INDEX | CHAR(1) | Yes | No |
|
CREATION_TEXT | CLOB(2M) | No | No | The SQL statement used to create the index. |
PACKED_DESC | BLOB(1M) | Yes | No | Internal description of the table. |
RUN_ID | TIMESTAMP | Yes | FK | A value corresponding to the START_TIME of a row in the ADVISE_INSTANCE table, linking it to the same Design Advisor run. |
INDEXTYPE | VARCHAR(4) | No | No | Type of index.
|
EXISTS | CHAR(1) | No | No | Set to 'Y' if the index exists in the database catalog or 'N' if the index does not currently exist in the catalog. |
RIDTOBLOCK | CHAR(1) | No | No | Set to 'Y' if the RID index was used to make a block index in the Design Advisor. |