I need some help in trying to work out why an index isn't being used. I hope someone here can help.
Table definition for ACT_TOTALS includes
STARTTIME TIMESTAMP(6)
There is an index on to_char(starttime, 'YYYY-MM-DD HH24')
There is a vew definition ACT_TOTALS_HOURLY2 which includes
select to_char(starttime, 'YYYY-MM-DD HH24') STARTTIMSE
..
...
from ACT_TOTALS
group by to_char(starttime, 'YYYY-MM'DD HH24'), TRANSTYPE
I have run a query
select starttime from ACT_TOTALS_HOURLY2
where starttime <= to_char(sysdate, 'YYYY-MM-DD HH24');
The table access comes out as full despite there being > 2 million rows in the table.
The predicate looks like
TO_CHAR(INTERNAL_FUNCTION(''STARTTIME''), 'YYYY-MM-DD HH24')<= ..........
I thinkg the INTERNAL_FUNCTION is an implicit cast from DATE to TIMESTAMP.
Can anyone please throw any light on why the index on the table wouldn't be used in this case?
Thanks in advance for any help.