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.
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
)