1

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.

  • 1
    I think I understand your confusion - let's try. Since your MV does not meet the requirements for `refresh fast`, it will not need the MV logs. That is a fact. Now, since that is a fact, do you still need the MV logs at all? The answer is, the logs are not there just for ONE materialized view. You may have different MV's that use the same base table (or tables). Some of those MV's may be fast refreshable, others may not. The MV log on a base table is just ONE log - it WILL be used by whatever MV's are refreshable fast and use that table, even if other MVs on the table are not fast refreshable. –  Jan 06 '17 at 14:20

1 Answers1

0

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
)
Wernfried Domscheit
  • 54,457
  • 9
  • 76
  • 110
  • Ok does refresh force the default cause the refresh to use the materialized view logs. –  Jan 06 '17 at 13:34
  • Also should i create materialized view logs at all for refresh force? –  Jan 06 '17 at 13:34
  • MATERIALIZED VIEW LOG on base table are (one) prerequisite for fast refresh. What is your problem, resp. what do you try to achieve? – Wernfried Domscheit Jan 06 '17 at 13:36
  • I want to know if refresh force the default option will use the materialized view log to do incremental updates and not a full rerun of the query. –  Jan 06 '17 at 13:41
  • Read my answer: "force" can do both, FAST or COMPLETE refresh. FAST uses materialized view logs, COMPLETE does not. – Wernfried Domscheit Jan 06 '17 at 13:53