title: How to join two tables based on a timestamp (with variance of a few seconds)?
body: <p>I have two tables that I'm trying to join and insert into another table based on a combination of three columns. I'll explain.</p>
  
  <p><strong>Table M</strong></p>
  
  <pre><code>| ANO      | BNO     |         Timestamp   |  Duration
  ---------------------------------------------------------------
  |  5612853 | 4732621 | 21.11.2013 09:50:58 | 196
  |  4842988 | 5610953 | 21.11.2013 17:34:58 | 98
  |  7765759 | 5612853 | 21.11.2013 20:48:00 | 377
  |  2470321 | 2470263 | 21.11.2013 21:47:18 | 125
  </code></pre>
  
  <p><strong>Table N</strong></p>
  
  <pre><code>| ANO      | BNO     |         Timestamp   |  Duration
  ---------------------------------------------------------------
  |  5612853 | 4732621 | 21.11.2013 09:50:52 | 196
  |  4842988 | 5610953 | 21.11.2013 17:34:53 | 98
  |  7765759 | 5612853 | 21.11.2013 20:47:55 | 377
  |  2470321 | 2470263 | 21.11.2013 21:47:13 | 125
  </code></pre>
  
  <p>Now these two tables have to be matched and inserted into <strong>Table MN</strong> based on</p>
  
  <pre><code>M.ANO=N.ANO and M.BNO=N.BNO and ((M.TIMESTAMP = N.TIMESTAMP+5/86400) or (M.TIMESTAMP = N.TIMESTAMP+6/86400))
  </code></pre>
  
  <p>So in theory, my output <strong>Table MN</strong> should be </p>
  
  <pre><code>| ANO      | BNO     |         Timestamp   |  Duration  || ANO      | BNO     |         Timestamp   |  Duration
  --------------------------------------------------------------------------------------------------------------
  |  5612853 | 4732621 | 21.11.2013 09:50:58 | 196        ||  5612853 | 4732621 | 21.11.2013 09:50:52 | 196
  |  4842988 | 5610953 | 21.11.2013 17:34:58 | 98         ||  4842988 | 5610953 | 21.11.2013 17:34:53 | 98
  |  7765759 | 5612853 | 21.11.2013 20:48:00 | 377        ||  7765759 | 5612853 | 21.11.2013 20:47:55 | 377
  |  2470321 | 2470263 | 21.11.2013 21:47:18 | 125        ||  2470321 | 2470263 | 21.11.2013 21:47:13 | 125
  </code></pre>
  
  <p><strong>Table M</strong> has about 1.4 million records, and <strong>Table N</strong> has about 0.9 million.</p>
  
  <p>I've tried to join the two tables based on the below two queries. But it takes hours to execute and that isn't feasible if I have to run this on a daily basis.</p>
  
  <pre><code>INSERT INTO MN_RECON (
  SELECT M.*,N.* FROM M FULL OUTER JOIN N ON
  M.ANO=N.ANO AND M.BNO=N.BNO AND 
  ((M.TIMESTAMP=N.TIMESTAMP+5/86400) OR (M.TIMESTAMP=N.TIMESTAMP+6/86400))
  
  INSERT INTO MN_RECON (
  SELECT M.*,N.* FROM M FULL OUTER JOIN N ON
  M.ANO=N.ANO AND M.BNO=N.BNO AND
  (M.TIMESTAMP-N.TIMESTAMP IN (5/86400,6/86400)
  </code></pre>
  
  <p>When I run just the SELECT statement of the above 2 queries I get an output within a minute (just a few 100 sample lines) but with the INSERT added it takes a very long time.
  Is there a way to optimize what I want to do?</p>
  
  <p>I need it to match on timestamp because there can be multiple occurrences of the same ANO - BNO combination during the day, with the timestamp being the unique identifier between them </p>
  
  <p>And I need a full outer join because I need to focus on records that are not matched, as well as matched records with a difference in duration between the two tables.</p>
  
  <p>Additional Oracle information <strong>Oracle Database 11g Enterprise Edition 11.2.0.3.0 64-bit Production</strong></p>
  
  <p><strong>EXPLAIN PLAN</strong></p>
  
  <pre><code>| Id  | Operation                | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
  ----------------------------------------------------------------------------------------------------
  |   0 | INSERT STATEMENT         |                 |  2386K|   530M|       |  2395M  (1)|999:59:59 |
  |   1 |  LOAD TABLE CONVENTIONAL | MN_RECON        |       |       |       |            |          |
  |   2 |   VIEW                   |                 |  2386K|   530M|       |  2395M  (1)|999:59:59 |
  |   3 |    UNION-ALL             |                 |       |       |       |            |          |
  |*  4 |     HASH JOIN RIGHT OUTER|                 |  1417K|   109M|    49M| 10143   (1)| 00:02:02 |
  |   5 |      TABLE ACCESS FULL   | N_VOICE         |   968K|    38M|       |  1753   (1)| 00:00:22 |
  |   6 |      TABLE ACCESS FULL   | M_VOICE         |  1417K|    52M|       |  2479   (1)| 00:00:30 |
  |*  7 |     FILTER               |                 |       |       |       |            |          |
  |   8 |      TABLE ACCESS FULL   | N_VOICE         |   968K|    38M|       |  1754   (1)| 00:00:22 |
  |*  9 |      TABLE ACCESS FULL   | M_VOICE         |     1 |    29 |       |  2479   (1)| 00:00:30 |
  </code></pre>