SQL Query optimization

[Jeremy Wilson]

I have the following query being run on my database

SELECT readingdatetime, max(decode(sensorid, 23008, sensorvalue))
"2853 JP5 XFER PMP MB-FE", max(decode(sensorid, 23009,
sensorvalue)) "2855 JP5 XFER PMP MB-CE/V", max(
decode(sensorid, 23010, sensorvalue))
"2857 JP5 XFER PMP MB-CE/A", max(decode(sensorid, 23011,
sensorvalue)) "2859 JP5 XFER PMP GB", max(decode(sensorid,
23012, sensorvalue)) "2861 JP5 XFER PMP PB-CE/V",
max(decode(sensorid, 23013, sensorvalue))
"2863 JP5 XFER PMP PB-FE/A"
FROM sensorreadings
WHERE readingdatetime >= '30-OCT-00'
AND readingdatetime <= '29-NOV-04'
GROUP BY readingdatetime

there is approximatly 8 million rows in the sensorreadings table. When viewing the plan it is doing a full table scan on sensorreadings. what index being applied would provide a full table scan. I believe this is from the greater than and less than where clause but I am not certain. Any help would be appreciated.

thanks,
jeremy

[Jeanne Boyarsky]

Jeremy,
Do you have a sorted index on readingdatetime? The key word is sorted. If the index isn't sorted, Oracle won't be able to use it.