Materialized View with REFRESH FAST ON COMMIT not working

0
votes

I am trying to create materialized views. Followed below steps and implemented them, but the views are not getting refreshed ON COMMIT.

1) Base table – AMT_T. Table has SCD 2 type data. Table has no index or primary key – Existing application's architecture. 

CREATE TABLE
AMT_T
(
    ID NUMBER(11) NOT NULL,
    AMT_TYP_CDE VARCHAR2(80 CHAR) NOT NULL,
    ROW_EFF_DTM TIMESTAMP(6) NOT NULL,
    ROW_EXP_DTM TIMESTAMP(6) NOT NULL,
    AMT NUMBER(13,2) NOT NULL
);

Sample Data

ID      AMT_TYP_CDE     ROW_EFF_DTM         ROW_EXP_DTM         AMT
9956570 LOSSAMT         2012-10-22 00:00:00 2012-11-01 00:00:00 10
9956570 LOSSAMT         2012-11-01 00:00:00 9999-12-31 00:00:00 110

Another processing date table stores system processing dates and is used to join to above table to filter current rows. Table is SCD-1 and maintains only current rows for application

CREATE TABLE
    PROCESS_DTE_T
    (
        CLASS_CDE CHAR(6 CHAR) NOT NULL,
        AS_OF_DTE DATE NOT NULL,
        GRP_CDE CHAR(6 CHAR) NOT NULL
    );

CLASS_CDE   AS_OF_DTE              GRP_CDE 
DETAIL      2018-02-02 00:00:00    PAL

2) Created materialized view logs on these tables as 

create materialized view log on SCHEMA1.AMT_T with rowid;
create materialized view log on SCHEMA2.PROCESS_DTE_T with rowid;

Created successfully.

3) Created materialized view as below. 

CREATE MATERIALIZED VIEW SCHEMA1.AMT_MV 
        ( ID, 
        AMT_TYP_CDE,
        ROW_EFF_DTM, 
        ROW_EXP_DTM, 
        AMT, 
        a_row_id, 
        b_row_id ) 
BUILD IMMEDIATE 
REFRESH FAST ON COMMIT
ENABLE QUERY REWRITE AS
(
    SELECT
        a."ID",
        a."AMT_TYP_CDE",
        a."ROW_EFF_DTM",
        a."ROW_EXP_DTM",
        a."AMT",
        a.rowid,
        b.rowid
    FROM
        SCHEMA1.AMT_T a,
        SCHEMA2.PROCESS_DTE_T b
    WHERE
        TRUNC(a.ROW_EFF_DTM) <= b.AS_OF_DTE (+)
    AND TRUNC(a.ROW_EXP_DTM) > b.AS_OF_DTE (+)
    AND b.GRP_CDE = 'PAL');

View was created successfully.

Problem – a) Unable to see anything in MLOG$_* of any of above tables b) Materialized view does not show new data which was Inserted/Updated by application is past three days. Able to see new records in base table.

1
sqloracleviewsoracle12cmaterialized-views
Are the transactions committed, in both schemas? Other than that, I am unable to reproduce what you describe. I created the two tables (in two different schemas, as in your example), I created the materialized view logs and the materialized view - everything was fine. Then I inserted the sample rows and selected from the MV - obviously (at this point) the output was empty. Then I committed the transactions, and the MV returned one row. - mathguy
NOTE: There is a glaring mistake in the MV definition. You must use Oracle syntax for joins (otherwise you can't have fast refresh - a dumb Oracle restriction); but if you use Oracle syntax for outer joins, you must use it properly. In your case, the last condition should be and b.GRP_CDE(+) = ... - without the (+) for absolutely all column references to table b, the join will become an inner join. On a different matter: in the table definition and in the MV definition, you have a column AMT in the first table, but in the example it's CLM_AMT. Please edit to make it all work. - mathguy
I find Oracle licensing to be very confusing, but I believe you need Enterprise Edition to use the fast refresh feature. See the licensing info at this location. - Brian Leach
@BrianLeach - if the OP didn't have the proper license, they wouldn't be able to successfully create the materialized view. - mathguy
Thank you for your comments. I will try out and b.GRP_CDE(+) = ... condition. Corrected the CLM_ reference. Even if I correct this definition, I am not able to ascertain why the MLOG$ of base table is empty. - Nik

1 Answers

-2
votes

After commit you nid call its DBMS_SNAPSHOT.REFRESH( 'SCHEMA1','f');