CAN I create FAST REFERECH ON COMMIT
Materialized View on the following Query..
PE.LOAN_NUMBER AS LOAN_NUMBER, PE.DUE_DATE
(SELECT LOAN_NUMBER, PROCESS_ID FROM WF_LOAN_PROCESS, PROCESS WHERE PROCESS_ID =
OBJECT_ID AND STATE = 2) LP,
PROCESS P,WF_PROCESS_EXTENSION PE
LP.LOAN_NUMBER = PE.LOAN_NUMBER AND
LP.PROCESS_ID = PE.ROOT_PROCESS_ID AND
PE.PROCESS_ID = P.OBJECT_ID AND
P.STATE = 2
GROUP BY PE.LOAN_NUMBER, PE.DUE_DATE;
Specify FAST for incremental refresh method, which performs the refresh according
to the changes that have occurred to the master tables. The changes are stored
either in the materialized view log associated with the master table
(for conventional DML changes) or in the direct loader log (for direct-load INSERT operations).
For both conventional DML changes and for direct-path loads, other conditions may
restrict the eligibility of a materialized view for fast refresh.
When you specify FAST refresh at create time, Oracle verifies that the materialized view
you are creating is eligible for fast refresh. When you change the refresh method to FAST
in an ALTER MATERIALIZED VIEW statement, Oracle does not perform this verification.
If the materialized view is not eligible for fast refresh, Oracle will return an error
when you attempt to refresh this view.
Materialized views are not eligible for fast refresh if the defining query contains
an analytic function.
Specify COMPLETE for the complete refresh method, which is implemented by executing
the materialized view's defining query. If you request a complete refresh,
Oracle performs a complete refresh even if a fast refresh is possible.
Specify FORCE if, when a refresh occurs, you want Oracle to perform a fast refresh
if one is possible or a complete refresh otherwise.
Specify ON COMMIT if you want a fast refresh to occur whenever Oracle commits a
transaction that operates on a master table of the materialized view.
This clause is supported only for materialized join views
and single-table materialized aggregate views.
Specify ON DEMAND if you want the materialized view to be refreshed on demand by
calling one of the three DBMS_MVIEW refresh procedures. If you omit both ON COMMIT
and ON DEMAND, ON DEMAND is the default.