how to optimize an oracle query that has to_char in where clause for date

Posted by panorama12 on Stack Overflow See other posts from Stack Overflow or by panorama12
Published on 2010-04-10T18:21:55Z Indexed on 2010/04/10 18:23 UTC
Read the original article Hit count: 241

Filed under:
|
|

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

© Stack Overflow or respective owner

Related posts about Oracle

Related posts about date