Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Best way to clear stale data from very active audit tables

Re: Best way to clear stale data from very active audit tables

From: <>
Date: Tue, 24 Jul 2007 06:56:15 -0700
Message-ID: <>

On Jul 24, 9:39 am, joel garry <> wrote:
> On Jul 24, 5:09 am, "" <> wrote:
> > I don't want to use the SYSDATE as its a DATE and that is NOT going to
> > translate well when the retention period is smaller, ie a few hours
> > (not for this table but for others). What about if i used
> > LOCALTIMESTAMP - i've done a quick scan and it might be because the
> > CURRENT_TIMESTAMP is timezone specific, wheres the logdate is not. I
> > can assume our app's clients all run in the same timezone (hopefully
> > anyways!!!)... i'll give this a shot and i'll let you know what i
> > find.
> That sounds like a bad assumption!
> jg
> --
> is bogus.
> Wonder how Larry posts.

Richard: no, i did not know that! I assumed (badly, yes) that since the default arithmetic on sysdate was days, that was it's max resolution.

jg: It does sound like a bad assumption yes! However, our "clients" are close proximity servers, 1 to 10 or so of them and they are designed to be colocated, and definitely in the same timezone. The dates are all created on those hosts via those hosts clocks and is time zone ambivilent anyways, so until our architecture changes i think i'm ok omitting the timezone from the delete's calculations.

I tried it anyways and oracle is NOT giving me a query plan based on the LOCALTIMESTAMP query. A first glance also shows that the DELETE query is now a LOT faster, ie it actually finishes so fast i can barely catch it on the "Top Activity" SQL page of the console.

Reading the info at it says SYSDATE is the equivilant of LOCALTIMESTAMP in that it has no timezone information. Since the LOGDATE field is a TIMESTAMP, it just seems natural to use a builtin that returns a TIMESTAMP, doesn't it?

I appreciate your folks advice immensely, IF the savings last throughout the day as it continues to run i'll likely keep this change. Can you folks think of other issues with using the LOCALTIMESTAMP? Received on Tue Jul 24 2007 - 08:56:15 CDT

Original text of this message