Is one large table partitioned and then subdivided into several or more smaller partitioned tables?

Question

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

eg. Authentication ATAB_AUDIT, BTAB_AUDIT and CTAB_AUDIT inserts, updates, and deletes from ATAB, BTAB, and CTAB, respectively.

These audit tables are divided by year.

Since the columns in these audit tables are identical (change_date, old_value, new_value, etc.), it would be useful to use one large audit table, add a column containing the name of the table in which the audit section is formed (table_name) is table_name, and then the unit by year?

The database is Oracle 11g on Solaris.

Why or why not do it?

Thank you very much in advance.

Answer 1

I would suggest that performance characteristics would be very similar to any approach. I would make this decision based solely on how you decide to model your data; this is how your applications want to interact with the database. I do not think your separation strategy will influence this decision (at least in this example).

Both approaches are valid, but sometimes people get carried away with a single table approach and end up putting all the data in one big table. There is a name for this (anti) template, but it slips through me.