14 Replies Latest reply: Aug 2, 2013 6:27 AM by bencol RSS

    Creation of Materialized view and Materialized view log.

    pm

      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