Is there a benefit to using BETWEEN in a select statement for a previous months data vs using a comparison with TO_CHAR in Oracle?

user_7531768 0
Viewed 44

I'm modifying a query in Oracle, in which the data from the previous month is needed. At present, the query has a where clause with the following:

...
...
...
WHERE cust_dt BETWEEN TO_DATE('05-01-2022','mm-dd-yyyy) AND TO_DATE('05-31-2022', 'mm-dd-yyyy')

I am modifying the query so that the start date and end date do not need to be manually changed every month to run the query. After doing some research, I came up with the following:

...
...
...
WHERE TO_CHAR(cust_dt, 'MM-YYYY') = TO_CHAR(add_months(sysdate, -1), 'MM-YYYY')

The results I get back are as I want them, but I am curious as to which query will be better performance-wise given a larger set of data. All the posts I saw online used BETWEEN, so I was wondering if there was some reason for this.

I am a complete novice as far as tuning, testing, performance, etc. goes on queries. The actual query itself it fairly complex with several joins, so performance is important. At present, I only have a small amount of test data to work with, so I am limited in what all I can do to find the best result.

So to circle back to my question, which query would be best? The one that uses a BETWEEN, or the one that uses TO_CHAR?

oracle sql
2 Answers

I recommend to do not use one of your two queries. The first query has the problem that it's checking a hard coded date range. This is generally a risk that should be avoided when possible. The second query makes a not necessary conversion from date to char and this will likely be slow.

You can just use common date functions to get the data for the previous month. According to your description, your reference date is the current date, i.e. sysdate.

In order to rule out incorrect comparison because of the time, you can use the function TRUNC to remove the time from the date.

The function ADD_MONTHS can be used to go one month back.

The function LAST_DAY can be used to find the last day of the month.

Putting this together, you can use a where clause like this:

WHERE cust_dt BETWEEN ADD_MONTHS(TRUNC(sysdate,'mm'),-1) AND 
LAST_DAY(ADD_MONTHS(TRUNC(sysdate,'mm'),-1));

This will be executed fast and safe and avoids unnecessary conversions or hard coded dates. A last note: Consider to change the above query to...

WHERE cust_dt BETWEEN ADD_MONTHS(TRUNC(sysdate,'mm'),-1)
AND TRUNC(sysdate,'MM')-INTERVAL '0.001' SECOND;

...depending on whether you need the full last day of the previous month or not. Please see here the difference and try out: db<>fiddle

user_187948260 
WHERE TO_CHAR(cust_dt, 'MM-YYYY') = TO_CHAR(add_months(sysdate, -1), 'MM-YYYY')

Will not use an index on the cust_dt column; instead you would need to create a separate function-based index on TO_CHAR(cust_dt, 'MM-YYYY')

WHERE cust_dt BETWEEN TO_DATE('05-01-2022', 'mm-dd-yyyy')
                  AND TO_DATE('05-31-2022', 'mm-dd-yyyy')

Will use an index on the cust_dt column.

However, in Oracle, a DATE data-type consists of 7 bytes representing: century, year-of-century, month, day, hour, minute and second. It ALWAYS has those components (but often the client application you use to access the database will default to only showing the date component and not the time component - but the time component will still exist).

This means that your query will find values where cust_dt is between 2022-05-01 00:00:00 and 2022-05-31 00:00:00. It will not match values where cust_dt is between 2022-05-31 00:00:01 and 2022-05-31 23:59:59.

So to circle back to my question, which query would be best?

Neither.

You want (if you are hardcoding the dates):

WHERE cust_dt >= TO_DATE('05-01-2022', 'mm-dd-yyyy')
AND   cust_dt <  TO_DATE('06-01-2022', 'mm-dd-yyyy')

Or (if you are finding the date dynamically):

WHERE cust_dt >= ADD_MONTHS(TRUNC(SYSDATE, 'MM'), -1)
AND   cust_dt <  TRUNC(SYSDATE, 'MM')

Which will use an index on cust_dt and match the entire range for the month.

db<>fiddle here

user_15092640
Related