Why create a Materialized View Log with Primary Key or RowID, etc?

Question

I have a table with 600million rows. I want to create a materialized view that filters it down to 50 million records but also uses REFRESH FAST ON COMMIT. To use REFRESH FAST I need a MATERIALIZED VIEW LOG on the base table.

I'm looking at how to create one and I see that I can do:

create materialized view log on t    
create materialized view log on t WITH PRIMARY KEY ;
create materialized view log on t WITH ROWID ;
create materialized view log on t WITH ROWID, PRIMARY KEY ;
create materialized view log on t  WITH SEQUENCE ;
create materialized view log on t WITH (column)

I don't see or understand the benefits or cons of this various options

This is what I'm thinking of creating:

CREATE MATERIALIZED VIEW LOG ON LLATTRDATA WITH PRIMARY KEY


CREATE MATERIALIZED VIEW MV_LLATTRDATA_TEST1
    NOLOGGING
    CACHE
    BUILD IMMEDIATE 
    REFRESH FAST ON COMMIT 
    AS 
        SELECT *
        FROM LLATTRDATA D
        WHERE 
            (D.DEFID = 3070056 AND D.ATTRID IN (2, 3, 4)) OR
            (D.DEFID = 3070055 AND D.ATTRID IN (3, 30, 31, 2, 24, 23, 4)) OR
            (D.DEFID = 3071379 AND D.ATTRID IN (3, 5, 8)) OR
            (D.DEFID = 3072256 AND D.ATTRID = 5);

BEGIN
  DBMS_STATS.gather_table_stats(
    ownname => 'me',
    tabname => 'MV_LLATTRDATA_TEST1');
END;

Answer 1

A MView Log table is with the name MLOG$_BASE_TABLE_NAME. Check this log table which show the changes being tracked. For eg, Primary key, ROWID, columns, OLD/NEW, DML Type, etc.

I had a similar challenge when designing a MView. I could try out following 3 to create the MView

1. WITH ROWID
2. WITH PRIMARY KEY
3. WITH (columns...)

When using ROWID and PRIMARY KEY, base table's record identification is present on the LOG table. In this case a query on the MView will have to fetch data from its MView storage and MView Log and the base table. In the last approach, the columns specified in the list is stored on LOG table itself. In this case does oracle refer to the base table again or will it use the values available from the MView Log?

Further, the view log you have created, must specify INCLUDING NEW VALUES like below CREATE MATERIALIZED VIEW LOG ON LLATTRDATA WITH PRIMARY KEY INCLUDING NEW VALUES This will ensure you have fast refresh working.

These are the pages from Oracle Database, SQL Language Reference, 12c Release 1 I have gone through:

• CREATE MATERLIALZED VIEW
• CREATE MATERLIALZED VIEW LOG