6

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.

Ollie
  • 17,058
  • 7
  • 48
  • 59

1 Answers1

4

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.

James Scriven
  • 7,784
  • 1
  • 32
  • 36
  • thanks for the answer. My worry is that the single table approach could end up with a very large table indeed. I don't want it to become unmanageable. As all columns across the audit are the same though it seems logical to group the data together. – Ollie Jul 15 '11 at 14:00
  • I'm not saying you shouldn't **use** partitions, its probably a good idea. It just doesn't matter whether you have two levels of partition on one big logical table, or one level of partitions on three smaller tables. the physical tables will be the same size in the end. – James Scriven Jul 15 '11 at 14:05
  • Agreed, it's more about where that data is stored than how much is stored. Can you think of any downsides to storing it in one table though? The advantages are it keeps all audit data together, I can still create views if users need access to certain tables' audit information and more reusable SQL statements - fewer hard parses when accessing audit information. – Ollie Jul 15 '11 at 14:10
  • 1
    Good point about the views. I would say go with one table for audit data. It keeps your schema from getting cluttered with audit logs. I can't think of any drawbacks, once you factor in your ability to partition for performance if needed. – James Scriven Jul 15 '11 at 14:56
  • 1
    @James: I like your answer but being picky, I'll remind you the name for that (anti)pattern: normalization. If the tables hold identical data - and differentiate only by year and target(table), then normalization dictates they should be in one table, right? – ypercubeᵀᴹ Jul 15 '11 at 17:29
  • @ypercube: I think the name I was looking for was Key Value Pair (KVP). See http://stackoverflow.com/questions/126271/key-value-pairs-in-relational-database for example. – James Scriven Jul 15 '11 at 17:55
  • 1
    @James: OK then. I think it's also called EAV (Entity-Attribute-Value): http://stackoverflow.com/questions/870808/entity-attribute-value-database-vs-strict-relational-model-ecommerce-question – ypercubeᵀᴹ Jul 15 '11 at 18:06
  • @ypercube: Thanks, that's what I was looking for. I think the issues discussed in that question relate well this this question. – James Scriven Jul 15 '11 at 18:16
  • I have just read both those referenced questions. Some I knew, some I didn't so thanks, very informative. I'm going to go with the "normalised" single table option. I just can't reconcile splitting the virtually identical data over many almost identical tables. – Ollie Jul 17 '11 at 07:19
  • @Ollie - I am looking at a similar situation, although quite different. I am going to be splitting the data into different tables and just wanted to offer a reason as to why in my situation it made sense. The tables are in different databases to save space and one is an archive. – Travis J Apr 05 '12 at 19:45