How to optimize date transformation in Oracle SQL

Question

I have a date column CUR_DATE in NUMBER(10) (20210821) data type in DB and when I starting some executions with transforming I have a very tough issues with operation time.

for ex. I need to get data for the last 2 months from the current day, so I use transformation like this in my WHERE clause:

and trunc(to_date(CUR_DATE,'yyyymmdd'),'mm') >= add_months(trunc(sysdate,'mm'),-1)

and if I don't change date type of course it runs faster

and CUR_DATE >= 20220201

So maybe there will be solution which allows me execute data for last two months?

Answer 1

Do the opposite:

and cur_date >= to_number(to_char(add_months(trunc(sysdate, 'mm'), -1), 'yyyymmdd'))

if you must. Why? Because - I presume - there's an index on cur_date column. When you mess with it (apply various functions), index is no longer used. You could create a function-based index, though.

SQL> select to_number(to_char(add_months(trunc(sysdate, 'mm'), -1), 'yyyymmdd')) val from dual;

       VAL
----------
  20220201

SQL>

---

What you really should do is to store dates into DATE, not NUMBER datatype columns.