CODEWITHSUNDEEP
sqloracle-databasedatesqldatatypessql-optimization
The AG
The AG

Reputation: 690

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.

Upvotes: 0

Views: 400

Answers (3)

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8665

Here a.date_key is a number

If it's really number, you can just simply use

where a.date_key between 19810101 and 19891231

Upvotes: 1

Popeye
Popeye

Reputation: 35930

As your number column is in YYYY-MM-DD format, You can directly use them as follows:

SELECT * FROM your_table
 WHERE
    A.DATE_KEY BETWEEN '&date3' AND '&date4'

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271181

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.

Upvotes: 2

Related Questions