title: Slow Update When Using Oracle PL/SQL Table
body: <p>We're using a PL/SQL table (named pTable) to collect a number of ids to be updated.</p>
  
  <p>However, the statement</p>
  
  <pre><code>UPDATE aTable
  SET aColumn = 1
  WHERE id IN (SELECT COLUMN_VALUE
                       FROM   TABLE (pTable));
  </code></pre>
  
  <p>takes a long time to execute.</p>
  
  <p>It seems that the optimizer comes up with a very bad execution plan, instead of using the index that is defined on id (as the primary key) it decides to use a full table scan on the aTable. pTable usually contains very few values (in most cases just one).</p>
  
  <p>What can we do to make this faster? The best we've come up with is to handle low pTable.Count (1 and 2) as special cases, but that is certainly not very elegant.</p>
  
  <p>Thanks for all the great suggestions. I wrote about this issue in my blog at <a href="http://smartercoding.blogspot.com/2010/01/performance-issues-using-plsql-tables.html" rel="nofollow noreferrer">http://smartercoding.blogspot.com/2010/01/performance-issues-using-plsql-tables.html</a>.</p>