- A materialized view in Oracle is a database object that contains the results of a query. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table’s data.
- A materialized view can query tables, views, and other materialized views.
- A materialized view, or snapshot as they were previously known, is a table segment whose contents are periodically refreshed based on a query, either against a local or remote table.
Syntax:-
CREATE MATERIALIZED VIEW view-name
BUILD [IMMEDIATE | DEFERRED]
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[[ENABLE | DISABLE] QUERY REWRITE]
AS
SELECT …;
Methods :-
BUILD :-
- IMMEDIATE : The materialized view is populated immediately.This option is default one.
- DEFERRED : The materialized view is populated on the first requested refresh.
Refresh types :
- FAST : A fast refresh is attempted only there is a change in base table. If materialized view logs are not present against the source tables in advance, the creation fails.To maintain the history of change in the base table ,it is known as materialized view log.It is named as MLOG$_<base_table>. Materialized view log will be located in source database in same schema as master table.Refresh fast will perform refresh according to the changes occurred in master table.
- COMPLETE : The table segment supporting the materialized view is truncated and repopulated completely using the associated query.
- FORCE : A fast refresh is attempted. If one is not possible a complete refresh is performed.
A refresh can be triggered in one of two ways.
- ON COMMIT : The refresh is triggered by a committed data change in one of the dependent tables.
- ON DEMAND : The refresh is initiated by a manual request or a scheduled task.
Grant privileges to the user:-
GRANT CREATE MATERIALIZED VIEW TO INDIA; Grant succeeded. GRANT CREATE DATABASE LINK TO INDIA; Grant succeeded.
Creating Database link to point remote database :-
SQL> conn india/india; Connected. CREATE DATABASE LINK TEST_LINK CONNECT TO HARI IDENTIFIED BY hari USING 'orcl';
COMPLETE refresh:-
Creating Materialized View
SQL> CREATE MATERIALIZED VIEW dept_mv BUILD IMMEDIATE REFRESH COMPLETE AS SELECT * FROM hari.dept@TEST_LINK; Materialized view created.
Table data present in remote database:
SQL> select * from dept; DEPTNO DNAME LOCATION ---------- ------------ ------------ 10 ACCOUNTING PAK 30 SALES RUSSIA 70 PRODUCTION ENG 75 prod engg 80 dev arts SQL> insert into hari.dept values(100,'uat','tech'); 1 row created. SQL> commit; Commit complete. SQL> select rowid,DEPTNO,DNAME,LOCATION from dept_mv; ROWID DEPTNO DNAME LOCATION ------------------ ---------- ------------- --------- AAAW1/AAGAAAADbAAE 10 ACCOUNTING PAK AAAW1/AAGAAAADbAAF 30 SALES RUSSIA AAAW1/AAGAAAADbAAG 70 PRODUCTION ENG AAAW1/AAGAAAADbAAH 75 prod engg AAAW1/AAGAAAADbAAI 80 dev arts
Manually using complete refresh using the DBMS_VIEW package.
SQL> execute DBMS_MVIEW.REFRESH( LIST => 'dept_mv', METHOD => 'C' ); PL/SQL procedure successfully completed.
ROWID changed after refreshing MV.
SQL> select rowid,DEPTNO,DNAME,LOCATION from dept_mv; ROWID DEPTNO DNAME LOCATION ------------------ ---------- -------------------- --------- AAAW1/AAGAAAADbAAA 10 ACCOUNTING PAK AAAW1/AAGAAAADbAAB 30 SALES RUSSIA AAAW1/AAGAAAADbAAC 70 PRODUCTION ENG AAAW1/AAGAAAADbAAD 75 prod engg AAAW1/AAGAAAADbAAJ 80 dev arts AAAW1/AAGAAAADbAAK 100 uat tech 6 rows selected.
ON COMMIT option is used to refresh the materialized view when the base table DEPT data gets committed.So we don’t need to manually run the DBMS_VIEW package.
DEFERRED :-
Initially drop the existing materialized view,
SQL> drop MATERIALIZED VIEW dept_mv; Materialized view dropped. SQL> select rowid,DEPTNO,DNAME,LOCATION from dept_mv; no rows selected
Materialized view creation using DEFERRED option :-
SQL> CREATE MATERIALIZED VIEW dept_mv BUILD DEFERRED REFRESH COMPLETE AS SELECT * FROM hari.dept@TEST_LINK; Materialized view created. SQL> execute DBMS_MVIEW.REFRESH( LIST => 'dept_mv', METHOD => 'C' ); PL/SQL procedure successfully completed. SQL> select rowid,DEPTNO,DNAME,LOCATION from dept_mv; ROWID DEPTNO DNAME LOCATION ------------------ ---------- -------------------- --------- AAAW2DAAGAAAADbAAA 10 ACCOUNTING PAK AAAW2DAAGAAAADbAAB 30 SALES RUSSIA AAAW2DAAGAAAADbAAC 70 PRODUCTION ENG AAAW2DAAGAAAADbAAD 75 prod engg AAAW2DAAGAAAADbAAE 80 dev arts AAAW2DAAGAAAADbAAF 100 uat tech 6 rows selected.
FAST refresh :-
Initially drop the existing materialized view,
SQL> drop MATERIALIZED VIEW dept_mv; Materialized view dropped.
Materialized view creation using FAST refresh :-
SQL> CREATE MATERIALIZED VIEW dept_mv BUILD IMMEDIATE REFRESH FAST with PRIMARY KEY AS SELECT * FROM hari.dept@TEST_LINK; CREATE MATERIALIZED VIEW dept_mv BUILD IMMEDIATE REFRESH FAST with PRIMARY KEY AS SELECT * FROM hari.dept@TEST_LINK * ERROR at line 1: ORA-23413: table "HARI"."DEPT" does not have a materialized view log
Check the primary key constraint to create MV log with primary key.
SQL> select distinct constraint_type from user_constraints where table_name='DEPT'; C - P
Materialized view log creation :-
- A materialized view log is a schema object that records changes to a master table’s data so that a materialized view defined on the master table can be refreshed incrementally.
- It has to be created where the base table DEPT is present.
SQL> create materialized view log on dept with primary key; Materialized view log created. SQL> desc MLOG$_DEPT; Name Null? Type --------------- ------ ----------- DEPTNO NUMBER SNAPTIME$$ DATE DMLTYPE$$ VARCHAR2(1) OLD_NEW$$ VARCHAR2(1) CHANGE_VECTOR$$ RAW(255) XID$$ NUMBER SQL> select * from MLOG$_DEPT; no rows selected
Check the base table DEPT data and perform DML operation
SQL> select * from dept; DEPTNO DNAME LOCATION ---------- -------------------- -------------------- 10 ACCOUNTING PAK 30 SALES RUSSIA 70 PRODUCTION ENG 75 prod engg 80 dev arts 100 uat tech 111 hat det 7 rows selected. SQL> update dept set DNAME='upg' where DNAME='hat'; 1 row updated. SQL> commit; Commit complete. SQL> select * from dept; DEPTNO DNAME LOCATION ---------- -------------------- -------------------- 10 ACCOUNTING PAK 30 SALES RUSSIA 70 PRODUCTION ENG 75 prod engg 80 dev arts 100 uat tech 111 upg det SQL> select * from MLOG$_DEPT; DEPTNO SNAPTIME$ DMLTYPE$$ OLD_NEW$$ CHANGE_VECTOR$$ XID$$ ------- --------- --------- --------- --------------- ---------- 111 19-SEP-18 U U 04 2.5334E+15 75 19-SEP-18 D O 00 1.6889E+15
For FAST refresh option,the table will not recreated all data only the datas changed will get updated.It will get information from MV log table and perform the refresh.
Manually run materialized view as FAST refresh
execute DBMS_MVIEW.REFRESH( LIST => 'dept_mv', METHOD => 'F' );
PL/SQL procedure successfully completed.
SQL> select rowid,DEPTNO,DNAME,LOCATION from dept_mv;
ROWID DEPTNO DNAME LOCATION
------------------ ---------- -------------- ----------
AAAW2HAAGAAAADbAAA 10 ACCOUNTING PAK
AAAW2HAAGAAAADbAAB 30 SALES RUSSIA
AAAW2HAAGAAAADbAAC 70 PRODUCTION ENG
AAAW2HAAGAAAADbAAE 80 dev arts
AAAW2HAAGAAAADbAAF 100 uat tech
AAAW2HAAGAAAADbAAA 111 upg det
6 rows selected.
No ROWID gets changed except for the update DML operation.SO i will not create entire new result set using new ROWID like refresh complete.
SQL> select * from MLOG$_DEPT; no rows selected.
Once MV is got fast refreshed then entries mentioned in MV log will be removed.
FORCE refresh :-
First it will try to do FAST refresh.If MV view log is corrupted or not available,then it fail to refresh FAST and do complete refresh.
execute DBMS_MVIEW.REFRESH( LIST => 'dept_mv', METHOD => 'FORCE' );
Scheduling MV Refresh :-
Create a procedure for MV refresh
SQL> CREATE OR REPLACE PROCEDURE refresh_mv_dept AS BEGIN DBMS_MVIEW.REFRESH('MV_DEPT'); END; / Procedure created.
Schedule DBMS_SCHEDULER job daily to refresh materialized view.
SQL> BEGIN 2 DBMS_SCHEDULER.CREATE_JOB 3 ( 4 job_name => 'MY_MVIEW_REFRESH', 5 job_type => 'STORED_PROCEDURE', 6 job_action => 'REFRESH_MV_DEPT', 7 number_of_arguments => 0, 8 start_date => SYSTIMESTAMP, 9 repeat_interval => 'FREQ=DAILY;', 10 end_date => NULL, 11 enabled => TRUE, 12 auto_drop => FALSE, 13 comments => 'This job refresh MV_DEPT every day' 14 ); 15 END; 16 / PL/SQL procedure successfully completed.
Enable MV Refresh:-
SQL> exec DBMS_SCHEDULER.RUN_JOB('MY_MVIEW_REFRESH',TRUE); PL/SQL procedure successfully completed.
Disable MV Refresh:-
SQL> exec DBMS_SCHEDULER.RUN_JOB('MY_MVIEW_REFRESH',FALSE); PL/SQL procedure successfully completed.
Catch Me On:- Hariprasath Rajaram
Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg
LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/
Facebook:https://www.facebook.com/HariPrasathdba
FB Group:https://www.facebook.com/groups/894402327369506/
FB Page: https://www.facebook.com/dbahariprasath/?
Twitter: https://twitter.com/hariprasathdba