1 person found this helpful
DUPLICATE THREAD! WRONG FORUM!
You were already ask to post your question in the Sql Forum - and now you have posted it again in the Sql dev forum.
Please mark BOTH threads ANSWERED and repost in the Sql and Pl/Sql forum
When you repost you should
1. provide the CORRECT code - the code you posted is INVALID and won't compile so it can NOT be correct
2. post the execution plan - you said a full scan is being done but you didn't post the plan so people can see it
3. post the table and index ddl - Oracle can ONLY do a full scan if there are no indexes - since you didn't post ANY indexes or even say that an index exists no one can really help
4. tell people WHY you think a full scan is wrong - for all we know the table has 800 BILLION rows and ALL of them are for the SAME date - the date you ask for. So a full table scan is the ONLY thing Oracle will do?
BEFORE you post read the FAQ in that forum that discusses how to post a tuning request and what info you need to provide. As you can see from the above four items you haven't provided ANY of them.