"John Leslie" <johnleslie_at_madasafish.com> wrote in message
news:4412dd31.0501270439.5c0054f4_at_posting.google.com...
> Do indexes speed up deletes?
>
> I am deleting 100000 rows from a table containing 1 million rows and
> it seems faster without the index. The index is created on the column
> used in the delete where clause.
>
> I created the index specifically to speed up the delete, but is my
> thinking incorrect?
Since you are deleting 1 out of every 10 rows, the only way using the index
would be faster would be if the average number of rows per block were less
than 10, so that using the index could avoid reading some blocks. The real
number would be closer to 5 (or less) per block since there is extra
overhead to using an index compared to a tablespace scan. This assumes that
the deleted rows are randomly spread throughout the table. If the rows to be
deleted were clustered together in the table, then the index might help.
Received on Thu Jan 27 2005 - 07:17:15 CST
