how to optimize an oracle query that has to_char in where clause for date
- by panorama12
I have a table that contains about 49403459 records.
I want to query the table on a date range. say 04/10/2010 to 04/10/2010. However, the dates are stored in the table as format 10-APR-10 10.15.06.000000 AM (time stamp).
As a result.
When I do:
SELECT bunch,of,stuff,create_date
FROM myTable
WHERE TO_CHAR (create_date,'MM/DD/YYYY)' >= '04/10/2010'
AND TO_CHAR (create_date, 'MM/DD/YYYY' <= '04/10/2010'
I get 529 rows but in 255.59 seconds! which is because I guess I am doing to_char on EACH record.
However, When I do
SELECT bunch,of,stuff,create_date
FROM myTable
WHERE create_date >= to_date('04/10/2010','MM/DD/YYYY')
AND create_date <= to_date('04/10/2010','MM/DD/YYYY')
then I get 0 results in 0.14 seconds.
How can I make this query fast and still get valid (529) results??
At this point I can not change indexes. Right now I think index is created on create_date column
