Rules for updating index statistics manually
There are certain guidelines that you should follow when updating statistics in the SYSSTAT.INDEXES catalog view.
- The following rules apply to PAGE_FETCH_PAIRS:
An example of a valid PAGE_FETCH_PAIRS statistic is:
- Individual values in the PAGE_FETCH_PAIRS statistic must not be longer than 10 digits and must be less than the maximum integer value (2 147 483 647).
- Individual values in the PAGE_FETCH_PAIRS statistic must be separated by a blank character delimiter.
- There must always be a valid PAGE_FETCH_PAIRS statistic if CLUSTERFACTOR is greater than zero.
- There must be exactly 11 pairs in a single PAGE_FETCH_PAIRS statistic.
- Buffer size values in a PAGE_FETCH_PAIRS statistic (the first value in each pair) must appear in ascending order.
- Any buffer size value in a PAGE_FETCH_PAIRS statistic cannot be greater than MIN(NPAGES, 524 287) for a 32-bit operating system, or MIN(NPAGES, 2 147 483 647) for a 64-bit operating system, where NPAGES (stored in SYSSTAT.TABLES) is the number of pages in the corresponding table.
- Page fetch values in a PAGE_FETCH_PAIRS statistic (the second value in each pair) must appear in descending order, with no individual value being less than NPAGES or greater than CARD for the corresponding table.
- If the buffer size value in two consecutive pairs is identical, the page fetch value in both of the pairs must also be identical.
PAGE_FETCH_PAIRS = '100 380 120 360 140 340 160 330 180 320 200 310 220 305 240 300 260 300 280 300 300 300'
NPAGES = 300 CARD = 10000 CLUSTERRATIO = -1 CLUSTERFACTOR = 0.9
- The following rules apply to CLUSTERRATIO and CLUSTERFACTOR:
- Valid values for CLUSTERRATIO are -1 or between 0 and 100.
- Valid values for CLUSTERFACTOR are -1 or between 0 and 1.
- At least one of the CLUSTERRATIO and CLUSTERFACTOR values must be -1 at all times.
- If CLUSTERFACTOR is a positive value, it must be accompanied by a valid PAGE_FETCH_PAIRS value.
- For relational indexes, the following rules apply to FIRSTKEYCARD,
FIRST2KEYCARD, FIRST3KEYCARD, FIRST4KEYCARD, FULLKEYCARD, and INDCARD:
- For a single-column index, FIRSTKEYCARD must be equal to FULLKEYCARD.
- FIRSTKEYCARD must be equal to SYSSTAT.COLUMNS.COLCARD for the corresponding column.
- If any of these index statistics are not relevant, set them to -1. For example, if you have an index with only three columns, set FIRST4KEYCARD to -1.
- For multiple column indexes, if all of the statistics are relevant,
the relationship among them must be as follows:
FIRSTKEYCARD <= FIRST2KEYCARD <= FIRST3KEYCARD <= FIRST4KEYCARD <= FULLKEYCARD <= INDCARD == CARD
- For indexes over XML data, the relationship among FIRSTKEYCARD,
FIRST2KEYCARD, FIRST3KEYCARD, FIRST4KEYCARD, FULLKEYCARD, and INDCARD
must be as follows:
FIRSTKEYCARD <= FIRST2KEYCARD <= FIRST3KEYCARD <= FIRST4KEYCARD <= FULLKEYCARD <= INDCARD
- The following rules apply to SEQUENTIAL_PAGES and DENSITY:
- Valid values for SEQUENTIAL_PAGES are -1 or between 0 and NLEAF.
- Valid values for DENSITY are -1 or between 0 and 100.