Difference between revisions of "Materialized views"
From dbawiki
| Line 2: | Line 2: | ||
* [http://www.shutdownabort.com/dbaqueries/Structure_Materialized_view.php shutdownabort.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 16: | Line 16: | ||
</pre> | </pre> | ||
| − | List all materialized view logs | + | ====List all materialized view logs==== |
<pre> | <pre> | ||
select log_owner | select log_owner | ||
| Line 24: | Line 24: | ||
</pre> | </pre> | ||
| − | Create a simple materialized view | + | ===Create a simple materialized view=== |
<pre> | <pre> | ||
create materialized view emp_mv | create materialized view emp_mv | ||
| Line 39: | Line 39: | ||
Force = fast if possible, complete if not.<br /> | Force = fast if possible, complete if not.<br /> | ||
<br /> | <br /> | ||
| − | Show all materialized views and their refresh times | + | ====Show all materialized views and their refresh times==== |
<pre> | <pre> | ||
set lines 100 pages 999 | set lines 100 pages 999 | ||
| Line 52: | Line 52: | ||
</pre> | </pre> | ||
| − | Show materialized view tables and masters | + | ====Show materialized view tables and masters==== |
<pre> | <pre> | ||
set lines 100 | set lines 100 | ||
| Line 63: | Line 63: | ||
</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 85: | Line 85: | ||
</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> | ||
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');
