oracle select query - index on multiple columns

Question

I'm working on a sql query, and trying to optimise it, because it takes too long to execute.

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 :

select * from A
where field1 <=TO_DATE ('01/01/2010', 'DD/MM/YYYY')
AND field1 >= TO_DATE(some date)
and field2 IN (...)

UNION 
select * from A
where field1 <=TO_DATE ('01/01/2010', 'DD/MM/YYYY')
AND field1 >= TO_DATE(some date2)
and field2 =(...)

UNION
....

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

WHERE field1 <TO_DATE ('01/01/2010', 'DD/MM/YYYY')

it does not use the index. I'm using Toad to see the explain plain and it said:

SELECT STAITEMENT Optimiser Mode = CHOOSE
TABLE ACCESS FULL 

It is a huge table, and the index on this column is there.

Any idea about this optimiser ? And why it does not uses the index ?

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 ?

Solution

Wouldn't you be better off without the Union and using OR's between your different clauses instead?

select * from A
where (
  field1 <"toto"
  and field2 IN (...)
)
OR
(
  field1 >"toto2"
  and field2 IN (...)
)
OR
....

It is also possible to have an index on 2 columns.

CREATE INDEX index_name
ON A (field1, field2);