ADVISE_MQT table
The ADVISE_MQT table contains information about materialized query tables (MQT) recommended by the Design Advisor.
| 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 the 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 | Statement number within package to which this Explain information is related. |
| NAME | VARCHAR(128) | No | No | MQT name. |
| CREATOR | VARCHAR(128) | No | No | MQT creator name. |
| IID | SMALLINT | No | No | Internal identifier. |
| CREATE_TIME | TIMESTAMP | No | No | Time at which the MQT was created. |
| STATS_TIME | TIMESTAMP | Yes | No | Time at which statistics were taken. |
| NUMROWS | DOUBLE | No | No | The number of estimated rows in the MQT. |
| NUMCOLS | SMALLINT | No | No | Number of columns defined in the MQT. |
| ROWSIZE | DOUBLE | No | No | Average length (in bytes) of a row in the MQT. |
| BENEFIT | FLOAT | No | No | Reserved for future use. |
| USE_MQT | CHAR(1) | Yes | No | Set to 'Y' when the MQT is recommended. |
| MQT_SOURCE | CHAR(1) | Yes | No | Indicates where the MQT candidate was generated. Set to 'I' if the MQT candidate is a refresh-immediate MQT, or 'D' if it can only be created as a full refresh-deferred MQT. |
| QUERY_TEXT | CLOB(2M) | No | No | Contains the query that defines the MQT. |
| CREATION_TEXT | CLOB(2M) | No | No | Contains the CREATE TABLE DDL for the MQT. |
| SAMPLE_TEXT | CLOB(2M) | No | No | Contains the sampling query that is used to get detailed statistics for the MQT. Only used when detailed statistics are required for the Design Advisor. The resulting sampled statistics will be shown in this table. If null, then no sampling query was created for this MQT. |
| COLSTATS | CLOB(2M) | No | No | Contains the column statistics for the MQT (if not null). These statistics are in XML format and include the column name, column cardinality and, optionally, the HIGH2KEY and LOW2KEY values. |
| EXTRA_INFO | BLOB(2M) | No | No | Reserved for miscellaneous output. |
| TBSPACE | VARCHAR(128) | No | No | The table space that is recommended for the MQT. |
| 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. |
| REFRESH_TYPE | CHAR(1) | No | No | Set to 'I' for immediate or 'D' for deferred. |
| EXISTS | CHAR(1) | No | No | Set to 'Y' if the MQT exists in the database catalog. |