title: Query optimization Not in to Left outer join
body: <p>Consider the following tables; </p>
  
  <h3>test</h3>
  
  <pre><code>SQL&gt; desc test;
   Name                                      Null?    Type
   ----------------------------------------- -------- ----------------------------
   NUM                                       NOT NULL NUMBER
   NUM2                                               NUMBER(10)
   NUM3                                               NUMBER
  
  
         NUM       NUM2       NUM3
  ---------- ---------- ----------
           1          1          1
           2          2          2
  </code></pre>
  
  <h3>test2</h3>
  
  <pre><code>SQL&gt; desc test2;
   Name                                      Null?    Type
   ----------------------------------------- -------- ----------------------------
   NUM                                       NOT NULL NUMBER
   NUM2                                      NOT NULL NUMBER
   NUM3                                               NUMBER
  
  
         NUM       NUM2       NUM3
  ---------- ---------- ----------
           1          1          1
           3          1          1
  </code></pre>
  
  <p>As per the book , if one were to replace a NOT IN condition to a LEFT Outer join ; the query performance would increase.</p>
  
  <p>Explain plan 1</p>
  
  <pre><code>SQL&gt; select * 
         from test 
        where num NOT IN (select num 
                            from test2);
  
  NUM      NUM2       NUM3
  --- --------- ----------
  2          2          2
  </code></pre>
  
  <p>Execution Plan</p>
  
  <pre><code>----------------------------------------------------------
  Plan hash value: 856752680
  
  ----------------------------------------------------------------------------
  | Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
  ----------------------------------------------------------------------------
  |   0 | SELECT STATEMENT   |       |     1 |     2 |     5   (0)| 00:00:01 |
  |*  1 |  FILTER            |       |       |       |            |          |
  |   2 |   TABLE ACCESS FULL| TEST  |     2 |     4 |     3   (0)| 00:00:01 |
  |*  3 |   TABLE ACCESS FULL| TEST2 |     2 |    26 |     2   (0)| 00:00:01 |
  ----------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
     1 - filter( NOT EXISTS (SELECT /*+ */ 0 FROM "TEST2" "TEST2" WHERE
                LNNVL("NUM"&lt;&gt;:B1)))
     3 - filter(LNNVL("NUM"&lt;&gt;:B1))
  </code></pre>
  
  <p>So I created indexes on num columns of both tables and re-wrote the query as :</p>
  
  <p>Explain plan 2</p>
  
  <pre><code>SQL&gt; select * 
         from test 
    left join test2 on (test.num = test2.num) 
        where test2.num is null;
  
         NUM       NUM2       NUM3        NUM       NUM2       NUM3
  ---------- ---------- ---------- ---------- ---------- ----------
           2          2          2
  
  
  Execution Plan
  ----------------------------------------------------------
  Plan hash value: 1525288557
  
  --------------------------------------------------------------------------------
  | Id  | Operation                     | Name  | Rows  | Bytes | Cost (%CPU)| Tim
  --------------------------------------------------------------------------------
  |   0 | SELECT STATEMENT              |       |     2 |    82 |     4   (0)| 00:
  |*  1 |  FILTER                       |       |       |       |            |
  |   2 |   NESTED LOOPS OUTER          |       |     2 |    82 |     4   (0)| 00:
  |   3 |    TABLE ACCESS FULL          | TEST  |     2 |     4 |     3   (0)| 00:
  |   4 |    TABLE ACCESS BY INDEX ROWID| TEST2 |     1 |    39 |     1   (0)| 00:
  |*  5 |     INDEX RANGE SCAN          | ID2   |     1 |       |     0   (0)| 00:
  --------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
     1 - filter("TEST2"."NUM" IS NULL)
     5 - access("TEST"."NUM"="TEST2"."NUM"(+))
  </code></pre>
  
  <p>I am clearly missing something as the suggested method is proving to be more expensive. Is this due to my data distribution?</p>
  
  <p>Ver: Oracle 10g</p>