Re: optimal method for purging time based data

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Thu, 24 Sep 2009 23:06:22 +0200
Message-ID: <7i25aeF2vctlcU1_at_mid.individual.net>



On 09/21/2009 07:24 PM, bobdurie_at_gmail.com wrote:
> Hi,
>
> 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);
Why not

DELETE
FROM someeventtable
WHERE eventdate < (LOCALTIME - INTERVAL '7' DAY)

> 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.

With the proper license of 11g you could have the database automatically partition your data on a daily basis. Then your delete statement is merely a partition drop which is extremely efficient.

If you restrict yourself to plain vanilla SQL I believe you cannot even use your INTERVAL syntax from above. In other words, in that case you probably need to calculate the min time you want to keep and do something like

DELETE FROM someeventtable WHERE eventdate < ?

i.e. use a bind variable. Performance characteristics of this will change between database products even with indexing. For example concurrent updates of or reads on that table might have a huge impact. It is questionable, whether a one size fits all approach is feasible and reasonable - especially if performance is critical.

Kind regards

        robert

-- 
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/
Received on Thu Sep 24 2009 - 16:06:22 CDT

Original text of this message