MATERIALIZED VIEW (Advance Replication)

----SYNTAX

CREATE MATERIALIZED VIEW view-name

BUILD [IMMEDIATE | DEFERRED]

REFRESH [FAST | COMPLETE | FORCE ]

ON [COMMIT | DEMAND ]

[[ENABLE | DISABLE] QUERY REWRITE]

[ON PREBUILT TABLE]

AS

SELECT ...;

Table 1 Refresh Modes

Refresh Mode	Description
ON COMMIT	Refresh occurs automatically when a transaction that modified one of the materialized view's detail tables commits. This can be specified as long as the materialized view is fast refreshable (in other words, not complex). The ON COMMIT privilege is necessary to use this mode
ON DEMAND	Refresh occurs when a user manually executes one of the available refresh procedures contained in the DBMS_MVIEW package (REFRESH, REFRESH_ALL_MVIEWS, REFRESH_DEPENDENT)

Table 2 Refresh Options

Refresh Option	Description
COMPLETE	Refreshes by recalculating the materialized view's defining query.
FAST	Applies incremental changes to refresh the materialized view using the information logged in the materialized view logs, or from a SQL*Loader direct-path or a partition maintenance operation.
FORCE	Applies FAST refresh if possible; otherwise, it applies COMPLETE refresh.
NEVER	Indicates that the materialized view will not be refreshed with refresh mechanisms.

For Complete Refresh, the refresh duration will be in the FULLREFRESHTIM column of the DBA_MVIEW_ANALYSIS. For Fast Refresh duration, it will be in the INCREFRESHTIM column.

Both values are in seconds.

SELECT mview_name, last_refresh_date, fullrefreshtim, increfreshtim

FROM dba_mview_analysis

WHERE owner='SCOTT';

-----Parameter
optimizer_mode = choose, first_rows, or all_rows
job_queue_interval = 3600
job_queue_processes = 1
query_rewrite_enabled = true
query_rewrite_integrity = enforced


# Referesh
exec dbms_mview.refresh('abc_mv'); //Mannual complete refresh
EXECUTE DBMS_MVIEW.REFRESH('emp_dept_sum','F'); //Mannual fast (incremental) refresh (F denotes Fast)
Execute DBMS_MVIEW.REFRESH_DEPENDENT // Refreshes all table-based Oracle materialized views.

-----Test Demontration Non wirtable

create table abc as select * from scott.dept;
create table abc_prim as select * from scott.dept;
alter table abc_prim add constraint PK_abc_PRIM primary key (DEPTNO);

-create materialized view abc_mv as select * from abc; //Error because create materialize view to need base table one primary Key.
-create materialized view abc_mv as select * from abc_prim; //ONLY View not a writable


SQL> insert into dept_mv values(50,'TESTING','MUNICH'); insert into dept_mv values(50,'TESTING','MUNICH')
*
ERROR at line 1:
ORA-01732: data manipulation operation not legal on this view

drop materialized view abc_mv;


--------Test Demontration Updateable MVIEW (Not Refresh Perform DML)
Advantages:
Can be updated even when disconnected from the master site or master materialized view site.
Requires fewer resources than multimaster replication.
Are refreshed on demand. Hence the load on the network might be reduced compared to using multimaster replication because multimaster replication synchronises changes at regular intervalls.

create table abc_prim as select * from scott.dept;
alter table abc_prim add constraint PK_abc_PRIM primary key (DEPTNO);
create materialized view abc_mv FOR UPDATE as select * from abc_prim;
select * from abc_prim;
select * from abc_mv;
insert into abc_mv values(50,'TESTING','MUNICH');
select * from abc_prim;
select * from abc_mv;
select * from abc_mv;
select * from abc_prim


drop table abc_prim;
insert into abc_mv values(50,'TESTING','MUNICH');
select * from abc_prim;
select * from abc_mv;

drop materialized view abc_mv;


----Automatic fast refresh of materialized views (ON COMMIT REFERESH) (with refresh view log)
1.
create table abc_prim as select * from scott.dept;
alter table abc_prim add constraint PK_abc_PRIM primary key (DEPTNO);
create materialized view log on abc_prim;
create materialized view abc_mv REFRESH FAST ON COMMIT DISABLE QUERY REWRITE
as select * from scott.abc_prim;

select * from abc_prim;
select * from abc_mv;
select * from MLOG$_abc_prim;

