oracle select query - index on multiple columns

Question

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 <=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 ?

Thanks alot.

Answer 1

You can create one index on both columns. But your question treats field1 like a string above and then like a date below. Is that intentional? Also, the optimizer chooses based on what it thinks. So are your stats up to date? If not, it might think that a FTS is the best way to find the records you want.

Answer 2

I'm not the biggest Oracle expert, but I imagine that you cannot get the advantage of an index when using '>' or '<' operators. Indexing provides a quick way to look up specific values, not a range of values. So when you look for values "less than" a specific value, the index does nothing.

Answer 3

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

Most probably because this condition

field1 < '12/12/2010'

returns all or almost all rows.

In this case, FULL TABLE SCAN is better.

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 ?

For this query:

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

, you need to create a composite index:

CREATE INDEX ix_a_2_1 ON A (field2, field1)

This will use an INLIST ITERATOR to split the query into a number of continuous ranges and use the INDEX RANGE SCAN to return the values from each range.

Answer 4

• Since you're dealing with dates for field1, you want to use TO_DATE. For example, WHERE field1 < TO_DATE('2010/12/12 12:00:00', 'yyyy/mm/dd hh24:mi:ss').

• What type of index does the field have? I'm assuming a b-tree index, in which case the less-than operator is OK, but the optimiser may decide not to use it depending on the current table statistics. If you want to force the index to be used, you can specify it in a hint and see how that affects the performance.

  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');

• As for your final question, you can create an index on each column individually, or a composite index using both columns. If you're always going to be selecting based on values from both columns, then a composite index over both of those columns would probably be better.

Answer 5

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);