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

On Jul 23, 12:08 pm, "bobdu..._at_gmail.com" <bobdu..._at_gmail.com> wrote:
>
> I'm sorry, should've mentioned - 10g.

My guess is if you have similar number of "auditable" events every day, then seven days represents about 15% of your table data (for the weekly purge ones). If that is the case, Oracle is just going to read the whole table and decide what to delete, ignoring your index. In that case, that is OK, since it is probably quicker than the index.

For the ones where you purge weekly, if you always do it by day, and you are running the Enterprise Edition of 10g, I would suggest at least reviewing partitioning your table by day as an option. Using that, you could just drop the partition from seven days ago, which is extremely fast.

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

If you can, I would also suggest looking at dbms_fga (Fine Grained Auditing).

See http://www.oracle.com/technology/pub/articles/project_lockdown/phase4.html for a portion of a test case.

HTH, Steve Received on Mon Jul 23 2007 - 11:35:35 CDT