Oracle Schema Design: Seperate Schema with I/O Overhead?

Question

We are designing database schema for a new system based on Oracle 11gR1. We have identified a main schema which would have close to 100 tables, these will be accessed from the front end Java application.

We have a requirement to audit the values which got changed in close to 50 tables, this has to be done every row.

Which means, it is possible that, for a single row in MYSYS.T1 there might be 50 (or more or even less, but minimum 1) rows in MYSYS_AUDIT.T1_AUD table. We might be having old values of every column entry and new values available from T1.

DBA gave an observation, advising against this method, because he said, separate schema meant an extra I/O for every operation. Basically AUDIT schema would be used only to do some analyse and enter values (thus SELECT and INSERT).

Is it true that, "a separate schema means an extra I/O" ? I could not find justification.

It appears logical to me, as the AUDIT data should not be tampered with, so a separate schema.

Also, we designed a separate schema for archiving some tables from MYSYS. From MYSYS_ARC the table might be backed up into tapes or deleted after sufficient time.

Few stats:
Few tables (close to 20, 30) in MYSYS schema could grow to around 50M rows.
We have asked for a total disk space of 4 TB.
MYSYS_AUDIT schema might be having 10 times that of MYSYS but we wont keep them more than 3 months.
Handful of tables in MYSYS will have the following transactions/mins.

• 100 INSERT in MYSYS meaning same number of inserts into MYSYS_AUDIT tables.
• 1000 UPDATE in MYSYS tables meaning same number of inserts in MYSYS_ADIT tables.

Questions:
Given all these, can you suggest me any improvements?

1. Separate schema affects disc I/O? (one extra I/O for every schema ?)
2. Any general suggestions?

Figure:

+-------------------+          +-------------------+
|       MYSYS       |          |     MYSYS_AUDIT   |
|                   |          |                   |
|    1. T1          |          |     1. T1_AUD     |
|    2. T2          |          |     2. T2_AUD     |
|    3. T3          |--------->|     3. T3_AUD     |
|    4. T4          |(SELECT,  |     4. T4_AUD     |
|     .             | INSERT)  |      .            |
|     .             |          |      .            |
|     .             |          |      .            |
|  100. T100        |          |    50. T50_AUD    |
+-------------------+          +-------------------+
        |
        |
        |
        |
        |(INSERT)
        |
        |
        |
        *
+-------------------+
|   MYSYS_ARC       |
|                   |
|    1. T1_ARC      |
|    2. T2_ARC      |
|    3. T3_ARC      |
|    4. T4_ARC      |
|     .             |
|     .             |
|     .             |
|  100. T100_ARC    |
+-------------------+

Apart from this, we have two more schemas with only read only rights, but mainly they are for adhoc purpose and we dont mind the performance on them.

Suggestions:
There are couple of suggestions. We are agreed to on the following.

1. Schema for logical separations.
2. TRIGGER for inserting data into AUDIT tables.
3. Table names will not have _AUD suffix. :)
4. Procedure to populate ARCHIVE schema tables.
5. Partitions based on intervals.

We are analyzing ...

1. Workspace manager option.

The question is still open for further suggestions before accepting APC's or dpbradely's solution.

Answer 1

Having a separate schema is definitely the way to go. Apart from anything else it means you can use the same table names - MYSYS.T1 and MYSYS_AUDIT.T1 - which is helpful if you have tables with long names (>25 chars).

But the main virtue of the separate schema is that the audit tables can be shielded from accidental or mischievous tampering.

The impact of inserting audit rows, e,g, from a trigger, remains regardless of who owns the tables.

general table design advice

It is a good idea to give the audit tables the same structure as the main table. So if you have metadata columns which you need for auditing, such as revision number, include them on the main table. Also, populate the audit tables with new values, not old. That is, when a new record is inserted into MYSYS.T1 insert a matching record into MYSYS_AUDIT.T1; when an existing record is updated into MYSYS.T1 insert a new record into MYSYS_AUDIT.T1. It is a lot easier to validate and report on the audit tables if their latest records are the same as the current ones in the main tables.

use of triggers

Auditing doesn't need to be complicated. All we need is an insert statement in a before insert or update or delete trigger. These are easily be generated from the data dictionary view USER_TAB_COLUMNS.

Answer 2

Having the objects in a separate schema will not cause extra I/O - perhaps there was a misunderstanding and what your DBA meant to say was that the presence of auditing would result in extra I/O, which of course will be true no matter how you implement it.