Difference between revisions of "Materialized views"
From dbawiki
(→Rebuild a broken View) |
|||
| 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 | ||
| Line 25: | Line 26: | ||
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> | ||
| Line 55: | Line 57: | ||
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> | ||
| 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> | ||
| Line 98: | Line 100: | ||
<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 109: | ||
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> | ||
Revision as of 16:42, 3 February 2017
Create a view log for the master table 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
select log_owner , log_table from dba_mview_logs /
Create a simple materialized view
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
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
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
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%' /
Manually start a refresh
execute dbms_mview.refresh ('<owner.mv_table>');
Force a complete refresh
execute dbms_mview.refresh ('<owner.mv_table>','C');
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
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');
