CHECK INDEX

The CHECK INDEX utility verifies that indexes are consistent with the data in their tables. An index is a separate structure from the table, so damage or failed maintenance can leave the two out of sync. CHECK INDEX finds those mismatches.

What is CHECK INDEX

• CHECK INDEX compares each index entry with the actual table rows it should point to.
• It reports missing entries, extra entries, and entries pointing to wrong rows.
• You can check all indexes of a table space with CHECK INDEX (ALL).
• CHECK INDEX only reports problems - it does not fix them.
• It is a read-only style check, so it is safe to run for diagnosis.

When to run CHECK INDEX

• After a RECOVER, to confirm the indexes match the restored data.
• When an index is in CHECK pending status after maintenance.
• When queries return wrong or missing rows but the table data looks correct - a classic index corruption symptom.
• After any utility failure that was interrupted halfway through index processing.

CHECK INDEX vs REBUILD INDEX

• CHECK INDEX diagnoses: it tells you whether the index matches the table.
• REBUILD INDEX fixes: it drops and recreates the index from the table data.
• If CHECK INDEX reports mismatches, run REBUILD INDEX on the affected indexes.
• An index in REBUILD pending (RBDP) status cannot be fixed by CHECK INDEX - only REBUILD INDEX clears it.

Example of a CHECK INDEX control statement:-