14 Replies Latest reply on Aug 2, 2013 11:29 AM by pm

    Creation of Materialized view and Materialized view log.


      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




      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


      select ...


      ... from table1

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


      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.