title: Optimize delete operation on table with millions of records
body: <p>I have a table with multiple columns. "reason" is one of the non-indexed columns, which may have duplicated values. In my table there are in total two hundred thousand records. I want to delete rows based on a particular reason, for example "MY REASON", whereas there are around 15K records with "MY REASON".</p>
  
  <p>When I use a standard delete query as shown below, it takes too much time (around 3 or 4 hours).</p>
  
  <pre><code>delete from my_table where reason='MY REASON';
  commit;
  </code></pre>
  
  <p>How can I speed this up?</p>
  
  <p>This is the execution plan for the same:</p>
  
  <pre><code>   Plan hash value: 2164670663
     | Id  | Operation          | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
     ---------------------------------------------------------------------------------------
     |   0 | DELETE STATEMENT   |                  |  6190 |   175K|  1019   (0)| 10:30:49 |
     |   1 |  DELETE            | MY_TABLE         |       |       |            |          |
     |*  2 |   TABLE ACCESS FULL| MY_TABLE         |  6190 |   175K|  1019   (0)| 10:30:49 |
     ---------------------------------------------------------------------------------------
  
     Predicate Information (identified by operation id):
     ---------------------------------------------------
  
     2 - filter("REASON"='MY REASON')
  </code></pre>
  
  <p>Statistics for 10rows deletion using primary key, id:</p>
  
  <pre><code>SQL&gt; set timing on;
  SQL&gt; set autotrace on explain statistics;
  SQL&gt; delete from my_table where id between 194404 and 194414;
  
  11 rows deleted.
  
  Elapsed: 00:00:19.53
  
  Execution Plan
  ----------------------------------------------------------
  Plan hash value: 2403087528
  
  ---------------------------------------------------------------------------------------
  
  | Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
  
  ---------------------------------------------------------------------------------------
  
  |   0 | DELETE STATEMENT  |                   |     1 |    29 |     1   (0)| 00:00:38 |
  
  |   1 |  DELETE           | MY_TABLE          |       |       |            |      |
  
  |*  2 |   INDEX RANGE SCAN| PK_MY_TABLE       |     1 |    29 |     1   (0)| 00:00:38 |
  
  ---------------------------------------------------------------------------------------
  
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
     2 - access("id"&gt;=194404 AND "id"&lt;=194414)
  
  
  Statistics
  ----------------------------------------------------------
           94  recursive calls
           66  db block gets
       602170  consistent gets
            0  physical reads
         8844  redo size
          528  bytes sent via SQL*Net to client
          372  bytes received via SQL*Net from client
            4  SQL*Net roundtrips to/from client
            7  sorts (memory)
            0  sorts (disk)
           11  rows processed
  
  SQL&gt;
  </code></pre>
  
  <p><strong>*ISSUE FIXED AFTER ADDING INDEX ON CHILD TABLE***</strong>
  Below are trace details:</p>
  
  <pre><code>AFTER ADDING INDEX TO CHILD TABLE
  ---------------------------------
  SQL&gt; set timing on;
  SQL&gt; set autotrace on explain statistics;
  SQL&gt; delete from MY_TABLE where id between 194241 and 194361;
  
  121 rows deleted.
  
  Elapsed: 00:00:01.37
  
  Execution Plan
  ----------------------------------------------------------
  Plan hash value: 2403087528
  
  ---------------------------------------------------------------------------------------
  
  | Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
  
  ---------------------------------------------------------------------------------------
  
  |   0 | DELETE STATEMENT  |                   |     1 |    29 |     1   (0)| 00:00:38 |
  
  |   1 |  DELETE           | MY_TABLE          |       |       |            |      |
  
  |*  2 |   INDEX RANGE SCAN| PK_MY_TABLE       |     1 |    29 |     1   (0)| 00:00:38 |
  
  ---------------------------------------------------------------------------------------
  
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
     2 - access("ID"&gt;=194241 AND "ID"&lt;=194361)
  
  
  Statistics
  ----------------------------------------------------------
            3  recursive calls
          760  db block gets
            7  consistent gets
            9  physical reads
        83968  redo size
          527  bytes sent via SQL*Net to client
          372  bytes received via SQL*Net from client
            4  SQL*Net roundtrips to/from client
            5  sorts (memory)
            0  sorts (disk)
          121  rows processed
  </code></pre>