This discussion is archived
14 Replies Latest reply: Aug 2, 2013 4:27 AM by bencol RSS

Creation of Materialized view and Materialized view log.

pm Newbie
Currently Being Moderated

I wanted to create materialized view with 'REFRESH FAST ON COMMIT' option.

 

1) Table1 --it is partitioned range + list -- Added primary key

2) view1   -- having primary keys on view's base table

 

Steps:

 

1) create materialized view log on Table1 ; -- default primary key

2) create materialized view log on view1.  --- It is giving below error.

ORA-00942: table or view does not exist

 

 

i wanted to create Materialized view like below

 

create materialized view

REFRESH fast ON commit

as

select ...

........

... from table1

where c1 in (select c1 from view1 where ... );


Question:

1) As i am getting above error while creating MV log on view. Can we create MV log on view or we have to create MV log on view base table?

2) To create MV with ''REFRESH FAST ON COMMIT' option , do we need to have primary key on master tables? 


Any pointers on this will really helpful.


Thanks

Prasad

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points