Deletions from tables that have an R-tree index might be slow if the WHERE clause of the DELETE statement does not specify the R-tree indexed column.
When deletions from tables are done with a DELETE statement that uses an R-tree index to find the rows to be deleted, the entries in the R-tree index can also be deleted or marked as deleted at the same time. This is relatively efficient. However, when rows are deleted by a query that does not use an R-tree index, a separate index search is needed for each deleted row to find the corresponding index entry. This might slow the overall performance of the delete operation.
Therefore, if a large fraction of rows are to be deleted this way, it might be faster to first drop the R-tree index, delete all the rows, and then re-create the index.
For example, assume you have an employees table that includes the following two columns: id, the employee's unique ID, and location, a map that shows the location of the employee's office. A B-tree index exists on the id column, and an R-tree index exists on the location column.
Further assume that all current employees have IDs greater than 2000, and you want to clean up the table by deleting all the rows whose id is less than 2000, or nonexistent employees. The DELETE statement might look like the following example:
DELETE FROM employees WHERE id < 2000;
Because a B-tree index exists on the id column, the database server will quickly find and delete all the relevant rows in the table. However, because an R-tree index exists on the location column, each corresponding entry in the R-tree index