title: Composite index is ignored when selecting unindexed columns (Oracle)
body: <p>We have a simple <strong>join</strong> statement in which some of the <strong>when</strong> clauses may turn into <strong>is null</strong>. The statement is generated by an application.
  A problem with a query plan arises when we have this <strong>is null</strong> constraint. <br/> We followed the approach described in the article on StackExchange and created a composite index for columns - nullable and the one we join on. It helps only if we select only indexed columns. If we select unindexed columns it is ignored, while the query result is the same - e.g., no rows selected. <br/><br/>
  The only option we see - to change the logic of the application, but may be there is still a way to solve this on db-level?</p>
  
  <pre><code>--Illustrative sample. Prepare tables and indexes:
  create table tableA 
   (
    Acol1 varchar2(32) NOT NULL,
    Acol2 varchar2(32),
    Acol3 varchar2(32)
   );
  insert into tableA (Acol1, Acol2, Acol3)
              values ('abcd1','abcd2A','abcd3A'); 
  
  create table tableB 
   (
    Bcol1 varchar2(32) NOT NULL, 
    Bcol2 varchar2 (32),
    Bcol3 varchar2 (32)
   ); 
  insert into tableB (Bcol1, Bcol2, Bcol3) 
  values             ('abcd1','abcd2B','abcd3B'); 
  
  create index tableA_col12 on tableA (acol1, acol2); 
  create index tableB_col1 on tableB (Bcol1); 
  commit;
  </code></pre>
  
  <p>Then we check the plans:</p>
  
  <p>1.</p>
  
  <pre><code> select a.Acol1 from tableA a join tableB b on a.Acol1 = b.Bcol1 where Acol2 is null; 
   --no rows selected
  </code></pre>
  
  <p><a href="http://i.imgur.com/At2wWwe.png" rel="nofollow">Plan1</a> - Range scan</p>
  
  <p>2.</p>
  
  <pre><code> select * from tableA a join tableB b on a.Acol1 = b.Bcol1 where Acol2 is null;
  --no rows selected
  </code></pre>
  
  <p><strong>Plan2</strong> (same link above) - Full table scan</p>
  
  <p>What would be the best way to improve performance: change the queries, use smarter indexes or by applying fixed plan?</p>
  
  <p>*Update* While I was preparing this question, the plan for my sample changed by itself, now we have <a href="http://i.imgur.com/DbSZrli.png" rel="nofollow">Plan2*</a> instead of Plan2 - no Full table scan. However, if I recreate the sample (drop tables and prepare them again) - the plan is Plan2 again (Full table scan). This trick does not happen in actual DB.</p>