title: Optimal way to DELETE specified rows from Oracle
body: <p>I have a project that needs to occasionally delete several tens of thousands of rows from one of six tables of varying sizes but that have about 30million rows between them. Because of the structure of the data I've been given, I don't know which of the six tables has the row that needs to be deleted in it so I have to run all deletes against all tables. I've built an INDEX against the ID column to try and speed things up, but it can be removed if that'll speed things up.</p>
  
  <p>My problem is, that I can't seem to find an efficient way to actually perform the delete. For the purposes of my testing I'm running 7384 delete rows against single test-table which has about 9400 rows. I've tested a number of possible query solutions in Oracle SQL Developer:</p>
  
  <p>7384 separate <code>DELETE</code> statements took <strong>203</strong> seconds:</p>
  
  <pre><code>delete from TABLE1 where ID=1000001356443294;
  delete from TABLE1 where ID=1000001356443296;
  etc...
  </code></pre>
  
  <p>7384 separate <code>SELECT</code> statements took <strong>57</strong> seconds:</p>
  
  <pre><code>select ID from TABLE1 where ID=1000001356443294
  select ID from TABLE1 where ID=1000001356443296
  etc...
  </code></pre>
  
  <p>7384 separate <code>DELETE from (SELECT)</code> statements took <strong>214</strong> seconds:</p>
  
  <pre><code>delete from (select ID from TABLE1 where ID=1000001356443294);
  delete from (select ID from TABLE1 where ID=1000001356443296);
  etc...
  </code></pre>
  
  <p>1 <code>SELECT</code> statement that has 7384 <code>OR</code> clauses in the where took <strong>127.4s</strong>:</p>
  
  <pre><code>select ID from TABLE1 where ID=1000001356443294 or ID = 1000001356443296 or ...
  </code></pre>
  
  <p>1 <code>DELETE from (SELECT)</code> statement that has 7384 <code>OR</code> clauses in the where took <strong>74.4s</strong>:</p>
  
  <pre><code>delete from (select ID from TABLE1 where ID=1000001356443294 or ID = 1000001356443296 or ...)
  </code></pre>
  
  <p>While the last may be the fastest, upon further testing its still very slow when scaled up from the 9000 row table to even just a 200,000 row table (which is still &lt; 1% of the final tableset size) where the same statement takes <strong>14mins</strong> to run. While > 50% faster per row, that still extrapolates up to about a day when being run against the full dataset. I have it on good authority that the piece of software we used to us to do this task could do it in about <em>20mins</em>.</p>
  
  <p>So my questions are:</p>
  
  <blockquote>
    <ul>
    <li>Is there a better way to delete?</li>
    <li>Should I use a round of <code>SELECT</code> statements (i.e., like the second test) to discover which table any given row is in and then shoot off delete queries? Even that looks quite slow but...</li>
    <li>Is there anything else I can do to speed the deletes up? I don't have DBA-level access or knowledge.</li>
    </ul>
  </blockquote>