Materialized View with Fast Refresh idea

Complete refresh for materialized views may not be time effective in all cases. Moreover if the changes to data are minimal Fast refresh should be used and scheduled frequently using cron job or dbms_job.

I faced issue as there was no primary key on underlying table to create materialized view. I was under impression that there must be primary key. However there is an idea to beat this..

We can create materialized view based on rowid column as well

Below are the scripts

---------------- on Web DB  -------------------

create materialized view log on subscription.xxrh_subscription with rowid excluding new values;

grant select on subscription.MLOG$_xxrh_subscription to web;



----------------- On R12EBS environment in Web Schema  ---------------

CREATE MATERIALIZED VIEW web.xxrh_subscription_mv tablespace web_data
    REFRESH FAST ON DEMAND WITH ROWID AS
    SELECT *
    FROM subscription.xxrh_subscription@WEB_LINK;

CREATE INDEX web.XXRH_SUBSCRIPTION_MV_IDX1 ON web.XXRH_SUBSCRIPTION_MV(ORACLE_ACCOUNT_NUMBER) tablespace web_indx;

ThanksAnil Prodduturi for this knowledge!