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

DEPARTMENT

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

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