I have the following select statement:
select distinct trans_id, 241
where schedule_date >= sysdate-10/1440
and schedule_date <= sysdate+1/24
The table trans has an index on schedule date, but it doesn't use the index.
I am sure it won't use the index due to the calulation after sysdate, am I right? Or should it be using the index?Thanks.
No, you are wrong. Calculation is not made on the indexed column, so nothing is preventing the usage of the index. But I guess optimizer is choosing not to use index for other reasons.
Note that if you would rewrite your condition to the following, this would indeed prevent the usage of the index:
where schedule_date+10/1440 >= sysdate
and schedule_date-1/24 <= sysdate
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Click Here to Expand Forum to Full Width