Difference between revisions of "Materialized views"
From dbawiki
(→Rebuild a broken View) |
|||
| (One intermediate revision by the same user not shown) | |||
| Line 1: | Line 1: | ||
| − | + | * [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 | + | 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 | + | as select * from emp@dblink |
/ | / | ||
| − | |||
| − | |||
| − | |||
| − | |||
</pre> | </pre> | ||
| − | + | where<br /> | |
| − | Show all materialized and | + | 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> | ||
| − | + | ===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> | ||
| − | + | 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> | </pre> | ||
| Line 98: | Line 106: | ||
<pre> | <pre> | ||
drop materialized view log on loyalty.account | 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 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; | create synonym snap_ltyprd.account for loyalty.account; | ||
</pre> | </pre> | ||
| Line 107: | Line 115: | ||
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; | 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; | 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
Contents
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');
