This content has been marked as final. Show 4 replies
I’ve got two points (one of them quite obvious):
It depends on what you deleted and the index you are using. If the number of records for the key that you using in your search has not changed, even if the table is 10 times smaller, the index scan would consume the same resources. Let say we have a table with an index on a date column. The table contains information since 2001 (2001-2011). You delete everything before May, 2010. If you search for a date in 2011 you would not get significant performance benefit from the delete, but if you search for a date in 2003 you would get significant performance benefits
It depends on the clustering factor of the index:
o High clustering factor indicates that data is almost randomly scattered in regards to the index, so there is a higher chance that there is one (or very few) records per block for a given index value. Deleting most of the records would cause many blocks not to have any entry for the given index key value, so the index scan will not visit them at all. That translates to good performance benefits.
o Low clustering factor indicates that data is ordered in regards to the index key. That means that many of the records for an index key reside in one block. Deleting large number of records would cause the records for the index key to get sparse in that block, but there is a smaller chance that the block would not hold any records for the given index key. That means that the block would most likely be visited, even though fewer records would be retrieved, and therefore the performance benefits of delete would be (much) smaller.