One large table partitioned and then subpartitioned or several smaller partitioned tables?

Question

I currently have several audit tables that audit specific tables data.

e.g. ATAB_AUDIT, BTAB_AUDIT and CTAB_AUDIT auditing inserts, updates and deletes from ATAB, BTAB and CTAB respectively.

These audit tables are partitioned by year.

As the columns in these audit tables are identical (change_date, old_value, new_value etc.) would it be beneficial to use one large audit table, add a column holding the name of the table that generated the audit record (table_name) partition it by table_name and then subpartition by year?

The database is Oracle 11g on Solaris.

Why or why not do this?

Many thanks in advance.

Answer 1

I would guess that performance characteristics would be quite similar with either approach. I would make this decision based solely on how you decide to model your data; that is how your application(s) wish to interact with the database. I don't think your partitioning strategy would affect this decision (at least in this example).

Both approaches are valid, but sometimes people get carried away with the single-table approach and end up putting all data in one big table. There's a name for this (anti)pattern but it slips my mind.