title: oracle select query - index on multiple columns
body: <p>I'm working on a sql query, and trying to optimise it, because it takes too long to execute.</p>
  
  <p>I have a few select and UNION between.
  Every select is on the same table but with different condition in WHERE clause.
  Basically I have allways something like :</p>
  
  <pre><code>select * from A
  where field1 &lt;=TO_DATE ('01/01/2010', 'DD/MM/YYYY')
  AND field1 &gt;= TO_DATE(some date)
  and field2 IN (...)
  
  UNION 
  select * from A
  where field1 &lt;=TO_DATE ('01/01/2010', 'DD/MM/YYYY')
  AND field1 &gt;= TO_DATE(some date2)
  and field2 =(...)
  
  UNION
  ....
  </code></pre>
  
  <p>I have a index on field1 (it a date field, and field2 is a number).
  Now, when I do the select and if I put only </p>
  
  <pre><code>WHERE field1 &lt;TO_DATE ('01/01/2010', 'DD/MM/YYYY')
  </code></pre>
  
  <p>it does not use the index.
  I'm using Toad to see the explain plain and it said:</p>
  
  <pre><code>SELECT STAITEMENT Optimiser Mode = CHOOSE
  TABLE ACCESS FULL 
  </code></pre>
  
  <p>It is a huge table, and the index on this column is there.</p>
  
  <p>Any idea about this optimiser ? And why it does not uses the index ?</p>
  
  <p>Another question is , if I have where clause on field1 and field2 , I have to create only one index, or one index for each field ?</p>