title: Keeping date intervals from overlapping and consistent with trigger in Oracle +11g
body: <p>Assume that I have the following:</p>
  
  <pre><code>    CREATE TABLE test (
      id NUMBER(10)
      , valid_from DATE
      , valid_to DATE,
      PRIMARY KEY (id, valid_from)
      );
  
      INSERT INTO test (id, valid_from) VALUES (1, '01/JAN/1900');
      INSERT INTO test (id, valid_from) VALUES (1, '01/JAN/1901');
      INSERT INTO test (id, valid_from) VALUES (1, '01/JAN/1902');
      INSERT INTO test (id, valid_from) VALUES (2, '01/JAN/1903');
  </code></pre>
  
  <p>Output:</p>
  
  <pre><code>            ID VALID_FROM VALID_TO 
      ---------- ---------- ---------
               1 01-JAN-01           
               1 01-JAN-02           
               2 01-JAN-03           
               1 01-JAN-00      
  </code></pre>
  
  <p>Now I need a trigger that will keep VALID_TO field consistent with VALID_FROM like this:</p>
  
  <pre><code>            ID VALID_FROM VALID_TO 
      ---------- ---------- ---------
               1 01-JAN-00  01-JAN-01
               1 01-JAN-01  01-JAN-02
               1 01-JAN-02           
               2 01-JAN-03 
  </code></pre>
  
  <p>I have a query that calculates the VALID_TO and checks whether there are any records that need to be updated:</p>
  
  <pre><code>    WITH original AS (        
          SELECT id,
                 valid_from,
                 valid_to, 
                 ROW_NUMBER() OVER (PARTITION BY id ORDER BY valid_from DESC) seq  
          FROM test
      ), should_be AS (
          SELECT df.id,
                 df.valid_from AS VALID_FROM, 
                 dt.valid_from AS VALID_TO
          FROM original df
          LEFT OUTER JOIN original dt ON (df.id = dt.id 
                                          AND df.seq = dt.seq + 1)
      ), update_req AS (
          SELECT
              should_be.*, 
              CASE WHEN original.VALID_TO = should_be.VALID_TO OR (original.VALID_TO IS NULL AND should_be.VALID_TO IS NULL) THEN 'N' ELSE 'Y' END UPDATE_REQUIRED        
          FROM should_be 
          INNER JOIN original ON (should_be.id = original.id AND should_be.valid_from = original.valid_from)
      )
      SELECT * 
      FROM update_req
      ORDER BY id, valid_from
  </code></pre>
  
  <p>Output:</p>
  
  <pre><code>            ID VALID_FROM VALID_TO  UPDATE_REQUIRED
      ---------- ---------- --------- ---------------
               1 01-JAN-00  01-JAN-01 Y              
               1 01-JAN-01  01-JAN-02 Y              
               1 01-JAN-02            N              
               2 01-JAN-03            N        
  </code></pre>
  
  <p>I use this query in a trigger that makes sure the VALID_TO field is updated if it is wrong:</p>
  
  <pre><code>    CREATE OR REPLACE TYPE ID_COLLECTION_T AS TABLE OF NUMBER(10);
  
      CREATE OR REPLACE TRIGGER trg_test
      FOR DELETE OR INSERT OR UPDATE
      ON test REFERENCING NEW AS NEW OLD AS OLD
      COMPOUND TRIGGER
  
          l_changed_ids ID_COLLECTION_T := ID_COLLECTION_T(); -- initialize
  
          AFTER EACH ROW IS
          BEGIN
              -- Keep track of changed ids
              CASE
                  WHEN INSERTING OR UPDATING THEN l_changed_ids.extend; l_changed_ids(l_changed_ids.last) := :NEW.id;
                  WHEN DELETING OR UPDATING THEN l_changed_ids.extend; l_changed_ids(l_changed_ids.last) := :OLD.id; 
              END CASE;
  
          END
          AFTER EACH ROW;
  
          AFTER STATEMENT IS
  
              l_existing_inconsistencies VARCHAR2(1);
  
          BEGIN
  
              -- first we check whether the executed statement caused any VALID_TO inconsistencies
              WITH original AS (        
                  SELECT id,
                         valid_from,
                         valid_to, 
                         ROW_NUMBER() OVER (PARTITION BY id ORDER BY valid_from DESC) seq  
                  FROM test
              ), should_be AS (
                  SELECT df.id,
                         df.valid_from AS VALID_FROM, 
                         dt.valid_from AS VALID_TO
                  FROM original df
                  LEFT OUTER JOIN original dt ON (df.id = dt.id 
                                                  AND df.seq = dt.seq + 1)
              ), update_req AS (
                  SELECT
                      should_be.*, 
                      CASE WHEN original.VALID_TO = should_be.VALID_TO OR (original.VALID_TO IS NULL AND should_be.VALID_TO IS NULL) THEN 'N' ELSE 'Y' END UPDATE_REQUIRED        
                  FROM should_be 
                  INNER JOIN original ON (should_be.id = original.id AND should_be.valid_from = original.valid_from)
                  WHERE original.id MEMBER OF l_changed_ids -- we ONLY (!) want to search for inconsistencies for modified ids
              )
              SELECT CASE WHEN 'Y' IN (SELECT UPDATE_REQUIRED FROM update_req) THEN 'Y' ELSE 'N' END
              INTO l_existing_inconsistencies
              FROM DUAL;
  
             -- If there are inconsistencies, then we update the table.
             IF l_existing_inconsistencies = 'Y' THEN 
  
                  MERGE INTO test o
                  USING (
                          WITH original AS (        
                              SELECT id,
                                     valid_from,
                                     valid_to, 
                                     ROW_NUMBER() OVER (PARTITION BY id ORDER BY valid_from DESC) seq  
                              FROM test
                          ), should_be AS (
                              SELECT df.id,
                                     df.valid_from AS VALID_FROM, 
                                     dt.valid_from AS VALID_TO
                              FROM original df
                              LEFT OUTER JOIN original dt ON (df.id = dt.id 
                                                              AND df.seq = dt.seq + 1)
                          )
                          SELECT
                                  should_be.*, 
                                  CASE WHEN original.VALID_TO = should_be.VALID_TO OR (original.VALID_TO IS NULL AND should_be.VALID_TO IS NULL) THEN 'N' ELSE 'Y' END UPDATE_REQUIRED        
                          FROM should_be 
                          INNER JOIN original ON (should_be.id = original.id AND should_be.valid_from = original.valid_from)
                          WHERE original.id MEMBER OF l_changed_ids -- we ONLY (!) want to search for inconsistencies for modified ids
                  ) n
                  ON (o.id = n.id AND o.valid_from = n.valid_from AND n.UPDATE_REQUIRED = 'Y')
                  WHEN MATCHED THEN UPDATE SET o.valid_to = n.valid_to;
  
              END IF;
  
          END
          AFTER STATEMENT;
  
      END trg_test;
  </code></pre>
  
  <p>Now the trigger keeps the data consistent for inserted/updated/deleted ids:</p>
  
  <pre><code>    INSERT INTO test (id, valid_from) VALUES (1, '01/JAN/1899');
  </code></pre>
  
  <p>Now we find in the test table the following:</p>
  
  <pre><code>            ID VALID_FROM VALID_TO 
      ---------- ---------- ---------
               1 01-JAN-99  01-JAN-00
               1 01-JAN-00  01-JAN-01
               1 01-JAN-01  01-JAN-02
               1 01-JAN-02           
               2 01-JAN-03       
  </code></pre>
  
  <p><strong>The problem here is the MEMBER OF statement. It is causing full table scans for each of it's members</strong>. 
  In case of multi-update/insert statements, there are many potential ids changed, so the l_changed_ids collection is large.
  I can not optimize the MEMBER OF:
  <a href="http://www.puthranv.com/search/label/Oracle%20Dynamic%20IN%20List" rel="nofollow">http://www.puthranv.com/search/label/Oracle%20Dynamic%20IN%20List</a>
  <a href="http://www.oracle-developer.net/display.php?id=301" rel="nofollow">http://www.oracle-developer.net/display.php?id=301</a></p>
  
  <p>I have tried:</p>
  
  <ol>
  <li>Casting the collection with TABLE() is quite slow for many single inserts/updates.</li>
  <li>Updating inconsistent rows from within a loop is a bad idea because for n-bulk statements this could potentially reactivate the trigger n times recursively. But (!) using a loop is very fast because the index on id is used every time. </li>
  </ol>
  
  <p><strong>My questions are:</strong></p>
  
  <ol>
  <li>Is there an other trigger-approach for this. </li>
  <li>The trigger must work for single and bulk update/delete/insert statements. So if there are inconsistencies, there might only one extra update statement be executed recursively. </li>
  <li>The trigger locks the rows based on id. (This is a later requirement but might be interesting to think about it now.)</li>
  </ol>
  
  <p><strong>UPDATE1: Some performance analysis on calculating the VALIT_TO date:</strong></p>
  
  <pre><code>    -- Original query on 5mil records:  40 sec
      WITH original AS (        
          SELECT id,
                 valid_from,
                 valid_to, 
                 ROW_NUMBER() OVER (PARTITION BY id ORDER BY valid_from DESC) seq  
          FROM test
      ), should_be AS (
          SELECT df.id,
                 df.valid_from AS VALID_FROM, 
                 dt.valid_from AS VALID_TO
          FROM original df
          LEFT OUTER JOIN original dt ON (df.id = dt.id 
                                          AND df.seq = dt.seq + 1)
                                         ) select * from should_be
  
  
      -- TommCatt suggestion on 5mil records:  65 sec
      with Date_List as (
        select  t1.ID, t1.Valid_from as From_Date, Min( t2.Valid_from ) as To_Date 
        from    test t1
        left join test t2
          on    t2.id = t1.id
          and   t2.valid_from &gt; t1.valid_from
        group by t1.ID, t1.Valid_from
      )
      select  id, from_date, to_date
      from    Date_List     
  
  
      -- TommCatt suggestion on 5mil records for 12c: untested
  
  
      -- a_horse_with_no_name suggestion on 5mil records:  10 sec WINNER!!
      SELECT id,
             valid_from,
             LEAD(valid_from, 1) OVER (PARTITION BY id ORDER BY valid_from ASC) valid_to
      FROM test
      -- EXEC Plan for the winner:
      ---------------------------------------------------------------------------------
      | Id  | Operation        | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
      ---------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT |              |  5106K|    63M| 22222   (1)| 00:04:27 |
      |   1 |  WINDOW BUFFER   |              |  5106K|    63M| 22222   (1)| 00:04:27 |
      |   2 |   INDEX FULL SCAN| SYS_C0011495 |  5106K|    63M| 22222   (1)| 00:04:27 |
      ---------------------------------------------------------------------------------                      
  </code></pre>