Re: Just need a small hint re: mirroring tables

Paul Murphy wrote:

> 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

Likely the reason it is not covered is that Oracle is a relational database and what you have presented as a problem is one that has nothing to do with relational databases.

Eliminate one of the tables and grant access to the remaining table to both schemas.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)