What is View and Materialized View ? What is the difference between them ?

Definition of View:-

View is a virtual table, created using Create View command. This virtual table contains the data retrieved from a query expression, in Create View command. View can be created from one or more than one base tables or views. A view can be queried like you query the original base tables.

It is not that the View is pre-computed and stored on the disk instead, a View is computed each time it is used or accessed. Whenever a view is used the query expression in Create View command is executed at that particular moment. Hence, you always get the updated data in a View.

If you update any content in View, it is reflected in the original table, and if any changes had been done to the original base table, it would reflect in its View. But this makes the performance of a View slower. For example, a view is created from the join of two or more tables. In that case, you have to pay time to resolve Joins each time a View is used.

But it has some advantages like it do not require storage space. You can create a customized view of a complex database. You can restrict the user from accessing sensitive information in a database. Reduces the complexity of queries by getting data from several tables into a single customized View.

Now Let us see the syntax of View

Create View V As {Query Expression}

Remember all View are not updateable. Like a View created using Distinct clause, Group By clause, CHECK constraint (if check constraints violate), Read-only option can’t be updated.

Creating Views

create view emp_det as
select e.empno, e.ename,e.sal,e.deptno,d.dname,d.loc
from emp e, dept d where e.deptno=d.deptno;

Now to see the employee details and department names we don’t have to give a join query, we can just type the following simple query.

select * from emp_det;


Creating FORCE VIEWS

A view can be created even if the defining query of the view cannot be executed, as long as the CREATE VIEW command has no syntax errors. We call such a view a view with errors. For example, if a view refers to a non-existent table or an invalid column of an existing table, or if the owner of the view does not have the required privileges, then the view can still be created and entered into the data dictionary.

You can only create a view with errors by using the FORCE option of the CREATE VIEW command:

CREATE FORCE VIEW AS ...;

Replacing/Altering Views

To alter the definition of a view, you must replace the view using one of the following methods:
A view can be dropped and then re-created. When a view is dropped, all grants of corresponding view privileges are revoked from roles and users. After the view is re-created, necessary privileges must be regranted.


A view can be replaced by redefining it with a CREATE VIEW statement that contains the OR REPLACE option. This option replaces the current definition of a view, but preserves the present security authorizations.

CREATE OR REPLACE VIEW Accounts_staff AS
SELECT Empno, Ename, Deptno
FROM Emp
WHERE Deptno = 30
WITH CHECK OPTION CONSTRAINT ica_Accounts_cnst;

more details are here : -

https://www.oracle-dba-online.com/sql/create_and_manage_views.htm

Definition of Materialized View:- 

Materialized View is the Physical copy of the original base tables. The Materialized View is like a snapshot or picture of the original base tables. Like View, it also contains the data retrieved from the query expression of Create Materialized View command.

But unlike View, the Materialized View are precomputed and stored on a disk like an object, and they are not updated each time they are used. Instead, the materialized view has to be updated manually or with the help of triggers. The process of updating the Materialized View is called Materialized View Maintenance.

Materialized View responds faster in comparison to View. It is because the materialized view is precomputed and hence, it does not waste time in resolving the query or joins in the query that creates the Materialized View. Which in turn responses faster to the query made on materialized view.

Where Build clause decides, when to populate the Materialized View. Refresh type decides how to update the Materialized View and trigger decides when to update the materialized View.

Materialized Views are generally used in the data warehouse.

Let us check the syntax of Materialized View:

Create materialized view View_Name Build [Immediate/Deffered] Refresh [Fast/Complete/Force] on [Commit/Demand] as Select ..........;

Using above syntax you can create materialized views.The Syntax includes some different optional fields:

1.Build Immediate: Means materialized views(mv) created immediately.

2.Build Deffered: Means materialized views(mv) created after one refresh.

3.Refresh on commit:This option committed the data in materialized views in SQL immediately after data inserted and committed in table.This option is known as incremental refresh option.View is not fully refreshed with this option

4.Refresh on Demand:Using this option you can add the condition for refreshing data in materialized views.

You can refresh the data using fast (incremental approach),Complete,Force options.

Example:

CREATE MATERIALIZED VIEW MV_Employee BUILD immediate
REFRESH complete
on commit SELECT * FROM Employee;

Comparison Chart :- 

BASIS FOR COMPARISON	VIEW	MATERIALIZED VIEW
Basic	A View is never stored it is only displayed.	A Materialized View is stored on the disk.
Define	View is the virtual table formed from one or more base tables or views.	Materialized view is a physical copy of the base table.
Update	View is updated each time the virtual table (View) is used.	Materialized View has to be updated manually or using triggers.
Speed	Slow processing.	Fast processing.
Memory usage	View do not require memory space.	Materialized View utilizes memory space.
Syntax	Create View V As	Create Materialized View V Build [clause] Refresh [clause] On [Trigger] As

Key Differences Between View and Materialized View

1) The basic difference between View and Materialized View is that Views are not stored physically on the disk. On the other hands, Materialized Views are stored on the disc.

2) View can be defined as a virtual table created as a result of the query expression. However, Materialized View is a physical copy, picture or snapshot of the base table.

3) A view is always updated as the query creating View executes each time the View is used. On the other hands, Materialized View is updated manually or by applying triggers to it.

4) Materialized View responds faster than View as the Materialized View is precomputed.

5) Materialized View utilizes the memory space as it stored on the disk whereas, the View is just a display hence it do not require memory space.

Conclusion:

Materialized View responds faster as compared to View. But View always provides up to date information to the user.

1)   
20 things to know about views:-

 

1.  
A view is like a virtual table. It takes the output of a query and
treats it like a table.

 

2.  
A view can be based on one or more tables or other views. These
tables/views are called base tables.

 

3.  
A view takes up no storage space other than for the definition of
the view in the data dictionary.

 

4.  
A view contains no data. All the data it shows comes from the base
tables.

 

5.  
A view can provide an additional level of table security by
restricting access to a set of rows or columns of a table.

 

6.  
A view hides implementation complexity. The user can select from
the view with a simple SQL, unaware that the view is based internally on a join
between multiple tables.

 

7.  
A view lets you change the data you can access, applying
operators, aggregation functions, filters etc. on the base table.

 

8.  
A view isolates application from changes in definitions of base
tables. Suppose a view uses two columns of a base table, it makes no difference
to the view if other columns are added, modified or removed from the base
table.

 

9.  
Using views encourages the use of shared SQL, which improves
efficiency of frequently invoked SQL.

 

10. An updatable view allows you to insert, update, and delete rows by
propagating the changes to the base table. A view can be updatable provided its
definition does not contain any of the following constructs: SET or DISTINCT
operators, an aggregate or analytic function, a GROUP BY, ORDER BY, CONNECT BY,
or START WITH clause, a subquery or collection expression in a SELECT list.

 

11.The data dictionary views ALL_UPDATABLE_COLUMNS, DBA_UPDATABLE_COLUMNS,and
USER_UPDATABLE_COLUMNS indicate which view columns are updatable.

 

12Views that are not updatable can be modified using an INSTEAD OF
trigger.

 

13.A view can be created even if the defining query of the view
cannot be executed, using the CREATE FORCE VIEW command. Such a view is called
a view with errors. This option can be useful for import/installation tools to
create a view before the underlying objects are present.

 

14.A view can be replaced with a CREATE OR REPLACE VIEW statement.
The REPLACE option updates the current view definition  but preserves the present security
authorizations.

 

15. A view lets you reorder columns easily with a CREATE OR REPLACE,
rather than going into a messy drop column for the base table with data.

 

16.To know about the views in your own schema, look up user_views.

 

17. The underlying SQL definition of the view can be read via select
text from user_views for the view.

 

18. Oracle does not enforce constraints on views. Instead, views are
subject to the constraints of their base tables.

 

19.Be careful when you define views of views. Don’t do it just
because it is easy to code – it may not be the optimal query. Check if you
would be better off using the base tables directly instead.

 

 

20.To create a view in your own schema, you must have the CREATE VIEW
system privilege. To create a view in another user’s schema, you must have the
CREATE ANY VIEW system privilege.

 

2)   
Difference between Table and View.

 

Table : An RDBMS object which can store data.

View : An RDBMS object which is a product of
multiple tables, overtime you run a “SELECT *” on a view a query run’s in
background to fetch you the desired results.

Table : You can add/update/delete data from a
table.

View : You cannot add/update/delete any data from
a view, to make any changes to the view, you will have to update the data in
the source tables that are used to create the view.

Table : You can only create or drop the table,
basically you cannot replace the table object directly as its an physical entry
in the RDBMS memory.

View : You can easily use replace option to
recreate the view, as its just a pseudo name to the query which is running
behind.

Table : DML operations can be performed.

View : DML operation is not allowed for the above-mentioned
reasons.

 

3)   
Is dml possible on views ?

 

An updatable view allows you to insert, update, and delete rows by
propagating the changes to the base table. A view can be updatable provided its
definition does not contain any of the following constructs: SET or DISTINCT
operators, an aggregate or analytic function, a GROUP BY, ORDER BY, CONNECT BY,
or START WITH clause, a subquery or collection expression in a SELECT list.