Creating a Materialized view with fast refresh and on Demand option

Question

create materialized view XXARG_GOKU
AS 
select 
XDR.GLOBAL_ID
XDR.PARTY_ID,
XDR.EMAIL_ADDRESS,
XDU.CITY_CODE,
XDU.STD_CODE,
XDU.PIN_CODE_SERIAL_NO
from XXARG_DUMMY XDU, XXARG_DRAGON XDR
where
XDR.PIN_CODE_SERIAL_NO=XDU.PIN_CODE_SERIAL_NO;

I need a query for creating fast refresh with on demand option. I have already created MLOG$_XXARG_DUMMY and MLOG$_XXARG_DRAGON mlog tables for it.

Please note: the above query is for creating a Materialised view and I'm using oracle 10g DB

Answer 1

Restrictions on Fast Refresh on Materialized Views with Joins Only

Defining queries for materialized views with joins only and no aggregates have the following restrictions on fast refresh:

They cannot have GROUP BY clauses or aggregates.

Rowids of all the tables in the FROM list must appear in the SELECT list of the query.

Materialized view logs must exist with rowids for all the base tables in the FROM list of the query.

Restrictions on Fast Refresh on Materialized Views with Joins Only

Example:

SQL> CREATE MATERIALIZED VIEW LOG ON XXARG_DUMMY with rowid;       

Materialized view log created.

SQL> CREATE MATERIALIZED VIEW LOG ON XXARG_DRAGON with rowid;  

Materialized view log created.

SQL> create materialized view XXARG_GOKU
BUILD IMMEDIATE 
REFRESH FAST ON DEMAND
AS 
select 
XDU.rowid as XDU_rowid, XDR.rowid as XDR_rowid, 
XDR.GLOBAL_ID
XDR.PARTY_ID,
XDR.EMAIL_ADDRESS,
XDU.CITY_CODE,
XDU.STD_CODE,
XDU.PIN_CODE_SERIAL_NO
from XXARG_DUMMY XDU, XXARG_DRAGON XDR
where
XDR.PIN_CODE_SERIAL_NO=XDU.PIN_CODE_SERIAL_NO;