Just need a small hint re: mirroring tables

Oracle 8i / Win2K

This seems absurdly simple, but there isn't any coverage of it in all my   Oracle books or in several Google searches.

I have 2 duplicate tables in different schemas of the same database. All I need to do is continuously synchronize table2 to always update itself to be the same as table1 every time table1 changes.

My first thought was to write a trigger that covers insert, update and delete events on table1. I started to write it (using :old and :new values for the update part). I think this will work fine, but I thought I might be hand writing a built-in feature of Oracle.

Then I got an idea that it might be better to do a truncate table2, insert into table2 select * from table1 for each event. This doesn't sound like a good idea performance-wise, although the tables are on the small side (just ordinary varchars and numbers, about 30 cols, a few hundred rows).

Then I thought, I wonder if materialized views would be more efficient, although all the chapters about them concern moving subsets or calculated data to a remote database.

Finally, I thought one of you might be kind enough to just give me a nudge in the right direction:

1. Stick to the triggers.
2. Go for the truncate method.
3. Use materialized views.
4. Go with the super-secret Oracle Mirrored Tables option (that I suspect is there, but can't find).

Thanks!
-Paul Received on Tue Jul 01 2003 - 20:55:48 CDT