title: Returning results from a NOT IN query very quickly
body: <p>We've got a system where we've got a collection of items (> 1 million), and several things processing it. Each processor should only process each item once, and the processors have a heirarchy.</p>
  
  <p>Our current implementation is to have a 'processed' table keeping track of what each processor has already done:</p>
  
  <pre><code>CREATE TABLE items (id NUMBER PRIMARY KEY, ...)
  CREATE TABLE itemsProcessed(
      item NUMBER REFERENCES items(id),
      processor NUMBER)
  </code></pre>
  
  <p>our query is this (<code>itemsProcessed</code> has got relevant indexes on it) - we're using a NOT IN to filter out items that have already been processed by the current processor or it's ancestors:</p>
  
  <pre><code>SELECT ... FROM items i WHERE &lt;additional queries on items&gt;
      AND id NOT IN (SELECT item FROM itemsProcessed WHERE processor IN (1, 2))
  </code></pre>
  
  <p>When the processed table gets very big, this query starts taking a long time (several seconds), as it has to do a lot of filtering before it starts returning the first item (the query plan is using a hash antijoin)</p>
  
  <p>We need this query to return the first few items very quickly - ideally return the first items <strong>under 500ms</strong>. This means it can't iterate over <code>items</code> and filter out those in <code>itemsProcessed</code>. So we need some way of doing a negation index on the join of <code>items</code> and <code>itemsProcessed</code> (we've accomplished this on mongo, but oracle doens't seem to be able to do a similar thing)</p>
  
  <p>Is this possible with Oracle?</p>