The available index utilities offer the ability to check, fix and rebuild indexes.
Index check (PROUTIL IDXCHECK)
Index Check identifies possible errors, but does not ever alter index entries. IDXCHECK can be run online or offline. If an IDXCHECK operation is interrupted or crashes, it does not cause data corruption. IDXCHECK works by:
1. Checking that all index entries associated with each record can be found.
2. Verifying that each value in the index is in the associated record.
3. Undertaking a validation of all index blocks and reporting an associated error as appropriate.
IDXCHECK is designed to detect index corruption as sensitively as possible. Because this requires many reads to take place during the process, it may take significant time to complete.
Available validation options for IDXCHECK are:
1. Validate physical consistence of index blocks (on by default)
2. Validate keys for each record (on by default)
3. Validate record for each key
4. Validate key order (on by default)
5. Validate tree
You can also choose to lock tables and change the error limit.
IDXCHECK processes the validation options in the following order:
1. Options 1 and 2 are executed together
2. Options 3 and 4 are executed together
Option 4 detects physical corruption in addition to validating key order. Running Option 4 online may generate false positives. If errors are detected, try re-running IDXCHECK with the option to lock the tables enabled. Locking the tables eliminates false positives.
Option 5 detects issues with indexes that span the 32/64 bit boundary of your index area in a deterministic fashion, it is off by default. The issue is also detected with Option 3, but in a non-deterministic fashion.
Note: IDXFIX and IDXCHECK are at parity and should always detect the same index corruption when IDXCHECK is run with validation options 1, 2, 3 (not on by default), and 4, and IDXFIX is run with option 3 and run offline.
If IDXCHECK is run with the default settings, it does not detect the corruption that can be detected by options 2 and 3 of IDXFIX. If IDXFIX is run online, it does not detect the errors found by Option 4 of IDXCHECK.
Index build (PROUTIL IDXBUILD)
Index Rebuild deletes existing indexes and rebuilds them from scratch IDXBUILD does not make any attempt to discover errors prior to deletion. Errors encountered during the index building process are reported. If there is a fatal error, the database cannot be recovered and must be restored from backup. For this reason, it is critical to perform a backup before running IDXBUILD. When the IDXBUILD process completes, the rebuilt indexes replace the old indexes. If there was any index corruption in the old indexes, it is not detected because the IDXBUILD replaces the old indexes with the newly rebuilt indexes.
IDXBUILD runs strictly offline. Multi-tenant index rebuild (MTIDXBUILD) and table partitioned index rebuild (TPIDXBUILD) can be run online. IDXBUILD is discussed in Rebuildingindexes.
For complete details on the index rebuild utilities, see:
