Difference view and Materialized view in Oracle SQL PLSQL.

 "This is excepted question in  interview. It is asked in  technical support ,PLSQL developer, SQL Analyst and developer."

INTRODUCTION:

View and Materialized view are called as the database object or the named query. View is the virtual table which stores the select statement from the base table by execution of the sql query at the runtime. View Query is running for the view definition. In the case of the materialized view, the data from the base table is loaded in the temporary or physical memory location by which the materialized view gets refreshed and all the dependent tables on the materialized view are also get updated after refresh operation. Materialized views are used in lakhs lines of code. In the data lake, tables are dependent on the master table to get updated where more than lakhs line of codes, today every reporting is based on this, in that case materialized view are used and preferred on the master tables.

An activity of materialized view will be refreshed by the below command, and it is having the physical data  retreived from the base table is shown with the help of select statement till refresh.

exec DBMS_MVIEW.refresh('Table name which you want to refresh'); Materialized view is used because view is failed to display the data. View and materialized view are used to provide the result of the select query. There is process of updating MV is called as MV maintenance.

View is the important concept of the database management system.Whenever an query is fired,view returns the updated data from the base table or the original table and change is clearly seen in it.

View and materialized view can be dropped using the syntax:

drop view view_name;

drop materialized materialized_view_name;

QUERY:

Below are the shared queries for your practice as Lab session.

create table emp(EMPNO NUMBER(4),ENAME VARCHAR2(10),JOB VARCHAR2(9),MGR NUMBER(4),HIREDATE DATE,SAL NUMBER(7,2),COMM NUMBER(7,2),DEPTNO NUMBER(2));

INSERT INTO emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES(7369,'SMITH','CLERK',7902,'17-Dec-80','800','',20);

INSERT INTO emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES(7499,'ALLEN','SALESMAN',7698,'20-Feb-81','1600','300',30);

INSERT INTO emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES(7521,'WARD','SALESMAN',7698,'22-Feb-81','1250','500',30);

INSERT INTO emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES(7566,'JONES','MANAGER',7839,'02-Apr-81','2975','',20);

INSERT INTO emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES(7654,'MARTIN','SALESMAN',7698,'28-Sep-81','1250','1400',30);

INSERT INTO emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES(7698,'BLAKE','MANAGER','7839','01-May-81','2850','',30);

INSERT INTO emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES(7782,'CLARK','MANAGER',7839,'09-Jun-81','2450','',10);

INSERT INTO emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES(7788,'SCOTT','ANALYST',7566,'19-Apr-87','3000','',20);

INSERT INTO emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES(7839,'KING','PRESIDENT','','17-Nov-81','5000','',10);

INSERT INTO emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES(7844,'TURNER','SALESMAN',7698,'08-Sep-81','1500','0',30);

INSERT INTO emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES(7876,'ADAMS','CLERK',7788,'23-May-87','1100','',20);

INSERT INTO emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES(7900,'JAMES','CLERK',7698,'03-Dec-81','950','',30);

INSERT INTO emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES(7902,'FORD','ANALYST',7566,'03-Dec-81','3000','',20);

INSERT INTO emp(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO) VALUES(7934,'MILLER','CLERK',7782,'23-Jan-82','1300','',10);

create or replace view emp_20

as

select * from emp where deptno=20;

select * from emp_20

create materialized view emp_20_mv

as

select * from emp where deptno=20;

select * from emp_20_mv

delete from emp;

select * from emp;

select * from emp_20;

When we delete all the records from the orginal or the base table, view will reflect no records.

select * from emp_20_mv;

When we delete all the records from the emp table, materialized view is still reflecting the old records.So,we need to remove the old records by firing or executing the below mention refresh command.

exec DBMS_MVIEW.refresh('emp_20_mv');

select * from emp_20_mv

When we insert all the records in the orginal or the base table, view will reflect the required records.

When we insert all the records for the emp table, materialized view is still reflecting no records.So,we need to insert the records by firing or executing the below mention refresh command.

DIFFERENCE:

VIEW:	Materialized View:
It is slower than the materialized view.	It is faster than the view.
When we will update the base table, there is no need refresh it.	When we will update the base table, there is need to manual refresh it. Refreshing technique is used to refresh the materialized view’s temporary table.
Syntax: Create view view_name AS SELECT columns FROM TABLES [WHERE CONDITIONS];	Syntax: Create Materialized view_name AS SELECT columns FROM TABLES [WHERE conditions];
Here, view is the keyword.	Here, materialized is the keyword.
It contains the select SQL statement.	It contains the data or output of the select statement.
It is not required the space for holding the data.	It is required the space for holding the data.
If the base table is dropped, then also the view will not be accessible.	If the base table is dropped, then also it will be accessible.
DML operations can directly performed.	DML operations cannot be directly performed.
It does not store the data, but it stores the SQL query.	It stores the data and query.
Base table dependency doesn't on the other tables, then it is preferred to use the view.	Base table dependency is on the other tables, then it is preferred to use the materialized view.
Refresh activity does not take place.	Refresh activity takes place. It stores the data of the remote table which is called snapshot. Snapshot is the older form of materialized view which is used in the data warehouse.

 

It fetches the latest data from the base table.	It does not fetch the latest data from the base table, it fetches the data from the physical table.
Any change in the base table data will reflect in the view immediately.	Any change in the base table ‘s data will not reflect in the materialized view immediately.
It is called as the online data retrieved from base tables.	It is called as the offline data or delay data retrieved from base tables.
It retrieves the data from the base table.	It retrieves the data from the materialized table which need to be manually updated through refresh from the underlying base tables.
It is used in the simple queries.	It is used in the complex queries.
Indexing cannot be applied.	Indexing can be applied.
Performance is slow for select queries.	Performance is fast for select queries
Maintenance or updating cost don't occur.	Maintenance or updating cost occur.
When the table's data is updated frequently and its access infrequently.	When the table's data is updated infrequently and its access frequently.
It is dynamic because it will show the updated data.	It is static because it will show the updated data after last refresh.

 

Storage cost is present.	Storage cost is not present.
Execution of automatic action method or trigger to refresh is not required.	Execution of automatic action method or trigger to refresh is required.
Don't require the physical copy of the database.	It requires the physical copy of the database.
Execution of the query will provide result or output which is not stored in the disk or the database.	Execution of the query will provide result or output which is stored in the disk or the database.
It is designed with fixed architecture approach for defining with sql standard.	It is designed with generic architecture approach for not defining with sql standard.
Row id is similar with the original table.	Row id is entirely different with the original table.