Performance Tuning - Question 006

Why is it necessary to reorganize an index because of delete activity?

A . Empty blocks are read during index lookups.
B. Index blocks that contain only a single entry still must be maintained.
C . Empty blocks are skipped during an index lookup.
D . Empty branch nodes are still visited during an index scan.

Explanation:

Answer B: Sparsely populated index blocks degrade performance. If you have deleted a significant number of rows from the table that the index is on, you should reorganize the index. The general guideline is 20 percent of the rows deleted should trigger the DBA to reorganize the index. D is not correct because you would not have an empty branch node in a B-Tree index (must be balanced). A and B are not correct because index lookups only apply to populated leaf blocks. However, empty blocks are read during index scans, which is another reason why you should rebuild your index after a large number of rows have been deleted.