title: What is the optimal way in DB2 to delete rows from a table and a subselect?
body: <p><strong>Database</strong></p>
  
  <p>Db2</p>
  
  <p><strong>Scenario</strong></p>
  
  <p>I have the following code, where on a NODE table I have about 135k rows and a NODE_LIST common table that has about 4k rows, and deleting all the rows from NODE that can be found in NODE_LIST takes about 48 seconds.</p>
  
  <p>I would like to run the query such that it completes in under 5 seconds.</p>
  
  <blockquote>
    <p>Additionally, I have noticed that its efficiency may be degrading as I add more nodes to the NODE table.</p>
  </blockquote>
  
  <p><strong>Current Solution</strong></p>
  
  <pre><code>SELECT COUNT (*) FROM OLD TABLE(
     DELETE FROM NODE n WHERE EXISTS (SELECT * FROM NODE_LIST nl WHERE nl.ID = n.ID)
  );
  </code></pre>