Best way to clear stale data from very active audit tables

From: <bobdurie_at_gmail.com>
Date: Mon, 23 Jul 2007 08:37:02 -0700
Message-ID: <1185205022.260631.56920@w3g2000hsg.googlegroups.com>

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 Received on Mon Jul 23 2007 - 10:37:02 CDT