title: Deletes Slow on a Oracle BIG Table
body: <p>I have a table which has around 180 million records and 40 indexes. A nightly program, loads data into this table but due to certain business conditions we can only delete and load data into this table. The nightly program will bring new records or updates to existing records in the table from the source system.We have limited window i.e about 6 hours to complete the extract from the source system, perform business transformations and finally load the data into this target table and be ready for users to consume the data in the morning. The issue which we are facing is that the delete from this table takes a lot of time mainly due to the 40 indexes on the table(an average of 70000 deletes per hour). I did some digging on the internet and see the below options</p>
  
  <p>a) <strong>Drop or disable indexes before delete and then rebuild indexes:</strong> The program which loads data into the target table after delete and loading the data needs to perform quite a few updates for which the indexes are critical. And to rebuild 1 index it takes almost 1.5 hours due to the enormous amount of data in the table. So this approach is not feasible due to the time it takes to rebuild indexes and due to the limited time we have to get the data ready for the users</p>
  
  <p>b) <strong>Use bulk delete:</strong> Currently the program deletes based on rowid and deletes records one by one as below</p>
  
  <p><code>DELETE
             FROM &lt;table&gt;
            WHERE rowid = g_wpk_tab(ln_i);
  </code></p>
  
  <p>g_wpk_tab is the collection which holds rowids to be deleted which is read by looping via FOR ALL and I do an intermediate commit every 50000 row deletes.</p>
  
  <p>Tom of AskTom says in this discussion over here says that the bulk delete and row by row delete will take almost the same amount of time </p>
  
  <p><a href="http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5033906925164" rel="nofollow noreferrer">http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5033906925164</a></p>
  
  <p>So this wont be a feasible option as well</p>
  
  <p>c)<strong>Regular Delete:</strong> Tom of AskTom suggests to use the regular delete and even that takes a long time probably due to the number of indexes on this table</p>
  
  <p>d)<strong>CTAS:</strong> This approach is out of question because the program needs to recreate the table , create the 40 indexes and then proceed with the updates and I mentioned above an index will take atleast 1.5 hrs to create</p>
  
  <p>If you could provide me any other suggestions I would really appreciate it.</p>
  
  <p><strong>UPDATE:</strong> As of now we have decided to go with the approach suggested by <a href="https://stackoverflow.com/users/409172/jonearles">https://stackoverflow.com/users/409172/jonearles</a> to archive instead of delete. Approach is to add a flag to the table to mark the records to be deleted as DELETE and then have a post delete program run during the day to delete off the records. This will ensure that the data is available for users at the right time. Since users consume via OBIEE we are planning to set content level filter on the table to not look at the archival column so that users needn't know about what to select and what to ignore.</p>