Error creating materialized view ON COMMIT

Question

I'm trying to create a MATERIALIZED VIEW like this:

CREATE MATERIALIZED VIEW MVProductStock 
BUILD IMMEDIATE
REFRESH FAST
ON COMMIT
AS
SELECT 
    IMP.PRODUCTID ID, 
    IMP.TOSTOCKID STOREID, 
    (IMP.TOTAL-COALESCE(EXP.TOTAL,0)) QUANTITY,
    SP.NAME,
    SP.COLOR,
    SP.WIDTH,
    SP.HEIGHT,
    SP.WEIGHT,
    SP.TYPE,
    SP.CATEGORY,
    SP.INTERNALREFERENCE,
    SP.EAN13BARCODE,
    SP.SALEPRICE,
    SP.WHOLESALEPRICE,
    SP.COSTPRICE,
    SP.COSTOUTSOURCE,
    SP.SALEBONUS,
    SP.MANUFACTUREBONUS,
    SP.UOM,
    SP.UOMPURCHASE,
    SP.PROCUREMENTMETHOD,
    SP.SUPPLYMETHOD,
    SP.MFACTLEADTIME,
    SP.STATUS,
    SP.PHOTO,
    SP.DESCRIPTION,
    SP.PARENTID,
    SP.TAXVATIN,
    SP.TAXVATOUT,
    SP.DEBIT,
    SP.CREDIT
FROM 
    (SELECT P1.PRODUCTID,
            SUM(P1.quantity) TOTAL,
            S1.TOSTOCKID
    FROM STOCKTRANSFERPRODUCT P1
    INNER JOIN STOCKTRANSFER S1 ON S1.ID = P1.STOCKTRANSFER
    GROUP BY P1.PRODUCTID, S1.TOSTOCKID) IMP
LEFT JOIN
    (SELECT P1.PRODUCTID,
            SUM(P1.quantity) TOTAL,
            S1.FROMSTOCKID
    FROM STOCKTRANSFERPRODUCT P1
    INNER JOIN STOCKTRANSFER S1
    ON S1.ID = P1.STOCKTRANSFER
    GROUP BY P1.PRODUCTID,S1.FROMSTOCKID) EXP 
ON IMP.TOSTOCKID = EXP.FROMSTOCKID AND IMP.PRODUCTID = EXP.PRODUCTID  
INNER JOIN PRODUCT SP ON SP.ID = IMP.PRODUCTID 
ORDER BY IMP.TOSTOCKID;

And the table below:

Table STOCKTRANSFER 
    ID  VARCHAR2(50 BYTE)
    TYPEID  NUMBER(10,0)
    STATUS  NUMBER(10,0)
    POSTDATE    DATE
    DUEDATE DATE
    NOTE    VARCHAR2(300 BYTE)
    SALEORDERID VARCHAR2(50 BYTE)
    PURCHASEORDERID VARCHAR2(50 BYTE)
    MANUFACTUREORDERID  VARCHAR2(50 BYTE)
    PARTNERID   VARCHAR2(20 BYTE)
    FROMSTOCKID VARCHAR2(20 BYTE)
    TOSTOCKID   VARCHAR2(20 BYTE)
    USERCREATED VARCHAR2(50 BYTE)
    DATECREATED TIMESTAMP(0)
    USERUPDATED VARCHAR2(50 BYTE)
    DATEUPDATED TIMESTAMP(0)
    COMPANYID   NUMBER(10,0)[/code]

 Table STOCKTRANSFERPRODUCT 
    ID  VARCHAR2(50 BYTE)
    STOCKTRANSFER   VARCHAR2(50 BYTE)
    PRODUCTID   VARCHAR2(20 BYTE)
    QUANTITY    NUMBER(38,2)

Error content:

Error at Command Line : 55 Column : 34
Error report -
SQL Error: ORA-12054: cannot set the ON COMMIT refresh attribute for the materialized view
12054. 00000 -  "cannot set the ON COMMIT refresh attribute for the materialized view"
*Cause: The materialized view did not satisfy conditions for refresh at
           commit time.
*Action:   Specify only valid options.

Please tell me how can i fix this error, and the best way to execute my MATERIALIZED VIEW because data has been changed every second. To fast refresh, how can i create MATERIALIZED VIEW LOG.

Im a fresher in oracle, please help me. Thanks you so much.

Kobi Kobi · Accepted Answer · 2015-08-07T15:41:53

Fast refresh can be extremely difficult to enable. There are many restrictions on fast refresh and not all of them are documented well.

Don't be surprised if a simple query needs to be completely re-written to enable fast refresh. For this example, Rowids of all the tables in the FROM list must appear in the SELECT list of the query.

After that it likely still won't work and you'll need to use DBMS_MVIEW.EXPLAIN_MVIEW to see more information on why fast refresh is not enabled

Oracle doc to create MATERIALIZED VIEW LOG

Error creating materialized view ON COMMIT

1 Answers