Hi!
I'm looking into the following problem:
replicate a few tables and views to another database
DB is both 11.2.0.4 (possible update to 19c, after the wrong result bug is resolved)
For a simple table and a simple join, the solution is easy: materialized view over db link (fast refresh, periodically)
What about the dynamic view? Only some rows and columns are needed in the other DB, the query is: SELECT TEN, ROWS,OUT,OF,FIFTEEN,x,y,z FROM the_view WHERE x in (1,2,3)
It is a complex one, involving subviews, joins, CONNECT BY, DISTINCT (we can drop DISTINCT if it helps).
So fast refresh does not work.
Currently we emulate fast (read:partial) refresh by triggers on related tables that then start a background job that copies only the affected rows from the view to the remote DB (over db link).
As the view and underlying tables are big (ball park: several thousand rows), we try to avoid complete refresh. Also the update interval should be about 5 minutes, not more.
What solutions could work?
EDIT:
The cases I used/need are, from simplest to most complex:
(implementation with dblink, the command that does the initial copy, later updating/refreshing is done differently)
insert into remote1@dblink select a,b,c,d,e from table_1;
insert into remote2@dblink select x,y,z,u,w from table_2 where aa=1;
insert into remote3@dblink select x,y,z,u,w from table_3 inner join table_4 on a3=a4 where b3=1 and b4=1 and c3=0 and c4=0;
insert into remote5@dblink select x,y,z,u,w from view_1 where xyz=1 or def=88 or foo=32;
(view_1 as the mentioned "pretty complex view")
So the tables need to match the above "definition" and be updated every few minutes.
Row count is less than 100 for most, few thousand for table_1 and tens of thousands for the view_1 (after the WHERE clause about "less than 1000")
The remote side will only ever read from those tables, never write. The sync is always in one direction.
Message was edited by: David Balažic
Added more details