View vs Materialized View | Oracle SQL
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.
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.
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;
|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
|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.|
Slow perfomance for
Fast Performance for
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 🙂
About the Author
Comments and QueriesIf 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.