Materalized Views and corresponding master table log for incremental refresh in Oracle 11x

Question

I have a complex query that does not satisfy the conditions for creating a materialized view with 'REFRESH FAST ON COMMIT' or 'REFRESH FAST'. I have created the materialized view with 'BUILD IMMEDIATE'. I have created materialized view logs on all the tables used in the query. My question is when I refresh the materialized view will it use the materialized view logs to incrementally refresh the table without the materialized view having the 'REFRESH FAST ON COMMIT' or 'REFRESH FAST' create options set. Will it redo the query and build all the records from scratch on refresh.

Answer 1

I did not fully get your question but here are some answers:

You can set REFRESH FAST, REFRESH COMPLETE and REFRESH FORCE. REFRESH FORCE means Oracle tries to make a FAST refresh and if this is not possible then perform a COMPLETE refresh. REFRESH FORCE is the default if you omit the clause.

COMPLETE refresh does not touch any MATERIALIZED VIEW LOG, they remain till all FAST MATERIALIZED VIEW's have been refreshed, i.e. COMPLETE refresh redo the query and build all the records from scratch.

Apparently you try to create a FAST refresh-able MATERIALIZED VIEW but you did not succeed.

Try procedure DBMS_MVIEW.EXPLAIN_MVIEW in order to check why FAST refresh is not possible. This procedure requires table MV_CAPABILITIES_TABLE:

CREATE TABLE MV_CAPABILITIES_TABLE
(
  STATEMENT_ID     VARCHAR2(30 BYTE),
  MVOWNER          VARCHAR2(30 BYTE),
  MVNAME           VARCHAR2(30 BYTE),
  CAPABILITY_NAME  VARCHAR2(30 BYTE),
  POSSIBLE         CHAR(1 BYTE),
  RELATED_TEXT     VARCHAR2(2000 BYTE),
  RELATED_NUM      NUMBER,
  MSGNO            INTEGER,
  MSGTXT           VARCHAR2(2000 BYTE),
  SEQ              NUMBER
)