title: SQL Statement for Reconciliation
body: <p><strong>Given the schema below:</strong></p>
  
  <pre><code>create table TBL1 (ID varchar2(100) primary key not null, MATCH_CRITERIA varchar2(100));
  create table TBL2 (ID varchar2(100) primary key not null, MATCH_CRITERIA varchar2(100));
  create table TBL_RESULT (ID varchar2(100) primary key not null, TBL1_ID varchar2(100), TBL2_ID varchar2(100));
  
  create unique index UK_TBL_RESULT_TBL1_ID on TBL_RESULT(TBL1_ID);
  create unique index UK_TBL_RESULT_TBL2_ID on TBL_RESULT(TBL2_ID);
  
  create sequence SEQ_TBL_RESULT;
  
  insert into TBL1 VALUES('1', '1');
  insert into TBL1 VALUES('2', '1');
  insert into TBL1 VALUES('3', '1');
  
  insert into TBL2 VALUES('4', '1');
  insert into TBL2 VALUES('5', '1');
  insert into TBL2 VALUES('6', '1');
  </code></pre>
  
  <p>I need an SQL statement that will produce the result by reconciling equal MATCH_CRITERIA from TBL1 and TBL2.</p>
  
  <p>The following would work given that the <em>UNIQUE constraint is not present</em>. However, <em>we need the unique constraints</em> in our application.</p>
  
  <pre><code>insert into TBL_RESULT (ID, TBL1_ID, TBL2_ID)
  select SEQ_TBL_RESULT.nextval, TBL1.ID, TBL2.ID
  from TBl1, TBL2
  where TBL1.MATCH_CRITERIA = TBL2.MATCH_CRITERIA;
  </code></pre>
  
  <p><strong>Example output of TBL_RESULT</strong></p>
  
  <pre><code>| ID  |  TBL1_ID | TBL2_ID |
  | '1' |  '1'     |    '5'  |
  | '2' |  '2'     |    '4'  |
  | '3' |  '3'     |    '6'  |
  </code></pre>
  
  <p>Note: it doesn't matter if '1' is matched with '4' or '6'. As long as the MATCH_CRITERIA from both tables are equal and the resulting table has unique TBL1_ID and TBL2_ID.</p>
  
  <p>Note that we are inserting a million records from TBL1 and another million records from TBL2. Thus, sequential insert using PL/SQL is not acceptable unless it can run really fast (less than 15 minutes).</p>