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:

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

Feedback