After a large purge removed about half the rows from an orders table, range queries on its indexes became slower. Index statistics show high fragmentation and low page density on several indexes. What should the DBA do?
Select an answer to reveal the explanation.
Short Explanation
An index after a mass delete is like a bookshelf with half the books pulled out: everything's still there, but you're walking past a lot of empty space. You check how bad each shelf is, then tidy it up lightly or rebuild it completely. Updating statistics just updates the catalog; it doesn't move a single book.
Full Explanation
Large deletes leave index pages partially empty and logically out of order, which increases the number of pages that range scans must read. Index optimization starts by measuring fragmentation and page density for each affected index. Lightly fragmented indexes can be reorganized, which compacts and reorders leaf pages in place with less resource use, while heavily fragmented indexes are usually rebuilt, which recreates the index from scratch and restores page density. The thresholds used to choose between them vary by organization and engine guidance, so they are applied as a local standard rather than a universal rule, and the work is scheduled in a maintenance window because rebuilds consume I/O and may lock objects. Dropping the indexes permanently would remove fragmentation but also remove the access paths the queries rely on, turning range seeks into full scans. Updating statistics refreshes the optimizer's information about data distribution, which may be worthwhile after a large purge, but it does not change the physical layout of index pages. Adding memory may let more pages stay cached, but it does not compact pages on disk or reduce the number of pages a scan touches. Exam caveat: rebuilding an index typically refreshes its statistics as part of the operation, so a separate statistics update on the same index is usually redundant. Operational check: record fragmentation and query timings before and after, and confirm range queries improve.