We use materialized views to replicate tables between instances. We'd like to increase performance of loads on the master side by TRUNCATEing/INSERTing data to specific partitions as needed. On the remote side how can we limit the complete refresh to a single partition instead of it doing the whole table each time? PCT works well but it's not supported over DB links. Any other alternatives? How best to achieve this?
Our setup is like this:
create table master_data (hour date, company number, data varchar2(2000), primary key (hour, company) )
partition by range (hour)
(partition p1 values less than ('1-jan-2000') ,
partition p2 values less than ('2-jan-2000') ,
partition p3 values less than ('3-jan-2000') ,
partition p4 values less than ('4-jan-2000') ,
partition p5 values less than ('5-jan-2000')
);
create materialized view log on master_data with rowid, primary key;
insert into master_data (hour, company, data)
select to_date('1-jan-2000') + (hour.hour-1)/24 hour, company.company company, rpad('data', 10) data
from (select level company from dual connect by level <= 10) company,
(select level hour from dual connect by level <= 24*4) hour
;
commit;
create materialized view master_mv
partition by range (hour)
(partition p1 values less than ('1-jan-2000') ,
partition p2 values less than ('2-jan-2000') ,
partition p3 values less than ('3-jan-2000') ,
partition p4 values less than ('4-jan-2000') ,
partition p5 values less than ('5-jan-2000')
)
refresh with rowid complete on demand as select * from master_data@dbl_msrs_msrs;
alter table master_data truncate partition p2;
-- HOW TO SPECIFY THAT ONLY THE p2 PARTITION SHOULD BE REFRESHED?
exec dbms_mview.refresh('master_mv', 'C');
I'm not aware of a way to do this. As you say, PCT refresh isn't supported over a database link.
You could truncate the partition of the MV at the same time as the remote table. But you'll still need to do a complete refresh after to re-enable fast refreshes.