Index node

An index node represents an index that is referenced by the SQL statement. An SQL statement can reference the same index multiple times; each reference is represented by an index node.

You can use an index for the following purposes:

To provide quick access to table data
To provide specific ordering of data, so that DB2® can avoid sort operations , by using clustering index.
To enforce the uniqueness of rows in the table, by using a unique index.

An index node is labeled with the name of the index and is , by default, displayed as a hexagon. The index's full key cardinality, creator name, or cluster ratio can also be displayed on the label. If the RUNSTATS utility has not collected statistics for the index, the index node is outlined in red. If the optimizer uses the default value for the full key cardinality, the cardinality is marked as the default.

If RUNSTATS has not collected statistics, the optimizer does not necessarily use the default values. If you manually update the values in SYSIBM.SYSINDEXES, the optimizer uses these values instead of the default values.

Example of how DB2 accesses an index

DB2 can use an index scan (IXSCAN) to access an index. The DB2 optimizer uses the index statistics that are listed in the table below to determine the access plan. These statistics are listed as attributes on the Descriptor window for an index node. To provide these index statistics, either use the RUNSTATS utility to collect them or manually update the catalog table SYSIBM.SYSINDEXES. If you do not provide these statistics, DB2 uses the default values that are listed in the following table.

Table 1. Index statistics that the optimizer uses
Attribute	Explanation	Default value
Cluster Ratio	Percentage of rows that are in clustering order	0
First Key Cardinality	Number of distinct values of the first key column	25
Full Key Cardinality	Number of distinct values of the key	25
Leaf Pages	Number of active leaf pages	Table rows divided by 300
Levels	Number of levels in the index partition tree	2