Deletions

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 must also be flagged for deletion. Because the database server has no quick way of finding the deleted rows in the R-tree index, it must perform an index search for each row that is deleted. The performance of this deletion might improve if the R-tree index on the location column is dropped first and then re-created after the deletion is complete.

Important: Although a delete that affects many rows might execute slowly due to the presence of an R-tree index, the deletion of data and the update of the index will still execute correctly.