DSN_VIRTUAL_INDEXES
The virtual indexes table, DSN_VIRTUAL_INDEXES, enables optimization tools to test the effect of creating and dropping indexes on the performance of particular queries.
Your subsystem or data sharing group can
contain multiple instances of these tables that are qualified by user ID. These tables are populated
with statement cost information when you issue the EXPLAIN statement or bind. They are also
populated when you specify EXPLAIN(YES) or EXPLAIN(ONLY) in a BIND or REBIND command. SQL
optimization tools might also create EXPLAIN tables that are qualified by a user ID. You can find
the SQL statement for creating an instance of these tables in member DSNTESC of the SDSNSAMP
library.
| Column name | Data type | Description |
|---|---|---|
| TBCREATOR | VARCHAR(128) | The schema or authorization ID of the owner of the table on which the index is being created or dropped. |
| TBNAME | VARCHAR(128) | The name of the table on which the index is being created or dropped. |
| IXCREATOR | VARCHAR(128) | The schema or authorization ID of the owner of the index. |
| IXNAME | VARCHAR(128) | The index name. |
| ENABLE | CHAR(1) | Indicates whether this index should be considered in the
scenario that is being tested. This column can have one of the following values:
If this column contains 'Y', but the index definition is not valid, the index is ignored. |
| MODE | CHAR(1) | Indicates whether the index is being created or dropped.
This column can have one of the following values:
|
| UNIQUERULE | CHAR(1) | Indicates whether the index is unique. This column can
have one of the following values:
|
| COLCOUNT | SMALLINT | The number of columns in the key. |
| CLUSTERING | CHAR(1) | Indicates whether the index is clustered. This column
can have one of the following values:
|
| NLEAF | INTEGER | The number of active leaf pages in the index. If unknown, the value is -1. |
| NLEVELS | SMALLINT | The number of levels in the index tree. If unknown, the value is -1. |
| INDEXTYPE | CHAR(1) | The index type. This column can have one of the
following values:
|
| PGSIZE | SMALLINT | The size, in kilobytes, of the leaf pages in the index. This column can have one of the following values: 4, 8, 16, or 32. |
| FIRSTKEYCARDF | FLOAT | The number of distinct values of the first key column. If unknown, the value is -1. |
| FULLKEYCARDF | FLOAT | The number of distinct values of the key. If unknown, the value is -1. |
| CLUSTERRATIOF | FLOAT | The percentage of rows that are in clustering order. Multiply this column value by 100 to get the percent value. For example, a value of .9125 in this column indicates that 91.25%. of the rows are in clustering order. If unknown, the value is -1. |
| PADDED | CHAR(1) | Indicates whether keys within the index are padded for
varying-length column data. This column can have one of the following values:
|
| COLNO1 | SMALLINT | The column number of the first column in the index key. |
| ORDERING1 | CHAR(1) | Indicates the order of the first column in the index
key. This column can have one of the following values:
|
| COLNOn | SMALLINT | The column number of the nth column in the index key, where n is a number between 2 and 64, including 2 and 64. If the number of index keys is less than n, this column is null. |
| ORDERINGn | CHAR(1) | Indicates the order of the nth column in the
index key, where n is a number between 2 and 64, including 2 and 64. This column
can have one of the following values:
If the number of index keys is less than n, this column is null. |
KEYTARGET_COUNT![]() |
SMALLINT NOT NULL WITH DEFAULT![]() |
The number of key-targets
for an extended index. A 0 value indicates a simple index.![]() |
UNIQUE_COUNT![]() |
SMALLINT NOT NULL WITH DEFAULT![]() |
The number of columns or
key-targets that make up the unique constraint of an index, when other non-constraint enforcing
columns or key-targets exist. Otherwise, the value is 0.![]() |
IX_EXTENSION_TYPE![]() |
CHAR(1) NOT NULL WITH DEFAULT![]() |
The type of extended index:
![]() |
DATAREPEATFACTORF![]() |
FLOAT NOT NULL WITH DEFAULT -1![]() |
The number of data pages
that are expected to be touched when an index key order is followed. The values is -1 if statistics
have not been collected. The valid values are -1 or any value that is equal to or greater than
1.![]() |
SPARSE![]() |
CHAR(1) NOT NULL WITH DEFAULT![]() |
Whether the index is sparse:
![]() |