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.
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.
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 |