Reading the contents of the Index Statistics window

Top area of the window

The top area of the Index Statistics window shows general information information about the index.

The columns in the window show both those statistics that were gathered at the time of the explain, and those that currently exist in the system catalog tables.

Rows in the Index Statistics window

The following information is contained in the rows of the Index Statistics window:

CREATE_TIME

The date and time that the index was created.

STATS_TIME

The last time a change was made to any recorded statistics for the index.

To see a troubleshooting tip, click on Catching inefficient access plans.

CLUSTERRATIO

The degree of data clustering of the index, represented as a percentage between 0 to 100. (Higher clustering generally means that the rows are ordered on the data pages in index key sequence.)

This value is available if you use the runstats command with the INDEXES ALL clause but without the DETAILED clause.

This value is -1 if the cluster ratio is unavailable, or if the cluster factor value is shown instead.

CLUSTERFACTOR

The data clustering factor of the index, represented as a ratio between 0 and 1. This is a more detailed measurement than the cluster ratio, and relates to page fetch pairs.

This value is available if you use runstats with the INDEXES ALL and DETAILED clauses.

This value is -1 if the clustering factor is unavailable, or if the cluster ratio value is shown instead.

NLEAF

The number of leaf pages.

"Leaf pages" are index pages that contain actual pointers to rows of data. The term comes from the analogy of a tree: the topmost (root) page and intermediate (branch) pages contain pointers to other index pages that eventually point to the leaf pages, which contain index entries and their pointers to rows of data.

NLEVELS

The number of index levels.

FIRSTKEYCARD

The number of distinct values in the first column of the index.

FIRST2KEYCARD

The number of distinct values in the first and second columns of the index.

FIRST3KEYCARD

The number of distinct values in the first, second, and third columns of the index.

FIRST4KEYCARD

The number of distinct values in the first, second, third, and fourth columns of the index.

FULLKEYCARD

The number of distinct values in all columns of the index.

UNIQUERULE

Indicates if duplicates are allowed, if unique entries must be used, or if the index is a primary index.

COLCOUNT

The number of columns in the index key.

MADE_UNIQUE

MADE_UNIQUE='Yes' means that an index was originally defined as not unique, but it was "taken over" by the system to enforce a primary key or UNIQUE constraint and therefore it now has the unique property. If the key or constraint is dropped, the index will revert to its previous non-unique status.

MADE_UNIQUE='No' means that the index still has whatever uniqueness property it was created with (that is, it has not been "taken over").

SEQUENTIAL PAGES

The number of leaf pages located on disk in index key order with few or no large gaps between them.