Materialized views(snapshots) are used for reporting as well as read write replication(advanced replication). This post deals with read only materialized view creation for reporting.
Materialized view can be refreshed in three mode:
1. create materialized view mv_name
tablespace mv_data
as select * from base_table;
By default materialized view refresh type is complete. This mv should be refreshed manually and using complete mode of refresh. Base_table is called master table or source table
Example of fast refreshing materialized view:
2. create materialized view mv1
tablespace mv_data
build immediate
refresh fast
start with sysdate
next sysdate+1
as select t1.rowdid,t2.rowid, t1.c1,t2.c2
from t1,t2
where t1.c1=t2.c1;
This is fast refreshing materialized view based on join query and refreshes automatically at 24 hours interval
Build immediate indicates materialized view will actually be instantiated at first refresh.
create materialized view log on t1 with primary key,rowid;
create materialized view log on t1 with primary key,rowid;
Creates MLOG$_T1 and MLOG$_T2 tables which contains the change vectors to be used for fast refresh.
Example of fast refreshing materialized view using aggregate query:
3. create materialized view mv1
refresh fast
start with sysdate
next sysdate+1
build immediate
as select t1.rowid,t2.rowid, t1.c1,t2.c2,sum(t1.c3)
from t1,t2
where t1.c1=t2.c1
group by t1.rowid,t2.rowid,t1.c1,t2.c2;
create materialized view log on t1 with primary key,rowid including new values;
create materialized view log on t1 with primary key,rowid including new values;
Alter the materialized vew refresh schedule:
alter materialized view mv1
refresh start with sysdate next sysdate+/10/1440
Manually refreshing a materialized view:
Refresh on commit:
refresh fast on committ
as select t1.c1,t2.c2
from t1,t2
where t1.c1=t2.c1
Query rewrite:
create materialized view mv1
refresh fast on demand
enable query rewrite
as select t1.c1,t2.c2
from t1,t2
where t1.c1=t2.c1
Remedy:
ORA-32401: materialized view log on "SCOTT"."T2" does not have new values
Remedy:
Materialized view can be refreshed in three mode:
- complete refresh - whole materialized view is truncated and populated from source query using all records. This is slowest refresh but it does not require materialized view logs to be created.
- fast refresh - MV is updated with changed records. This materialized view requires materialized view logs be created on all base tables or source tables
- force refresh - if MV can not be refreshed from fast mode then mv is refreshed using complete mode.
1. create materialized view mv_name
tablespace mv_data
as select * from base_table;
By default materialized view refresh type is complete. This mv should be refreshed manually and using complete mode of refresh. Base_table is called master table or source table
Example of fast refreshing materialized view:
2. create materialized view mv1
tablespace mv_data
build immediate
refresh fast
start with sysdate
next sysdate+1
as select t1.rowdid,t2.rowid, t1.c1,t2.c2
from t1,t2
where t1.c1=t2.c1;
This is fast refreshing materialized view based on join query and refreshes automatically at 24 hours interval
Build immediate indicates materialized view will actually be instantiated at first refresh.
- materialized view with join query and fast refresh needs materilaized view log based on primary key as well as rowid
- materialized view with join query and fast refresh requires rowid from all source table be included in select list of query of materialized view definition
create materialized view log on t1 with primary key,rowid;
create materialized view log on t1 with primary key,rowid;
Creates MLOG$_T1 and MLOG$_T2 tables which contains the change vectors to be used for fast refresh.
Example of fast refreshing materialized view using aggregate query:
3. create materialized view mv1
refresh fast
start with sysdate
next sysdate+1
build immediate
as select t1.rowid,t2.rowid, t1.c1,t2.c2,sum(t1.c3)
from t1,t2
where t1.c1=t2.c1
group by t1.rowid,t2.rowid,t1.c1,t2.c2;
- materialized view with with aggregate function needs materialized view log be created based on new values
create materialized view log on t1 with primary key,rowid including new values;
create materialized view log on t1 with primary key,rowid including new values;
Alter the materialized vew refresh schedule:
alter materialized view mv1
refresh start with sysdate next sysdate+/10/1440
Manually refreshing a materialized view:
- complete refreshing mv: exec dbms_mview.refresh('materialized_view_name','C')
- fast refreshing mv: exec dbms_mview.refresh('materialized_view_name','F')
- force refreshing mv: exec dbms_mview.refresh('materialized_view_name','?')
Refresh on commit:
- IN order to refresh materialized view on commit on refresh commit object privilege needs to be granted to user creating materialized view or on commit refresh system privilege needs to be granted to user creating materialized view.
refresh fast on committ
as select t1.c1,t2.c2
from t1,t2
where t1.c1=t2.c1
Query rewrite:
create materialized view mv1
refresh fast on demand
enable query rewrite
as select t1.c1,t2.c2
from t1,t2
where t1.c1=t2.c1
- This mv will be refreshed manually only and query rewrite has been enabled. This mv requires user be granted global query rewrite system privilege be granted or query rewrite object privilege be granted on source tables.
note:Materialized view can be indexed like any oracle table.
Some cases:
1. create materialized view mv1
refresh fast
start with sysdate next sysdate+1/144
as select t1.rowid t1_rowid ,t2.rowid t2_rowid, t1.c1 c1, t2.c1 c2 from t1,t2 where t1.c1=t2.c1
refresh fast
start with sysdate next sysdate+1/144
as select t1.rowid t1_rowid ,t2.rowid t2_rowid, t1.c1 c1, t2.c1 c2 from t1,t2 where t1.c1=t2.c1
Error at line 1
ORA-12032: cannot use rowid column from materialized view log on "SCOTT"."T2"
Remedy:
alter materialized view log on t2 add rowid
2. create materialized view mv1
refresh fast
start with sysdate next sysdate+1/144
as select t1.c1 c1, t2.c1 c2 from t1,t2 where t1.c1=t2.c1
refresh fast
start with sysdate next sysdate+1/144
as select t1.c1 c1, t2.c1 c2 from t1,t2 where t1.c1=t2.c1
Error at line 1
ORA-12052: cannot fast refresh materialized view SCOTT.MV1
Remedy:
Create materialized view log on tables t1 and t2 and include rowid of both tables in query
3. create materialized view mv1
refresh fast
start with sysdate next sysdate+1/144
as select t1.rowid t1_rowid ,t2.rowid t2_rowid, t1.c1 c1, t2.c1 c2, sum(t1.c1) from t1,t2 where t1.c1=t2.c1 group by t1.c1,t2.c1 ,t1.rowid,t2.rowid
refresh fast
start with sysdate next sysdate+1/144
as select t1.rowid t1_rowid ,t2.rowid t2_rowid, t1.c1 c1, t2.c1 c2, sum(t1.c1) from t1,t2 where t1.c1=t2.c1 group by t1.c1,t2.c1 ,t1.rowid,t2.rowid
ORA-32401: materialized view log on "SCOTT"."T2" does not have new values
Remedy:
Since this materialized view uses aggregate function so include new values clause in create materialized view log statement for both tables t1 and t2
drop materialized view log on t1
create materialized view log on t1 with primary key,rowid including new values
create materialized view log on t1 with primary key,rowid including new values
drop materialized view log on t2
create materialized view log on t2 with primary key,rowid including new values
create materialized view log on t2 with primary key,rowid including new values
create materialized view mv1
refresh fast
start with sysdate next sysdate+1/144
as select t1.rowid t1_rowid ,t2.rowid t2_rowid, t1.c1 c1, t2.c1 c2, sum(t1.c1) from t1,t2 where t1.c1=t2.c1 group by t1.c1,t2.c1 ,t1.rowid,t2.rowid
refresh fast
start with sysdate next sysdate+1/144
as select t1.rowid t1_rowid ,t2.rowid t2_rowid, t1.c1 c1, t2.c1 c2, sum(t1.c1) from t1,t2 where t1.c1=t2.c1 group by t1.c1,t2.c1 ,t1.rowid,t2.rowid
4. create materialized view mv1
refresh Fast
start with sysdate next sysdate+1/144
as select t1.rowid t1_rowid ,t2.rowid t2_rowid, t1.c1 c1, t2.c1 c2 from t1@"u2",t2@"u2" where t1.c1=t2.c1
/
as select t1.rowid t1_rowid ,t2.rowid t2_rowid, t1.c1 c1, t2.c1 c2 from t1@"u2",t2@"u2" where t1.c1=t2.c1
*
ERROR at line 4:
ORA-12028: materialized view type is not supported by master site @"U2"
refresh Fast
start with sysdate next sysdate+1/144
as select t1.rowid t1_rowid ,t2.rowid t2_rowid, t1.c1 c1, t2.c1 c2 from t1@"u2",t2@"u2" where t1.c1=t2.c1
/
as select t1.rowid t1_rowid ,t2.rowid t2_rowid, t1.c1 c1, t2.c1 c2 from t1@"u2",t2@"u2" where t1.c1=t2.c1
*
ERROR at line 4:
ORA-12028: materialized view type is not supported by master site @"U2"
Remedy :
Master site and materialized view site should have same version of oracle database
Posts
