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) 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.

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.