Oracle MATERIALIZED VIEW (On Demand) Refreshing

In this post I am going to explain how to create MATERIALIZEDVIEW based on On demand 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 Einner Join department D on D.DEPTNO= E.DEPTNO
group by d.department_name;

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

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

select * from "SYSTEM"."MVW_DEPT_COST_ONDEMAND"

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.