Re: Just need a small hint re: mirroring tables

Paul Murphy <pmurphy_at_scsinet.com> wrote in message news:<EYqMa.28406$iZ3.16890_at_twister.nyroc.rr.com>...
> 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

Forget about 4. It doesn't exist. The obvious reason of course is you will have a database with a completely out of order datamodel. I would go for
5 Drop either one of the tables and set up proper synonyms and grants Without that you will continue symptom figthing and end up in a mess. IMO, any other solution is just stupid.

Sybrand Bakker
Senior Oracle DBA Received on Wed Jul 02 2003 - 02:43:57 CDT