SQL Tutorial - Difference Between View and Materialized View in Oracle SQL

In the post, we will explore about the what is difference between View and Materialized View in Oracle SQL.

In this section, we will understand view and materialized view in oracle SQL with the help of example and then we will discuss about the difference between view and materialized view in oracle SQL.

What is View in Oracle SQL?

View is a named query because view stores SQL query. View is a simple query, which can be referenced by the view name. In the view, we write SQL query using one or more tables.

Let's understand more about the View in Oracle SQL

View is a temporary table or virtual table because its description similar that original table. View is always execute the stored SQL query when we run any view statement then it returns latest data of the tables. Whenever any DML operation executed on the base table then the view returns same data result which is exactly exist in their based table.

View is also use for the security purpose. For example:-If you don’t want to display all yours table columns then in this case we can use view.

Syntax of View in Oracle SQL

Create [or replace] [Force] view view_name as SQL Query;

Note: - brackets keywords are not necessary for creating view in Oracle SQL.

Example of View in Oracle SQL

CREATE VIEW ABC AS SELECT EMPNO, ENAME FROM EMP;

I am creating a view with ABC Name, in this view I am using two column empno and ename. As per requirements you can also add more columns.

What is Materialized View?

Materialized Views are physically exist in database and stores the output of the query from one or more tables. Its having ability to refresh its own.

Syntax of Materialized View in Oracle SQL

CREATE MATERIALIZED VIEW MV_MY_VIEW
REFRESH FAST START WITH SYSDATE
NEXT SYSDATE + 1
 AS SELECT * FROM <table_name>;

Difference Between View and Materialized View in Oracle

View	Materialized View
View is a Named Query because inside the view we define SQL Query that’s why View is a named Query.	Materialized View is also a Named Query because inside the view we define SQL Query that’s why View is a named Query.
View always return the latest data.	Materialized View is not return the latest data. If want to get latest data from the Materialized view then we need to refresh by manually or using trigger.
View query result not store in the database.	Materialized view query result store in the database.
View query result performance is slow.	Materialized view query result is fast.

That's all for today I think this Difference Between View and Materialized View in Oracle post will helpful for you. If you like this post then share your view in the comment box.