In this lesson, you will learn why database engines treat the recovery of tables and indexes differently, specifically focusing on why rebuilding an index is often more efficient than restoring its physical blocks from logs.
The Physical vs. Logical Difference
To a database, a table is the source of truth. It contains the actual business data—rows of customer records, orders, or transactions. If that data is lost, it must be reconstructed exactly as it was using the Write-Ahead Log (WAL) during recovery.
An index, however, is merely a performance-optimized map. It is a secondary structure derived entirely from the data already present in the table. Because an index doesn't "own" the information, the database has a choice during recovery: it can either "redo" the physical changes to the index blocks or simply discard the corrupt index and rebuild it from scratch.
Why Rebuilding Wins
Recovering physical data blocks from logs is a "heavy" process. It requires the engine to find the specific log entries, read the physical disk addresses, and apply changes block-by-block, often involving complex I/O operations and coordination with the recovery manager.
In contrast, rebuilding an index is a logical operation. The database scans the table once (a sequential read) and sorts the entries to reconstruct the index structure. If you have a massive table with a corrupted index, the database might execute something conceptually similar to this:
-- Instead of complex log-replay, the engine drops and re-creates:
DROP INDEX idx_customer_email ON Customers;
CREATE INDEX idx_customer_email ON Customers(email);
While the CREATE INDEX command takes CPU and I/O time, it is often significantly faster than navigating the fragmented, random-access history of log files. Because the table is already safe and consistent, the engine can build the index in a high-speed, stream-oriented fashion without needing to worry about the history of individual transactions.
By choosing to rebuild, the database effectively trades a bit of compute power for a massive reduction in the complexity and time required for recovery.
Key Takeaway: Because indexes are secondary structures derived from your source data, rebuilding them is often faster and safer than performing a complex, block-level restoration from recovery logs.