read only materialized views in oracle

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. 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.
2. fast refresh  -  MV is updated with changed records. This materialized view requires materialized view logs be created on all base tables or source tables 
3. force refresh - if MV can not be refreshed from fast mode then mv is refreshed using complete mode. 

Creating materialiez view example: Requires CREATE MATERIALIZED VIEW privilege

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 

so for above mv,  mv log should be created as:
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

so for above mv log should be created as
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','?')

v$mvrefresh view is used to know the currently refreshing materialized view.

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.

create materialized view mv1
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

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

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

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

 

drop materialized view log on t2
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

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"

 

Remedy :

 

Master site and materialized view site should have same version of oracle database

keep watch always on query causing extra I/O becuase of high HWM ..its high high high !!!

while I keep watch always on top resoucre intensive queries I observerd many time

HUGE I/O ON DEF$_AQERROR,THOUGH NO ROW in that CURRENTLY.
Since oracle checks for def error perioidically,it was wasting resource in our case.

for that Query oracle fires internally is below:

select q_name, state, delay, expiration, rowid, msgid,
dequeue_msgid, chain_no, local_order_no, enq_time, enq_tid,
step_no, priority, exception_qschema, exception_queue,
retry_count, corrid, time_manager_info
from SYSTEM.DEF$_AQERROR
where time_manager_info <= :1
and state != :2
for
update skip locked

currently there is no row in SYSTEM.DEF$_AQERROR table.
Though there were some errors seen in DEFERROR view a month ago due
confliction but we deleted them giving command like following for every error transaction.

.
execute dbms_defer_sys.delete_error(destination=>null,deferred_tran_id=>'1.26.1180542');
..
commit;


meanwhile we set parametr refresh_after_errors to true in all mv refreh group and when got all errors
removed from DEFERROR at master site we set back these parameter to default i.e
false.

Now my question was why did we still had reads from DEF$_ERRROR and that was comming in huge physical reads, the highest on instance.

Thouught:

that happening beacuse of HWM of DEF$_AQERROR was not reset by oracle's given error
removal procedure dbms_defer_sys.delete_error.

I took following action.

1. for all master groups
Suspend the master acivity at master site.
SQL> connect repadmin/repadmin
SQL> begin
dbms_repcat.suspend_master_activity(gname=>'REPDBA_MG');
end;
/

2.Truncate the table

SQL>connect SYSTEM/

SQL> truncate table def$aq_error;

3. Resume the master activity for all masterr groups.

SQL> connect repadmin/repadmin
SQL> begin
dbms_repcat.resume_master_activity(gname=>'REPDBA_MG');
end;
/



and I was done.

since then I never saw that query in top SQL

Out of deep Woods-Thanks Heaven ORA-26500: error on caching "SALES"."TRANSACT

Few days back I found a huge index on my main transaction table in primary production database(9i RAC environment), which I suspected was not used by any query. So I decided to monitor its usage using oracle 9i Monitoring usage command. And after monitoring it for a week I found it was not used by any query and I dropped it just before production hour began at monday after weekly backup and other maintenance activities were finished. This table was replicated to three other databases using updateable materialized view setup.So this production database was mater site for replication.

To meet my surprise I found that even though 45 minutes of production time elapsed no new records were append to the main transaction table whose unused function based index was dropped.
In my database alert log I got very weird error in both my RAC instances
Errors in file d:\oracle\admin\ctdp\udump\ctdp1_ora_424.trc
:ORA-26500: error on caching "SALES"."TRANSACT" "

search on metalink and Google suggested
DBMS_REPUTIL.MAKE_INTERNAL_PKG ('EDBMS','TEST');
DBMS_REPUTIL.SYNC_UP_REP('EDBMS','TEST');
but it was riskier too as said links.

Then I decided to regenerate replciation support.

took following steps on repadmin user at master site

begin
dbms_repcat.suspend_master_activity(gname=>'master_replication_group_name');
end;/
begin
dbms_repcat.generate_replication_support( sname => 'schemanameofmastertable',
oname => 'tablenamewhichisrepliciated',
type => 'TABLE',
min_communication => TRUE);
end;
/

begin
dbms_repcat.resume_master_activity(gname=>'master_replication_group_name');
end;
/


thanks God it worked.

Now It was clear the culprit was poor documentation of Oracle as oracle says only that you need to regenerate replication support when you change the object only. But I did not change the master table .I dropped its one index and that can not be said change to master table.Thats only a indirect change not a change to structure of DB .