Index on date-column isn't being used

[Pejo]

I have a pretty big table that among many other columns has a date-column that I have indexed. When I try to run a select-statement that selects records within a date-range the index isn't used and a full table scan is made instead, which means that the select takes very long time to run. The select looks something like this (simplified):

select name, age, time
from table_a
where time between to_date('2002-02-25 08:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('2002-02-25 17:00:00','yyyy-mm-dd hh24:mi:ss')

Does anydody know why the index on column "time" isn´t used? Can't date-columns be used in indexes or what?

/PeJo

 

[spenglerr]

Hello Pejo,

there could be some reasons for it:

1.) The optimizer recognises that loading the time intervall is nearly loading the whole table, so a quick full table scan is cheaper then an index-scan followed by reading the table's data.

2.) Your last analyze is too long ago and therefore the statistics lead to argument 1) even though this isn't true
ANALYZE TABLE table_a COMPUTE/ESTIMATE STATISTIC
will help.

3.) Since you are using the TO_DATE-Function Oracle will not use the INDEX. If this is true try to create a function-based index:
CREATE INDEX xxx ON table_a(TO_CHAR(time,'yyyy-mm-dd hh24:mi'))
and rewrite the statement TO_CHAR(time,'yyyy-mm-dd hh24:mi') between '2002-02-25 08:00' and '2002-02-25 17:00'

hope this is helpful

spenglerr

 

[Pejo]

Thanks for your reply.

Unfortunatly your suggestions isn't helping much.

1) This can't be it as the table has 31 columns and it must be faster to use an index than to load the whole table into memory...

2) I have analyzed the table but the result is still the same.

3) I'm not really using to_date but a date-variable (the select is really in a PL/SQL procedure and inparameters in_from_time and in_to_time is date-columns and used in the select).

The index is used if I run
Select name, age, time
from table_a
where time = to_date('2002-02-25 08:00:00','yyyy-mm-dd hh24:mi:ss')
but it's when I use BETWEEN two dates that the index isn't used.

Should I use another type of index than an "ordinary index", such as bitmap index?

Any more suggestions?

/Pejo

 

[spenglerr]

Hello Pejo,

I wouldn't throw explanation 1 away so easy since the number of columns does never affect the choice if a full table scan is done or not. It is the number of expected rows to fetch that will give the hint if the optimizer uses a full or a index scan.

 

[Pejo]

Ok. So how will I know if this is the reason why the index isn't used?
And, more important, what can I do to avoid it?

/PeJo

 

[spenglerr]

Try your statement using sql*plus with:

First run the script UTLXPLAN.SQL in %ORACLE_HOME%/rdbms/admin-directory

Then use
SET AUTOTRACE ON
before a statement then you will get the execution plans stating if the Index is used or not.

You can try the SQL-Statement using HINTS to force the use of the Index.

At meantime I had a second possibility why your index is not used:
You should have the index matching the where-conditions. This means that the Index may not be used due to a combination of equality-comparisons not indexed:

select * from table_a where colA=... and colB=... and colC=... and colD between ....;

If you have the database not QUERY REWRITE-enabled then it may be that the wrong order in the Where-clause causes the ignoration of the index.

 

[Guest_imported]

Does an OR clause help?

 

[HenryMonster]

Pejo,

Post the exact pl/sql code that does not work (I mean a snippet of the code containing the statement).

 

[Pejo]

It actually looks something like the select-statement that I posted first. There are of course other tables and statements that I didn't write but they don't make a difference (when it comes to the problem with the index).
If I remove all those other tables and statements the explain plan still looks the same (the part about the time-column index not being used). This is my problem. I would like to know WHY the index isn't used. Is it because it's a date-time column and too many different values is represented and therefore the index isn't used or what?

/Pejo

 

[rtefft]

Something simple to try, that has helped me with similar problems. Instead of using

  BETWEEN X and Y

try using

  >= X and <= Y

instead. Sounds silly, but it has helped indexes work with dates more often than BETWEEN has for me.

Good luck! ____________________________
Rich Tefft
PL/SQL Programmer

 

[Pejo]

Thanks for the suggestion, but I have tried that already... Still doesn't use the index.

/Pejo

 

[daddymack]

If the table is partitioned on the "time" field, then your select statement would do a full scan of the partition

If your table is not partitioned on time, try doing the following select statement..

select name, age, time
from table_a
where time = to_date('2002-02-25 08:00:00','yyyy-mm-dd hh24:mi:ss')

If the above is still doing a full table scan, try using a hint

select /*+ index(a) */
name, age, time
from table_a a
where time = to_date('2002-02-25 08:00:00','yyyy-mm-dd hh24:mi:ss')