Indexing AUD$

AUD$ can become very large. It depends, of course, on how you set-up auditing in your database.

It can be a bit of a pain querying a monolithic AUD$, especially as it has no index.

So, what to do? Well, the only real option is to create an index on AUD$.

Caveat: Oracle does not support additional indexes on AUD$ (see MOS Note ID 1329731.1), but it's not a show-stopper.

A good approach to creating an index on AUD$ is to use a Function-Based Index (FBI), on the trunc of NTIMESTAMP#. This means each index leaf points to just a day's worth of AUD$ rows.

The SQL:
CREATE INDEX AUD_NTIMESTAMP
ON AUD$ (TRUNC(NTIMESTAMP#))
ONLINE
TABLESPACE MY_NEW_TABLESPACE;

To use the new index:

Example 1:
SELECT *
FROM SYSTEM.AUD$
WHERE TRUNC(NTIMESTAMP#) = TRUNC(SYSDATE);

Example 2:
SELECT *
FROM SYSTEM.AUD$
WHERE TRUNC(NTIMESTAMP#) =
TO_DATE('01-JUL-2011 00:00:00','DD-MON-YYYY HH24:MI:SS');

I found that the FBI index was about 4% of the size of AUD$.