Hello.

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 <"toto"
and field2 IN (...)

UNION 
select * from A
where field1 >"toto2"
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 <'12/12/2010'

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 ?

Thanks alot.