CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Mike Christensen
Mike Christensen

Reputation: 91590

Why is this DELETE query so slow on Oracle?

I'm writing some database upgrade scripts, and am running into a query that takes a lot longer than I think it should:

DELETE FROM TPM_TASK WHERE TASK_TYPE='System';

This query takes over an hour, and I'm curious what the culprit is.

The execution plan is:

DELETE STATEMENT    899.0   887 57793984    35481   1454721 899                             ALL_ROWS                                            
DELETE                                                      1   TPMDBO  TPM_TASK                                                        
TABLE ACCESS (FULL) 899.0   887 57793984    35481   1454721 1   TPMDBO  TPM_TASK    FULL    TABLE   ANALYZED    1

Running:

select count(1) FROM TPM_TASK WHERE TASK_TYPE='System';

The plan is:

SELECT STATEMENT    92.0    89  14527479    1   7   92                  ALL_ROWS                                            
SORT (AGGREGATE)                1   7   1           AGGREGATE                                                   
INDEX (FAST FULL SCAN)  92.0    89  14527479    35481   248367  1   TPMDBO  TPM_TASK_TASK_TYPE  FAST FULL SCAN  INDEX   ANALYZED

This query is quite fast and gives me 44,202 rows. The total number of rows in the table is 71419. Since I'm deleting over half the rows, I'm thinking Oracle isn't bother to use the index at all on the delete, which is fine. A full scan of 71,000 rows should still only take a few seconds anyhow.

There are no triggers on this table. There aren't any other tables that have FK constraints on this table, however there are a few views and SQL functions that use this table. The only application that uses this database is our web server, which is shut down during the upgrade - so I don't think there's any locking issues going on. Any other ideas?

Upvotes: 3

Views: 17236

Answers (1)

Glitch Desire
Glitch Desire

Reputation: 15023

Baring in mind the considerations you gave, the only thing I can think of is some kind of UNINDEXED REFERENCE CONSTRAINT referring to this table. Try running this script against the table, it should give you a report with any unindexed references it finds. There's certainly no reason why this operation should take close to this long.

Upvotes: 8

Related Questions