Oracle MATERIALIZED VIEW (On Commit) Refreshing

In this post I am going to explain how to create MATERIALIZEDVIEW based on On Commit condition in the oracle Database.
First You have to create below table in your oracle database.

DEPARTMENT Table  Creation Script:-

CREATE TABLE "SYSTEM"."DEPARTMENT" ("DEPTNO" NUMBER(4,0),"DEPARTMENT_NAME" VARCHAR2(10 BYTE),PRIMARY KEY ("DEPTNO"));

DEPARTMENT Table insert Script:- 

Insert into DEPARTMENT (DEPTNO,DEPARTMENT_NAME) values (10,'HR')Insert into DEPARTMENT (DEPTNO,DEPARTMENT_NAME) values (20,'Finance')Insert into DEPARTMENT (DEPTNO,DEPARTMENT_NAME) values (30,'IT'); 

EMPLOYEE_SRC Table  Creation Script:-

CREATE TABLE "SYSTEM"."EMPLOYEE_SRC" ("EMPNO" NUMBER(4,0),"ENAME" VARCHAR2(10 BYTE),"JOB" VARCHAR2(9 BYTE),"MGR" NUMBER(4,0),"HIREDATE" DATE,"SAL" NUMBER(7,2),"COMM" NUMBER(7,2),"DEPTNO" NUMBER(2,0),PRIMARY KEY ("EMPNO")); 

EMPLOYEE_SRC Table insert Script:-

Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (1,'KING','PRESIDENT',null,to_timestamp('17-NOV-81','DD-MON-RR HH.MI.SSXFF AM'),5000,null,10)Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7698,'BLAKE','MANAGER',7839,to_timestamp('01-MAY-81','DD-MON-RR HH.MI.SSXFF AM'),2850,null,30)Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7782,'CLARK','MANAGER',7839,to_timestamp('09-JUN-81','DD-MON-RR HH.MI.SSXFF AM'),2450,null,10)Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7566,'JONES','MANAGER',7839,to_timestamp('02-APR-81','DD-MON-RR HH.MI.SSXFF AM'),2975,null,20)Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7788,'SCOTT','ANALYST',7566,to_timestamp('19-APR-87','DD-MON-RR HH.MI.SSXFF AM'),3000,null,20)Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7902,'FORD','ANALYST',7566,to_timestamp('03-DEC-81','DD-MON-RR HH.MI.SSXFF AM'),3000,null,20);Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7369,'SMITH','CLERK',7902,to_timestamp('17-DEC-80','DD-MON-RR HH.MI.SSXFF AM'),800,null,20)Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7499,'ALLEN','SALESMAN',7698,to_timestamp('20-FEB-81','DD-MON-RR HH.MI.SSXFF AM'),1600,300,30)Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7521,'WARD','SALESMAN',7698,to_timestamp('22-FEB-81','DD-MON-RR HH.MI.SSXFF AM'),1250,500,30)Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7654,'MARTIN','SALESMAN',7698,to_timestamp('28-SEP-81','DD-MON-RR HH.MI.SSXFF AM'),1250,1400,30)Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7844,'TURNER','SALESMAN',7698,to_timestamp('08-SEP-81','DD-MON-RR HH.MI.SSXFF AM'),1500,0,30)Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7876,'ADAMS','CLERK',7788,to_timestamp('23-MAY-87','DD-MON-RR HH.MI.SSXFF AM'),1100,null,20)Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7900,'JAMES','CLERK',7698,to_timestamp('03-DEC-81','DD-MON-RR HH.MI.SSXFF AM'),950,null,30)Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (7934,'MILLER','CLERK',7782,to_timestamp('23-JAN-82','DD-MON-RR HH.MI.SSXFF AM'),1300,null,10);

First write a Select statement and it  will base our view.

select d.department_name AS "D_NAME",sum(E.sal) AS "COST"
from Employee_SRC E
inner Join department D on D.DEPTNO= E.DEPTNO
group by d.department_name;

After that create a Materialized view based on On Commit condition using above select statement.

CREATE MATERIALIZED VIEW "SYSTEM"."MVW_DEPT_COST_ONCOMMIT" ("D_NAME", "COST") REFRESH COMPLETE ON COMMIT ASSELECT d.department_name AS "D_NAME",sum(E.sal) AS "COST"from Employee_SRC Einner Join department D on D.DEPTNO= E.DEPTNOgroup by d.department_name;

Then Query the below query and it will return the same output as select statement.

select * from "SYSTEM"."MVW_DEPT_COST_ONCOMMIT" 

Then Add New record to 'IT' department.

Insert into EMPLOYEE_SRC (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) values (200,'MILLER','CLERK',7782,to_timestamp('23-JAN-82','DD-MON-RR HH.MI.SSXFF AM'),9999,null,30);commit;

Then Execute the select statement and you can not see insert data.

.
Then query the Materialized View and you can not see insert data.

But if you refresh your materialized view you can see where data has changed in the Materialized view.Execute the below Pl/SQl block to refresh your Materialized view.

begin    DBMS_MVIEW.REFRESH('MVW_DEPT_COST_ONDEMAND',atomic_refresh=>TRUE);
end;

Now, You can see data has added to Materialized view.

Thank You !!!