title: Why is this DELETE query so slow on Oracle?
body: <p>I'm writing some database upgrade scripts, and am running into a query that takes a lot longer than I think it should:</p>
  
  <pre><code>DELETE FROM TPM_TASK WHERE TASK_TYPE='System';
  </code></pre>
  
  <p>This query takes over an hour, and I'm curious what the culprit is.</p>
  
  <p>The execution plan is:</p>
  
  <pre><code>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   
  </code></pre>
  
  <p>Running:</p>
  
  <pre><code>select count(1) FROM TPM_TASK WHERE TASK_TYPE='System';
  </code></pre>
  
  <p>The plan is:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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.</p>
  
  <p>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?</p>