delete records older than 24 hours in oracle SQL

Question

I want to delete all the records which is older than 24 hours. I am using the below query for the same but sometimes doesn't run perfectly. I want to know whether my query is right or wrong ? Or which is better way to do so.

delete from TEMP_SERVICE_OPTION where EVENT_DATE < TRUNC(SYSDATE) - 1;

Answer 1

If you want older than 24 hours then do:

where event_date < sysdate - 1

If you want before yesterday, then do:

where event_date < trunc(sysdate) - 1

As for performance, that depends on how many rows are being deleted. If your table only has thousands of rows, then this is fine. If it has millions of rows, then you might want an index on event_date. Or, you might even want to take a different approach -- selecting the data into a temporary table, truncating the original table, and then re-inserting it.

Answer 2

Your query is deleting records where the EVENT_DATE is before yesterday.

If you want to delete records that are over 24 hours old, try:

delete from TEMP_SERVICE_OPTION where (SYSDATE - EVENT_DATE) > 1;