oracle select query - index on multiple columns
- by CC
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.