insert into abc_prim values(50,'TEST','MUNICH');


select * from abc_prim;
select * from abc_mv;
select * from MLOG$_abc_prim;

commit;

select * from abc_prim;
select * from abc_mv;
select * from MLOG$_abc_prim;




2.
CREATE MATERIALIZED VIEW abc_mv1
ENABLE QUERY REWRITE
AS SELECT *
FROM abc_prim
GROUP BY deptno
PCTFREE 5
PCTUSED 60
NOLOGGING PARALLEL 5
TABLESPACE users
STORAGE (INITIAL 50K NEXT 50K)
USING INDEX STORAGE (INITIAL 25K NEXT 25K)
REFRESH FAST
START WITH SYSDATE
NEXT SYSDATE + 1/12;



# Purge MV log
-select * from MLOG$_abc_prim;
-insert into abc_prim values(50,'TEST','MUNICH');
-select * from MLOG$_abc_prim;
-execute DBMS_MVIEW.PURGE_LOG( master => 'T', num => 9999, flag => 'delete' ) ;
-select * from MLOG$_abc_prim;



----------REFRESH GROUPS - CLUBBING RELATED VIEWS

Metalink: http://www.databasejournal.com/features/oracle/article.php/10893_2200191_2/Manually-Refreshing-Materialized-Views-and-Creating-Refresh-Groups-in-Oracle.htm

Metalink: http://www.sqlsnippets.com/en/topic-12880.html


MAKE Make a Refresh Group
ADD Add materialized view to the refresh group
SUBTRACT Remove materialized view from the refresh group
REFRESH Manually refresh the group
CHANGE Change refresh interval of the refresh group
DESTROY Remove all materialized views from the refresh group and delete the refresh group

DBMS_REFRESH - Procedure MAKE

The MAKE procedure is used to create a new Refresh group.

We will make a refresh group my_group_1:
SQL> execute DBMS_REFRESH.MAKE(
name => 'my_group_1',
list => ' mv_market_rate, mv_dealer_rate',
next_date => sysdate,
interval => 'sysdate+1/48');

my_group_1 has two views in its group, mv_market_rate and mv_dealer_rate. Both of these views will be refreshed at an interval of 30 minutes
DBMS_REFRESH - Procedure ADD

Add a snapshot/materialized view to the already existing refresh group:
SQL> execute DBMS_REFRESH.ADD(
name => 'my_group_1',
list => 'mv_borrowing_rate');



------Monitoring materialized views
select
substr(job,1,4) "Job", substr(log_user,1,5) "user",
substr(schema_user,1,5) "schema",
substr(to_char(last_date,'DD-MM-YYYY HH24:MI'),1,16) "last_date",
substr(to_char(next_date,'DD-MM-YYYY HH24:MI'),1,16) "next_date",
substr(broken,1,2) "B" ,substr(failures,1,6) "failed",
substr(what,1,20) "command"
From dba_jobs;

-------Refresh Materialized Views

If a materialized view is configured to refresh on commit, you should never need to manually refresh it, unless a rebuild is necessary. Remember, refreshing on commit is a very intensive operation for volatile base tables. It makes sense to use fast refreshes where possible.

For on demand refreshes, you can choose to manually refresh the materialized view or refresh it as part of a refresh group.

The following code creates a refresh group defined to refresh every minute and assigns a materialized view to it.

BEGIN

   DBMS_REFRESH.make(

     name                 => 'SCOTT.MINUTE_REFRESH',

     list                 => '',

     next_date            => SYSDATE,

     interval             => '/*1:Mins*/ SYSDATE + 1/(60*24)',

     implicit_destroy     => FALSE,

     lax                  => FALSE,

     job                  => 0,

     rollback_seg         => NULL,

     push_deferred_rpc    => TRUE,

     refresh_after_errors => TRUE,

     purge_option         => NULL,

     parallelism          => NULL,

     heap_size            => NULL);

END;

/

BEGIN

   DBMS_REFRESH.add(

     name => 'SCOTT.MINUTE_REFRESH',

     list => 'SCOTT.EMP_MV',

     lax  => TRUE);

END;

/

A materialized view can be manually refreshed using the DBMS_MVIEW package.

EXEC DBMS_MVIEW.refresh('EMP_MV');

Rather than using a refresh group, you can schedule DBMS_MVIEW.REFRESH called using the ORACLE SCHEDULER