EXPLAIN_OBJECT table

The EXPLAIN_OBJECT table identifies those data objects required by the access plan generated to satisfy the SQL statement.

Table 1. EXPLAIN_OBJECT Table. PK means that the column is part of a primary key; FK means that the column is part of a foreign key.
Column Name Data Type Nullable? Key? Description
EXPLAIN_REQUESTER VARCHAR(128) No FK Authorization ID of initiator of this Explain request.
EXPLAIN_TIME TIMESTAMP No FK Time of initiation for Explain request.
SOURCE_NAME VARCHAR(128) No FK 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 FK Schema, or qualifier, of source of Explain request.
SOURCE_VERSION VARCHAR(64) No FK Version of the source of the Explain request.
EXPLAIN_LEVEL CHAR(1) No FK Level of Explain information for which this row is relevant.
STMTNO INTEGER No FK Statement number within package to which this explain information is related.
SECTNO INTEGER No FK Section number within package to which this explain information is related.
OBJECT_SCHEMA VARCHAR(128) No No Schema to which this object belongs.
OBJECT_NAME VARCHAR(128) No No Name of the object.
OBJECT_TYPE CHAR(2) No No Descriptive label for the type of object.
CREATE_TIME TIMESTAMP Yes No Time of Object's creation; null if a table function.
STATISTICS_TIME TIMESTAMP Yes No Last time of update to statistics for this object; null if statistics do not exist for this object.
COLUMN_COUNT SMALLINT No No Number of columns in this object.
ROW_COUNT INTEGER No No Estimated number of rows in this object.
WIDTH INTEGER No No The average width of the object in bytes. Set to -1 for an index.
PAGES BIGINT No No Estimated number of pages that the object occupies in the buffer pool. Set to -1 for a table function.
DISTINCT CHAR(1) No No Indicates whether the rows in the object are distinct (that is, whether there are duplicates).
Possible values are:
Y
Yes
N
No
TABLESPACE_NAME VARCHAR(128) Yes No Name of the table space in which this object is stored; set to null if no table space is involved.
OVERHEAD DOUBLE No No Total estimated overhead, in milliseconds, for a single random I/O to the specified table space. Includes controller overhead, disk seek, and latency times. Set to -1 if:
  • no table space is involved
  • a partitioned object is involved and the respective table spaces for the partitions have different values
  • a partitioned object is involved and the EXPLAIN_OBJECT table is populated using section explain
TRANSFER_RATE DOUBLE No No Estimated time to read a data page, in milliseconds, from the specified table space. Set to -1 if:
  • no table space is involved
  • a partitioned object is involved and the respective table spaces for the partitions have different values
  • a partitioned object is involved and the EXPLAIN_OBJECT table is populated using section explain
PREFETCHSIZE INTEGER No No Number of data pages to be read when prefetch is performed. Set to -1 if:
  • no table space is involved
  • a partitioned object is involved and the respective table spaces for the partitions have different values
  • a partitioned object is involved and the EXPLAIN_OBJECT table is populated using section explain
EXTENTSIZE INTEGER No No Size of extent, in data pages. This many pages are written to one container in the table space before switching to the next container. Set to -1 for a table function.
CLUSTER DOUBLE No No Degree of data clustering with the index. If >= 1, this is the CLUSTERRATIO. If >= 0 and < 1, this is the CLUSTERFACTOR. Set to -1 for a table, table function, or if this statistic is not available.
NLEAF BIGINT No No Number of leaf pages this index object's values occupy. Set to -1 for a table, table function, or if this statistic is not available.
NLEVELS INTEGER No No Number of index levels in this index object's tree. Set to -1 for a table, table function, or if this statistic is not available.
FULLKEYCARD BIGINT No No Number of distinct full key values contained in this index object. Set to -1 for a table, table function, or if this statistic is not available.
OVERFLOW BIGINT No No Total number of overflow records in the table. Set to -1 for an index, table function, or if this statistic is not available.
FIRSTKEYCARD BIGINT No No Number of distinct first key values. Set to -1 for a table, table function, or if this statistic is not available.
FIRST2KEYCARD BIGINT No No Number of distinct first key values using the first 2 columns of the index. Set to -1 for a table, table function, or if this statistic is not available.
FIRST3KEYCARD BIGINT No No Number of distinct first key values using the first 3 columns of the index. Set to -1 for a table, table function, or if this statistic is not available.
FIRST4KEYCARD BIGINT No No Number of distinct first key values using the first 4 columns of the index. Set to -1 for a table, table function, or if this statistic is not available.
SEQUENTIAL_PAGES BIGINT No No Number of leaf pages located on disk in index key order with few or no large gaps between them. Set to -1 for a table, table function, or if this statistic is not 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 percentage (integer between 0 and 100). Set to -1 for a table, table function, or if this statistic is not available.
STATS_SRC CHAR(1) No No Indicates the source for the statistics. Set to 1 if from single node.
AVERAGE_SEQUENCE_ GAP DOUBLE No No Gap between sequences.
AVERAGE_SEQUENCE_ FETCH_GAP DOUBLE No No Gap between sequences when fetching using the index.
AVERAGE_SEQUENCE_ PAGES DOUBLE No No Average number of index pages accessible in sequence.
AVERAGE_SEQUENCE_ FETCH_PAGES DOUBLE No No Average number of table pages accessible in sequence when fetching using the index.
AVERAGE_RANDOM_ PAGES DOUBLE No No Average number of random index pages between sequential page accesses.
AVERAGE_RANDOM_ FETCH_PAGES DOUBLE No No Average number of random table pages between sequential page accesses when fetching using the index.
NUMRIDS BIGINT No No Total number of row identifiers in the index.
NUMRIDS_DELETED BIGINT No No Total number of psuedo-deleted row identifiers in the index.
NUM_EMPTY_LEAFS BIGINT No No Total number of empty leaf pages in the index.
ACTIVE_BLOCKS BIGINT No No Total number of active multidimensional clustering (MDC) blocks in the table.
NUM_DATA_PART INTEGER No No Number of data partitions for a partitioned table. Set to 1 if the table is not partitioned.
NULLKEYS CHAR(1) Yes No Specifies whether null keys are indexed.
  • N = Keys that contain all null values are not indexed (not considering columns or expressions from the INCLUDE clause)
  • Y = Keys that contain all null values are indexed (not considering columns or expressions from the INCLUDE clause)
A null value indicates that this object is not an index.
Table 2. Possible OBJECT_TYPE Values
Value Description
IX Index
NK Nickname
RX RCT index
DP Data partitioned table
TA Table
TF Table function
+A Compiler-referenced alias
+C Compiler-referenced constraint
+F Compiler-referenced function
+G Compiler-referenced trigger
+N Compiler-referenced nickname
+T Compiler-referenced table
+V Compiler-referenced view
XI Logical XML index
PI Physical XML index
LI Partitioned index
LX Partitioned logical XML index
LP Partitioned physical XML index