Code Pumpkin

View vs Materialized View | Oracle SQL

September 10, 2017
Posted by Abhi Andhariya

Every Java Developer is expected to have basic knowledge of database like SQL Queries, joins, having clause, group by, views, stored procedures, cursors, triggers, etc. If you are preparing for Java / J2EE interview, you should also prepare basic SQL interview questions.

In our previous article on SQL interview Questions, we have seen differences between UNION and UNION ALL keywords. In this article, we will learn about view, materialized view and basic difference between them.

View 

A view is simply any SELECT query that has been given a name and saved in the database.  For this reason, a view is sometimes called a named query or a stored query.

In Oracle, you can create a view using below SQL syntax :


CREATE OR REPLACE VIEW view_name AS  
SELECT columns  
FROM tables  
WHERE conditions; 

Views are useful when we have complex join queries and we are using this queries at multiple places. In such cases we can give name to such complex queries i.e. we can create view or named query.

EMPLOYEE

EMP_ID Name DEPT_ID
100 Rama Prasad 3
101 Amrita Iyer 2
102 Kamal Hasan 3

DEPARTMENT

DEPT_ID DEPARTMENT_NAME
1 Admin
2 Customer Care
3 Human Resource

For Example, we have Employee and Department table. We can create a view for the join query to display Employee details with his department as below :


CREATE OR REPLACE EMP_DEPT_VIEW AS
SELECT 
   E.EMP_ID,
   E.NAME,
   E.DEPT_ID,
   D.DEPARTMENT_NAME
FROM
   EMPLOYEE E,
   DEPARTMENT D
WHERE
   E.DEPT_ID = D.DEPT_ID;

EMP_DEPT_VIEW

EMP_ID Name DEPT_ID DEPARTMENT_NAME
100 Rama Prasad 3 Human Resource
101 Amrita Iyer 2 Customer Care
102 Kamal Hasan 3 Human Resource

Now instead of writing entire join query, we can just query on view. We can also use where clause with view.


SELECT
   NAME,
   DEPARTMENT_NAME
FROM 
   EMP_DEPT_VIEW
WHERE
   DEPT_ID = 3;

In short, view is just a named query. It doesn't store anything. In oracle, view_name and its query string mapping is stored in ALL_VIEWS table. When there is a query on view, it runs the query of the view definition i.e mapped SQL query. Actual data comes from table.  


In Other words, A view contains no data itself. Quering on view pulls data from the underlying base tables.

Views are also useful when we want to hide certain columns from users which we can not do using tables. Hence, by creating a view, we can also achieve security.

Materialized views (MV)

A materialized view in Oracle is a database object that contains the results of a query.  It stores data physically and get updated periodically. While querying Materialized View, it gives data directly from Materialized View and not from table. 

SQL Query to create materialized view :


CREATE MATERIALIZED VIEW MV_EMP AS 
SELECT * 
FROM 
   EMPLOYEE;

When is Materialized view useful?

1. Better Performance with complex joins

If our join queries are using many tables, group by and aggregate functions on millions of rows, then it takes much time to execute.

In such scenarios, Materialized views help us to get data faster. Materialized views are physically exist in database. Whenever the base table is updated the Materialized view gets updated. Once MV is updated, query on that single MV gives very fast results.

2. Data Warehouses

In data warehouses, materialized views can be used to pre-compute and store aggregated data such as sum of sales. Materialized views in these environments are typically referred to as summaries since they store summarized data.

They can also be used to pre-compute joins with or without aggregations. So a materialized view is used to eliminate overhead associated with expensive joins or aggregations for a large or important class of queries.

3. Distributed Computing – To take snapshots of remote data

Materialized views are local copies of data located remotely. These remote tables whose data is being stored in materialized views are also known as snapshots. In other words, Materialized views store snapshots of remotely located tables.

4. Mobile Computing

Materialized views are used to download a subset of data from central servers to mobile clients, with periodic refreshes from the central servers and propagation of updates by clients back to the central servers.

Reference : Data Warehousing with Materialized Views

View vs Materialized View

No View Materialized View
1 View is just a named query. It doesn't store anything. Materialized view Stores data physically and get updated periodically.
2 When there is a query on view, it runs the query of the view definition. Actual data comes from underlying tables. While querying MV, it gives data from MV.
3 Slow perfomance for SELECT queries. Fast Performance for SELECT Queries.

That's all for this topic. If you guys have any suggestions or queries, feel free to drop a comment. We would be happy to add that in our post. You can also contribute your articles by creating contributor account here.

Happy Learning 🙂

If you like the content on CodePumpkin and if you wish to do something for the community and the planet Earth, you can donate to our campaign for planting more trees at CodePumpkin Cauvery Calling Campaign.

We may not get time to plant a tree, but we can definitely donate ₹42 per Tree.



About the Author


Surviving Java Developer, Passionate Blogger, Table Tennis Lover, Bookworm, Occasional illustrator and a big fan of Joey Tribbiani, The Walking Dead and Game of Thrones...!!



Tags: , ,


Comments and Queries

If you want someone to read your code, please put the code inside <pre><code> and </code></pre> tags. For example:
<pre><code class="java"> 
String foo = "bar";
</code></pre>
For more information on supported HTML tags in disqus comment, click here.
Total Posts : 124
follow us in feedly

Like Us On Facebook