optimal method for purging time based data

I'm a develper of a j2ee based web application that supports mutliple databases (oracle obviously included). We access oracle via their JDBC driver.

We have several tables in our database that store time based events, and periodically we have a routine in our code that "purges" the data. We run a query similar to the one below, this one will purge all but the last 7 days of data:

DELETE FROM someeventtable WHERE eventdate < (LOCALTIMESTAMP + INTERVAL '-10080' MINUTE); This "someeventtable" is constantly being inserted to in our application as well. We've found that on busy systems this query can take a VERY long time. I did some experimentation in the past and found that using "CURRENT_TIMESTAMP" is in fact far slower than "LOCALTIMESTAMP" so thats why we're using that - however, i'm hoping we can do better.

Can anyone suggest a SQL based method for doing this more efficiently? It must be SQL based as we support other DB's and need to remain agnostic as much as possible.

Thx,
Bob Received on Mon Sep 21 2009 - 12:24:10 CDT