Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Renjith.

Asked: September 02, 2016 - 12:32 pm UTC

Last updated: September 06, 2016 - 3:55 pm UTC

Version: Oracle 11g

Viewed 1000+ times

You Asked

Hi,

I have a scenario as below.


a) Table A with 10 columns of which 3 columns form a composite primary key
b) A Materialzed view is created on top of the Table A with primary key enabled and fast refresh
c) Materilized view log is also created on top of the table.
c) Now i need to modify the composite primary key of Table A from 3 columns to 2 columns.

For table A, i deleted the entire data and modified the primary key.
How should i proceed with the materialized view?

a) Do i need to modify my materilized view ior it will be taken care automatically?

and Chris said...

If you've deleted all the rows from the table, you may as well do a complete refresh. But otherwise you can refresh it as normal:

create table t (w, x, y, z, primary key (w, x)) as
  select rownum, rownum, rownum, mod(rownum, 10) from dual
  connect by level <= 300;
  
create materialized view log on t with 
  primary key, rowid, sequence (z, y) including new values ;

create materialized view mv 
refresh fast 
as 
  select z, count(*), sum(y)
  from   t
  group  by z;
  
select * from mv
order  by 1;

Z  COUNT(*)  SUM(Y)  
0  30        4,650   
1  30        4,380   
2  30        4,410   
3  30        4,440   
4  30        4,470   
5  30        4,500   
6  30        4,530   
7  30        4,560   
8  30        4,590   
9  30        4,620
  
delete t;
alter table t drop primary key;
alter table t add primary key (w);

exec dbms_mview.refresh('MV', 'C');

select * from mv
order  by 1;

no rows selected

insert into t
  select rownum, rownum, rownum, mod(rownum, 10) from dual
  connect by level <= 300;
commit;

exec dbms_mview.refresh('MV', 'F');

select * from mv
order  by 1;

Z  COUNT(*)  SUM(Y)  
0  30        4,650   
1  30        4,380   
2  30        4,410   
3  30        4,440   
4  30        4,470   
5  30        4,500   
6  30        4,530   
7  30        4,560   
8  30        4,590   
9  30        4,620

Rating

  (1 rating)

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

Comments

Feedback

Renjith Paul, September 06, 2016 - 2:30 pm UTC

So as of now my code is designed as below.

a) Level 1 table
b) MV code for FAST refresh
c) MV

Am planning as below based on your feedback

a) Delete data from Level 1 table.
b) Load new data from Level 1 table.
c) MV code for COMPLETE refresh

Kindly suggest on the below
a) Will the primary key table from Level 1 table reflect in the MV?
Chris Saxon
September 06, 2016 - 3:55 pm UTC

Will the primary key table from Level 1 table reflect in the MV?

I'm not sure what you mean! The constraint is on the table not the MV. What are you hoping will happen?

More to Explore

DBMS_MVIEW

More on PL/SQL routine DBMS_MVIEW here