Re: optimal method for purging time based data
Date: Mon, 21 Sep 2009 11:56:16 -0700 (PDT)
Message-ID: <dbd2ada7-8c1a-4ee2-8410-684e1a2c709a_at_12g2000pri.googlegroups.com>
On Sep 21, 10:24 am, "bobdu..._at_gmail.com" <bobdu..._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);
>
> 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
This is one of those things where using a database blind command can really bite you in places you don't want to be bitten. I'm pretty sure Jonathan Lewis has written something recently about these kinds of windowed deletes, but I couldn't find it with a quick google. However, I did find http://jonathanlewis.wordpress.com/2008/09/29/index-analysis-2/ , which, if you follow some links, gives some idea of the types of specific problems one can run into. Of course, specific problems tend to have specific solutions, so in the Oracle world they tend to be things like coalescing after deleting, or using partitioning, or doing a create table as select for the rows you want to keep, then renaming table.
For situations like these, you might as well fork code for database engines. The alternative is to have people think badly of your work. Blaming Oracle for having Oracle specific solutions is pretty silly, too.
You also might want to poke around on asktom.oracle.com, I'm sure this subject has been covered there, somewhere.
There is a tuning book by Cary Millsap ( http://carymillsap.blogspot.com/ ) that has a methodology for figuring out what is slowing you down. You also should use the tools available to you to figure out what is actually slowing you down, rather than relying on mysterious incantations that may do nothing to solve your problem. Look for tools that ogle tables with wait in the table name. Enterprise Manager allows you to drill down and look at what a slow session is doing.
jg
-- _at_home.com is bogus. Step aside! http://www3.signonsandiego.com/stories/2009/sep/21/webs-general-store/?uniontribReceived on Mon Sep 21 2009 - 13:56:16 CDT