Skip to Main Content
  • Questions
  • Complete refresh single partition of MV over DB link?

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Jennifer.

Asked: June 12, 2017 - 10:55 pm UTC

Last updated: June 13, 2017 - 1:20 pm UTC

Version: 12.1.0.2

Viewed 1000+ times

You Asked

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'); 


and Chris said...

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.

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

VLDB

If you are new to partitioning, check out Connor McDonald's introduction series here.

VLDB

Documentation set on VLDB and Partitioning.