Difference between revisions of "Materialized views"

From dbawiki
Jump to: navigation, search
(Created page with "From [http://www.shutdownabort.com/dbaqueries/Structure_Materialized_view.php shutdownabort.com] Create a view log for the master table This is required for fast refresh <pr...")
 
 
(3 intermediate revisions by the same user not shown)
Line 1: Line 1:
−
From [http://www.shutdownabort.com/dbaqueries/Structure_Materialized_view.php shutdownabort.com]
+
* [https://oracle-base.com/articles/misc/materialized-views oracle-base.com]
 +
* [http://www.shutdownabort.com/dbaqueries/Structure_Materialized_view.php shutdownabort.com]
  
−
Create a view log for the master table
+
===Create a view log for the master table===
 
This is required for fast refresh
 
This is required for fast refresh
  
Line 15: Line 16:
 
</pre>
 
</pre>
  
−
List all materialized view logs
+
====List all materialized view logs====
 
<pre>
 
<pre>
 
select log_owner
 
select log_owner
Line 23: Line 24:
 
</pre>
 
</pre>
  
−
Create a simple materialized view
+
===Create a simple materialized view===
 
<pre>
 
<pre>
−
create materialized view andy_mview
+
create materialized view emp_mv
 
refresh [fast | complete | force]
 
refresh [fast | complete | force]
 
start with sysdate
 
start with sysdate
 
next sysdate + 1/24
 
next sysdate + 1/24
 
with primary key
 
with primary key
−
as select * from test_table
+
as select * from emp@dblink
 
/
 
/
−
 
−
Fast = update changes only
 
−
Complete = wipe and repopulate the mview
 
−
Force = fast if possible, complete if not.
 
 
</pre>
 
</pre>
−
 
+
where<br />
−
Show all materialized and resfresh times
+
Fast = update changes only<br />
 +
Complete = wipe and repopulate the mview<br />
 +
Force = fast if possible, complete if not.<br />
 +
<br />
 +
====Show all materialized views and their refresh times====
 
<pre>
 
<pre>
 
set lines 100 pages 999
 
set lines 100 pages 999
 
col last_refresh format a20
 
col last_refresh format a20
−
select owner
+
select owner
−
, mview_name
+
,     mview_name
−
, to_char(last_refresh_date, 'dd/mm/yy hh24:mi') last_refresh
+
,     to_char(last_refresh_date, 'dd/mm/yy hh24:mi') last_refresh
−
from dba_mviews
+
from   dba_mviews
−
order by owner, last_refresh
+
order by owner
 +
,     last_refresh
 
/
 
/
 
</pre>
 
</pre>
  
−
Show materialized view tables and masters
+
====Show materialized view tables and masters====
 
<pre>
 
<pre>
 
set lines 100
 
set lines 100
 
col mview format a40
 
col mview format a40
 
col master format a40
 
col master format a40
−
select owner || '.' || name mview
+
select owner || '.' || name mview
−
, master_owner || '.' || master master
+
,     master_owner || '.' || master master
−
from dba_mview_refresh_times
+
from   dba_mview_refresh_times
 
/
 
/
 
</pre>
 
</pre>
  
−
Show refresh jobs in dba_jobs
+
===Show refresh jobs in dba_jobs===
 
<pre>
 
<pre>
 
This is useful for spotting failures
 
This is useful for spotting failures
Line 72: Line 74:
 
col fail format 9999
 
col fail format 9999
 
col what format a20
 
col what format a20
−
select job
+
select job
−
, log_user
+
,     log_user
−
, to_char(last_date, 'dd/mm/yy hh24:mi') last
+
,     to_char(last_date, 'dd/mm/yy hh24:mi') last
−
, to_char(next_date, 'dd/mm/yy hh24:mi') next
+
,     to_char(next_date, 'dd/mm/yy hh24:mi') next
−
, failures fail
+
,     failures fail
−
, replace(what, '"') what
+
,     replace(what, '"') what
−
from dba_jobs
+
from   dba_jobs
−
where what like '%dbms_refresh.refresh%'
+
where what like '%dbms_refresh.refresh%'
 
/
 
/
 
</pre>
 
</pre>
  
−
Manually start a refresh
+
===Force a refresh===
 +
Fast - uses the changes in the view log on the source side.
 
<pre>
 
<pre>
−
execute dbms_mview.refresh ('<owner.mv_table>');
+
execute dbms_mview.refresh ('<owner.mv_table>','F');
 
</pre>
 
</pre>
  
−
Force a complete refresh
+
Complete - truncates or deletes the data (depending on other options) and rebuilds the view from scratch (remember to gather view stats afterwards).
 
<pre>
 
<pre>
 
execute dbms_mview.refresh ('<owner.mv_table>','C');
 
execute dbms_mview.refresh ('<owner.mv_table>','C');
 +
</pre>
 +
 +
Force - tries a fast. If this does not work, a complete will be done.
 +
<pre>
 +
execute dbms_mview.refresh ('<owner.mv_table>','?');
 +
</pre>
 +
 +
===Rebuild a broken View===
 +
Sometimes the materialised views stop working. If the original table was modified for example (maybe index was changed or a column removed), the view will fail.
 +
* On PROD
 +
<pre>
 +
drop materialized view log on loyalty.account
 +
create materialized view log on loyalty.account tablespace loyalty_dl_01 nocache with primary key, sequence including new values;
 +
create synonym snap_ltyprd.account for loyalty.account;
 +
</pre>
 +
* On QA
 +
<pre>
 +
sqlplus snap_ltyprd/****
 +
drop materialized view account;
 +
create materialized view snap_ltyprd.account tablespace snap_ts using index tablespace snap_ts refresh fast on demand enable query rewrite as select * from account@link_loyalty_ltyprd;
 +
select count('e') from account;
 +
</pre>
 +
Remember to refresh the view stats!
 +
<pre>
 +
exec dbms_stats.gather_table_stats(snap_ltyprd, 'account');
 
</pre>
 
</pre>

Latest revision as of 16:55, 3 February 2017

Create a view log for the master table[edit]

This is required for fast refresh 

create materialized view log on <table>
/

or...

create materialized view log on <table>
tablespace <tablespace_name>
/

List all materialized view logs[edit]

select 	log_owner
,	log_table
from	dba_mview_logs
/

Create a simple materialized view[edit]

create materialized view emp_mv
refresh [fast | complete | force]
start with sysdate
next sysdate + 1/24
with primary key
as select * from emp@dblink
/

where
Fast = update changes only
Complete = wipe and repopulate the mview
Force = fast if possible, complete if not.

Show all materialized views and their refresh times[edit]

set lines 100 pages 999
col last_refresh format a20
select owner
,      mview_name
,      to_char(last_refresh_date, 'dd/mm/yy hh24:mi') last_refresh
from   dba_mviews
order  by owner
,      last_refresh
/

Show materialized view tables and masters[edit]

set lines 100
col mview format a40
col master format a40
select owner || '.' || name mview
,      master_owner || '.' || master master
from   dba_mview_refresh_times
/

Show refresh jobs in dba_jobs[edit]

This is useful for spotting failures

set lines 100
col job format 9999
col log_user format a15
col last format a15
col next format a15
col fail format 9999
col what format a20
select job
,      log_user
,      to_char(last_date, 'dd/mm/yy hh24:mi') last
,      to_char(next_date, 'dd/mm/yy hh24:mi') next
,      failures fail
,      replace(what, '"') what
from   dba_jobs
where  what like '%dbms_refresh.refresh%'
/

Force a refresh[edit]

Fast - uses the changes in the view log on the source side. 

execute dbms_mview.refresh ('<owner.mv_table>','F');

Complete - truncates or deletes the data (depending on other options) and rebuilds the view from scratch (remember to gather view stats afterwards). 

execute dbms_mview.refresh ('<owner.mv_table>','C');

Force - tries a fast. If this does not work, a complete will be done. 

execute dbms_mview.refresh ('<owner.mv_table>','?');

Rebuild a broken View[edit]

Sometimes the materialised views stop working. If the original table was modified for example (maybe index was changed or a column removed), the view will fail.

  • On PROD
drop materialized view log on loyalty.account
create materialized view log on loyalty.account tablespace loyalty_dl_01 nocache with primary key, sequence including new values;
create synonym snap_ltyprd.account for loyalty.account;
  • On QA
sqlplus snap_ltyprd/****
drop materialized view account;
create materialized view snap_ltyprd.account tablespace snap_ts using index tablespace snap_ts refresh fast on demand enable query rewrite as select * from account@link_loyalty_ltyprd;
select count('e') from account;

Remember to refresh the view stats! 

exec dbms_stats.gather_table_stats(snap_ltyprd, 'account');
Retrieved from "https://mailnest.com/dbawiki/index.php?title=Materialized_views&oldid=2603"