title: How can I efficiently query a table with revisioned values?
body: <p>I need to store a table of task items where each item has a unique identifier. Tasks can arrive multiple times, the identifier is therefore not a primary key. I do however only care about the latest version of a task which I identify using a sequence. Every instance of a task can be <em>NEW</em> or <em>DONE</em>. The tables look somewhat like this:</p>
  
  <pre><code>CREATE SEQUENCE TASKSEQ;
  
  CREATE TABLE TASKS (
    ID VARCHAR2(100),
    STATE VARCHAR2(50),
    SEQ NUMBER(20)
  );
  </code></pre>
  
  <p>As a data simulation, consider that the table contains a million complete tasks but that a new batch of previously existing tasks arrived right after setting the state to <code>NEW</code>.</p>
  
  <pre><code>BEGIN
    FOR IDX IN 1..1000000
      LOOP
        INSERT INTO TASKS (ID, STATE, SEQ)
        VALUES (IDX, 'DONE', TASKSEQ.NEXTVAL);
      END LOOP;
    FOR IDX IN 900001..1000000
      LOOP
        INSERT INTO TASKS (ID, STATE, SEQ)
        VALUES (IDX, 'NEW', TASKSEQ.NEXTVAL);
      END LOOP;
  END;
  </code></pre>
  
  <p>I am now trying to select tasks that are marked as <code>NEW</code> in their latest revision. I do not really care about the order in which I process these tasks, just about the fact that those tasks are marked <code>NEW</code> in their individual latest revision. I would like to read "old" tasks first to avoid live-locks. I am fetching blocks of tasks of a given batch size.</p>
  
  <p>The select statement looks something like this:</p>
  
  <pre><code>SELECT L.ID, L.SEQ
  FROM TASKS L
  INNER JOIN (
    SELECT ID, MAX(SEQ) MAXSEQ
    FROM TASKS
    GROUP BY ID
  ) R
  ON L.ID = R.ID
  AND L.SEQ = R.MAXSEQ
  WHERE L.STATE = 'NEW'
  ORDER BY L.SEQ
  FETCH FIRST 100 ROWS ONLY;
  </code></pre>
  
  <p>Once the tasks arrive in the application, they are processed and updated in the database via:</p>
  
  <pre><code>UPDATE TASKS
  SET STATE = 'DONE'
  WHERE ID = ? 
  AND SEQ = ?;
  </code></pre>
  
  <p>Once this update is complete, the next batch of tasks is polled. There might have been parallel writes to the table while processing tasks but other then the statements above, no task is ever deleted from the table.</p>
  
  <p>The data in the table would for example be:</p>
  
  <pre><code>ID|STATE|SEQ
  A |NEW  |1
  A |DONE |2
  B |DONE |3
  B |NEW  |4
  C |NEW  |5
  C |NEW  |6
  </code></pre>
  
  <p>In this case, I would expect that a polling would contain (B,4) and (C,6) but not A. After updating these tuple states to <em>DONE</em>, I would expect that the subsequent poll would not contain any data unless more data was inserted in the table.</p>
  
  <p>I am wondering if this table design can be implemented efficiently with an index and how this index would look like. A simple index such as</p>
  
  <pre><code>CREATE UNIQUE INDEX NEW_TASK_INDEX ON TASKS (ID, SEQ, STATE);
  </code></pre>
  
  <p>does not do the trick do to the ordering constraint and I wonder how I could change or add an index to accomplish my goal. I also wonder if a materialized view would be a better option to define an index onto it.</p>
  
  <hr>
  
  <p>Update: As for the suggested solutions, here are the query plans for executing the statements, when adding</p>
  
  <pre><code>CREATE UNIQUE INDEX tasks_idx1 ON tasks (ID ASC, SEQ DESC);
  CREATE UNIQUE INDEX tasks_idx2 ON tasks (STATE, SEQ); 
  </code></pre>
  
  <p>I get the following plan:</p>
  
  <p><a href="https://i.stack.imgur.com/8gdjz.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/8gdjz.png" alt="Query plan first suggestion"></a></p>
  
  <p>For the altered select statement, I get the following plan which seems more efficient but runs quite a bit slower then the above selection:</p>
  
  <p><a href="https://i.stack.imgur.com/KYM4D.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/KYM4D.png" alt="enter image description here"></a></p>