On Jul 23, 11:53 am, Steve Howard <stevedhow..._at_gmail.com> wrote:
> bobdu..._at_gmail.com wrote:
> > Hi there, i have a java application using oracle's jdbc driver that
> > has a bunch of audit style tables. We're experiencing significant
> > issues with our application when the data within these tables is being
> > purged, and i need to know a better method for designing the schema
> > (or application) to better handle this situation.
>
> > Typically:
> > - our "audit" table is created with a bunch of fields, one being
> > "auditdate" which is a timestamp with a typical index on it.
> > - this table is continually insert'ed into
> > - select queries are run against this table on a regular basis
> > - once an hour our "purge" job runs that does:
> > DELETE FROM audits WHERE auditdate < (CURRENT_TIMESTAMP + INTERVAL
> > '-7' DAY(5))
>
> > When this table gets upwards of 20million+ records and many inserts
> > are happenning concurrently, the purge job hammers the system, and
> > causes the subsequent inserts to slow to a crawl, not to mention the
> > DELETE command above to start taking upwards of 10+ minutes to run.
>
> > I've debated doing a SELECT auditID with the where above and deleting
> > the records one by one, but that seems horribly inefficient. Stopping
> > the inserts altogether during the purge is not really an option. Can
> > someone suggest an alternate approach or design for this scenario?
>
> > A few other points:
> > - our application has several different "audit" style tables, each of
> > them has similar requirements, some get purged every week, some every
> > 2 hours. I'm hoping this approach is universal for all because all
> > are experiencing similar problems.
> > - our application can also run against sql server, which while fast,
> > occasionally has deadlock issues doing the above purging, inserting,
> > and selecting concurrently... but it is fast. This also means we aim
> > to have db agnostic code but i realize this is not always possible.
>
> > Thank you for any advice you can offer!!
>
> > Bob
>
> Hi Bob,
>
> What version are you running?
>
> Regards,
>
> Steve
I'm sorry, should've mentioned - 10g.
Received on Mon Jul 23 2007 - 11:08:18 CDT
