What is the effect of the compress index operation?

• A. It removes all sub-indexes
• B. It deletes the index from the database
• C. It increases fragmentation
• D. It compresses the index and reduces fragmentation to zero

Answer: (D)

Index compression works by reorganizing and compacting the index data so that wasted space from inserts and deletes is reclaimed. This process packs the entries more tightly on the index pages, which reduces fragmentation and makes reads through the index more efficient. It does not remove sub-indexes or delete the index, and it does not cause fragmentation to increase. The goal of the compress operation is to compress the index and minimize fragmentation, often described as reducing it significantly, potentially to zero in ideal cases.