Oracle Selection Query - Multiple Column Pointer

Question

I am working on a sql query and trying to optimize it because it takes too long.

I have several select and UNION between them. Each selection is in one table, but with a different condition in the WHERE clause. Basically, I always have 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 an index in field1 (this is a date field, and field2 is a number). Now, when I make a choice, and if I bet only

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

It does not use an index. I use Toad to see an explanation, and he said:

SELECT STAITEMENT Optimiser Mode = CHOOSE
TABLE ACCESS FULL 

This is a huge table, and there is an index in this column.

Any ideas on this optimizer? And why doesn't he use an index?

Another question: if I have a where clause in field1 and field2, do I need to create only one index or one index for each field?

Answer 1

OR ?

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

2 .

CREATE INDEX index_name
ON A (field1, field2);

Answer 2

. field1 , . ? , , . , ? , , FTS - .

Answer 3

? ?

,

field1 < '12/12/2010'

.

FULL TABLE SCAN .

: where field1 field2, ?

:

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

:

CREATE INDEX ix_a_2_1 ON A (field2, field1)

INLIST ITERATOR, INDEX RANGE SCAN .

Answer 4

• field1, TO_DATE. , WHERE field1 < TO_DATE('2010/12/12 12:00:00', 'yyyy/mm/dd hh24:mi:ss').

• ? b-tree, , , . , , .

  SELECT /*+ index(tbl.INDEX_NAME) */
tbl.*
FROM A tbl WHERE field1 < TO_DATE('2010/12/12 12:00:00','yyyy/mm/dd hh24:mi:ss');

• , , . , , , .

Answer 5

Oracle, , ' > ' '<' . , . , "" , .