How to optimize an Oracle query that has to_char in where clause for date

Question

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.

How can I convert the two date ranges so that first date range gets converted to time stamp with all 0's and the second one gets converted to time stamp that is the last time stamp of the date. If that makes sense...?

The following where clause fetches no results either:

WHERE            
create_date >= to_timestamp('04/10/2010 00:00:00.000000','MM/DD/YYYY HH24:MI:SS.FF')
AND
create_date <= to_timestamp('04/10/2010 00:00:00.000000','MM/DD/YYYY HH24:MI:SS.FF')

Answer 1

I get 529 rows but in 255.59 seconds! Which is because I guess I am doing TO_CHAR on EACH record.

If you were to generate an execution plan for your first query ...

explain plan for 
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'
/

... you would see that it does a full table scan. That's because the to_char() prevents the use of your index on CREATE DATE.

You don't say how long it took to return the results when you ran ...

SELECT bunch,of,stuff,create_date
FROM myTable
WHERE             
create_date >= to_timestamp('04/10/2010 00:00:00.000000','MM/DD/YYYY HH24:MI:SS.FF') 
AND 
create_date <= to_timestamp('04/10/2010 23:59:59:123000','MM/DD/YYYY HH24:MI:SS.FF')
/

... but I expect it was way closer to 0.14 seconds than 4 minutes.