title: Index range scan vs index skip scan vs index fast full scan
body: <p>I have table </p>
  
  <pre><code>test_A(
      id1 number,
      id2 number,
      id3 number,
      name varchar2(10),
      create_dt date
  )
  </code></pre>
  
  <p>I have two indexes one composite index <code>indx1</code> on <code>(id1,id2)</code> and  <code>indx2(id3)</code>. Now when I query this table <code>test_A</code> as</p>
  
  <pre class="lang-sql prettyprint-override"><code>select * from test_A where id2=123 and 
  create_dt=(select max(create_dt) from test_A where test_A.id2=id2);
  </code></pre>
  
  <p>I ran explain plan for this above SQL and it is using "index skip scan". If I create another index on <code>create_dt</code> then it using index fast full scan and over all cost and %cpu is showing higher than plan with Index skip scan. It is also using Index range scan after creating index on <code>create_dt</code>. </p>
  
  <p>I could not come to conclusion which should be could? Do I need to create another Index on <code>create_dt</code> or is Index skip scan good? I believe Index skip is a feature of Oracle to run multiple index range scan? </p>