Materialized View (mview) in oracle

Materialized View (mview) in oracle
A materialized view is a replica of a target master from a single point in time. The master can be either a master table at a master site or a master materialized view at a materialized view site. Whereas in multimaster replication tables are continuously updated by other master sites, materialized views are updated from one or more masters through individual batch updates, known as a refreshes, from a single master site or master materialized view site.

How to create materialized view in oracle

Create materialized view refresh on demand with force method

CREATE MATERIALIZED VIEW [OWNER].[MVIEW_NAME]
TABLESPACE
NOCACHE
LOGGING
NOPARALLEL
BUILD IMMEDIATE
REFRESH FORCE
WITH ROWID
AS
SELECT * FROM ;

Create materialized view refresh on demand with force method and create schedule job for refresh materialized view.

CREATE MATERIALIZED VIEW [OWNER].[MVIEW_NAME]
TABLESPACE
NOCACHE
LOGGING
NOPARALLEL
BUILD IMMEDIATE
REFRESH FORCE
START WITH TO_DATE('21-04-2009 00:00:00','dd-mm-yyyy hh24:mi:ss')
NEXT TRUNC(SYSDATE+1,'DD')+0/24+00/1440
WITH ROWID
AS
SELECT * FROM ;

Create materialized view refresh on commit with force method

CREATE MATERIALIZED VIEW [OWNER].[MVIEW_NAME]
TABLESPACE
NOCACHE
LOGGING
NOPARALLEL
BUILD IMMEDIATE
REFRESH FORCE ON COMMIT
WITH ROWID
AS
SELECT * FROM ;

Create materialized view refresh on demand with fast method

1. Create materialized view log

CREATE MATERIALIZED VIEW LOG ON [OWNER].[MVIEW_NAME]
TABLESPACE
WITH ROWID;

2. Create materialized view with fast method

CREATE MATERIALIZED VIEW [OWNER].[MVIEW_NAME]
TABLESPACE
NOCACHE
LOGGING
NOPARALLEL
BUILD IMMEDIATE
REFRESH FAST
WITH ROWID
AS
SELECT * FROM ;

How to refresh materialized view on demand

Refresh mview on demand method have 3 clauses
1. FAST Clause
Specify FAST to indicate the incremental refresh method, which performs the refresh according to the changes that have occurred to the master tables. The changes for conventional DML changes are stored in the materialized view log associated with the master table.The changes for direct-path INSERT operations are stored in the direct loader log.
2. COMPLETE Clause
Specify COMPLETE to indicate the complete refresh method, which is implemented by executing the defining query of the materialized view. If you request a complete refresh, then Oracle Database performs a complete refresh even if a fast refresh is possible.
3. FORCE Clause
Specify FORCE to indicate that when a refresh occurs, Oracle Database will perform a fast refresh if one is possible or a complete refresh otherwise. If you do not specify a refresh method (FAST, COMPLETE, or FORCE), then FORCE is the default.

Refresh mview command

EXEC DBMS_MVIEW.REFRESH('[OWNER].[MVIEW_NAME]','FAST');

EXEC DBMS_MVIEW.REFRESH('[OWNER].[MVIEW_NAME]','COMPLETE');

EXEC DBMS_MVIEW.REFRESH('[OWNER].[MVIEW_NAME]','FORCE');


How to alter refresh method from force to on commit
1. Execute an on-demand refresh on the materialized view to synchronize the data between the materialized view and the detail tables
EXEC DBMS_MVIEW.REFRESH('[OWNER].[MVIEW_NAME]','FORCE');

2. Alter mview from on-demand method to on-commit
ALTER MATERIALIZED VIEW . REFRESH ON COMMIT;