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')

Solution 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.

Solution 2

Of course this doesn't work:

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')

Because that would only return rows where the create_date is 4/10/2010 12:00 AM exactly!

If you want to get all rows where create_date occurs any time on the day of 4/10/2010, use this:

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/11/2010 00:00:00.000000','MM/DD/YYYY HH24:MI:SS.FF')

OR if you prefer:

WHERE create_date BETWEEN to_timestamp('04/10/2010 00:00:00.000000','MM/DD/YYYY HH24:MI:SS.FF')
                      AND to_timestamp('04/10/2010 23:59:59.999999','MM/DD/YYYY HH24:MI:SS.FF')

By the way, when you want to represent midnight, you can leave all the other parts out. So you could just say:

WHERE            
    create_date >= to_timestamp('04/10/2010','MM/DD/YYYY')
AND
    create_date < to_timestamp('04/11/2010','MM/DD/YYYY')

Solution 3

In your first query, you are doing a character comparison rather than a date comparison, which should not be producing correct results.

For example, using your logic, 01/02/2009 will be greater than 01/01/2010 because the day component '02' is greater than the day component '01' when comparing characters and the year will never be evaluated.

Solution 4

This works:

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') 

Solution 5

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/11/2010','MM/DD/YYYY')

The date 04/10/2010 includes all the date values from midnight on the 10th until 11:59:59 PM, so getting everything less than the 11th will cover all the bases. An alternative is to ensure data in myTable has the CREATE_DATE field truncated on data entry; I prefer to do that for DATE fields, and if I care about the time components, I use TIMESTAMPs.

View more solutions

Comments

[Josh]

thanks. I tried that but still i dont get any results. please see my edit

[mikew]

I edited my answer. I think your second query is probably correct. The first query is giving you incorrect results.

[Jeffrey Kemp]

@panorama12, read ElectricDialect's answer again - you're not getting it. The comparison logic you're using is wrong.