sqloracledatesqldatatypessql-optimization

Slow performance with date comparison with number datatype

Select * from table
WHERE to_date(a.date_key, 'YYYY-MM-DD') BETWEEN to_date('&date3', 'YYYY-MM-DD') AND to_date('&date4', 'YYYY-MM-DD');

Here a.date_key is a number and I am changing the format. But applying the above process makes it slow performance wise.

Is it right way to do it or we can make it better in a different way.

Any help would be appreciated.

Solution

  • This is not the best way. Assuming date_key is a string of the form YYYY-MM-DD, then do the comparisons as strings:

    WHERE a.date_key BETWEEN '&date3' AND '&date4'

    The comparison will be accurate and Oracle can use indexes, partitions, and statistics on date_key.

    This begs the question why you have a key at all instead of using a date data type. But that is another issue.