SQL Reference

ADVISE_INDEX Table

The ADVISE_INDEX table represents the recommended indexes.

For the definition of this table, see ADVISE_INDEX Table Definition.

Table 140. ADVISE_INDEX Table
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.
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(64K) No No List of column names.
UNIQUERULE CHAR(1) No No Unique rule:

D = Duplicates allowed

P = Primary index

U = Unique entries only allowed

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 INTEGER 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.
FULLKEYCARD BIGINT No No Number of distinct full key values; -1 if statistics are not gathered.
FIRSTKEYCARD BIGINT No No Number of distinct first 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).
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

1 if this index is required for primary key or unique key constraint, OR if this is the index on the object identifier (OID) column of a typed table.

2 if this index is required for primary key or unique key constraint, AND this is the index on the object identifier (OID) column of a typed table.

0 otherwise.


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(254) 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 INTEGER 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 on-line index reorganization is enabled and the value is the threshold of minimum used space before merging pages.
REVERSE_SCANS CHAR(1) No No

Y = Index supports reverse scans

N = Index does not support reverse scans


USE_INDEX CHAR(1) Yes No

Y = index recommended or evaluated

N = index not to be recommended


CREATION_TEXT CLOB(1M) No No The SQL statement used to create the index.
PACKED_DESC BLOB(20M) Yes No Internal description of the table.


[ Top of Page | Previous Page | Next Page ]