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.

Start of change If the DSN_VIRTUAL_INDEXES table contains more than 4096 rows with ENABLE='Y', Db2 ignores any rows after the first 4096 that it reads with this value. End of change

Begin program-specific programming interface information. If virtual indexes are being used, queries that are submitted using EXPLAIN PLAN FOR explainable-sql-statement will use virtual indexes as well as regular indexes during optimization. Any virtual index specifications in the DSN_VIRTUAL_INDEXES table are used during query optimization. The following table describes the columns in DSN_VIRTUAL_INDEXES.

Tip: Enhanced query tuning capabilities that can help you with this task are available in IBM® Db2 Query Workload Tuner for z/OS® and IBM Db2 Administration Foundation for z/OS.

Recommendation: Do not manually insert data into or delete data from this table, it is intended to be used only by optimization tools.

Table 1. DSN_VIRTUAL_INDEXES description
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: Y Use this index. N Do not use this index. If the DSN_VIRTUAL_INDEXES table contains more than 4096 rows with ENABLE='Y', Db2 ignores any rows after the first 4096 that it reads with this value.
MODE	CHAR(1)	Indicates whether the index is being created or dropped. This column can have one of the following values: C This index is to be created. D This index is to be dropped.
UNIQUERULE	CHAR(1)	Indicates whether the index is unique. This column can have one of the following values: D The index is not unique. (Duplicates are allowed.) U This index is unique.
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: Y The index is clustered. N The index is not clustered.
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: 2 The index is a nonpartitioned secondary index. D The index is a data-partitioned secondary index.
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: Y The keys are padded. N The keys are not padded.
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: A Ascending D Descending
COLNO`n`	SMALLINT	The column number of the `n`th column in the index key, where `n` is a number in the range 2–64, including 2 and 64. If the number of index keys is less than `n`, this column is null.
ORDERING`n`	CHAR(1)	Indicates the order of the `n`th column in the index key, where `n` is a number in the range 2–64, including 2 and 64. This column can have one of the following values: A Ascending D Descending 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: 'S' Index on a scalar expression 'V' XML index. blank A simple 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: 'N' No, the index contains an entry for each data row in the data table. 'Y' Yes, the index might not contain an entry for each data row in the table. 'X' This index excludes entries for data rows in which the key column contains the NULL value.