Difference between a View and Materialized View

Questions by shravanamsharat   answers by shravanamsharat

Showing Answers 1 - 10 of 10 Answers


  • Jun 5th, 2006

View is nothing but a set a sql statements together which join single or multiple tables and  shows the data .. however views do not have the data themselves  but point to the data .

Whereas  Materialized view is a concept mainly used in  Datawarehousing .. these views contain the data itself .Reason being it is easier/faster to access the data.The main purpose of Materialized view is to do calculations and display data from multiple tables using joins .

  Was this answer useful?  Yes

narendra kumar mshar

  • Jun 12th, 2006


view: View is a logical or virtual memory which is based on select query

and the simple view is the view in which we can not make DML command if the

view is created by multiple tables.

Materialize veiw: It works faster than simple, Its works as snap shot and used for security purposes and we can make DML command in materialize view

Shambhu & Narendra

  Was this answer useful?  Yes


  • Sep 20th, 2006

materialized views are those to view the data which is located inthe remote place. As name suggest, the materialized view is not the "VIEW" but the data itself..of the remote schema

  Was this answer useful?  Yes


  • Nov 28th, 2006

Materialized View or mainly used in dataware housing.Materialized Views are more effective in multiple conditions in ware clauses.Materialized Views are summery tables(i.e the most frequently viewd data for analysys is kept inside the table) to faster retreval of data so that joins will minimizes so the performance going to be increases.

  Was this answer useful?  Yes

A view takes the output of a query and makes it appear like a virtual table. You can use a view in most places where a table can be used.

All operations performed on a view will affect data in the base table and so are subject to the integrity constraints and triggers of the base table.

A View can be used to simplify SQL statements for the user or to isolate an application from any future change to the base table definition. A View can also be used to improve security by restricting access to a predetermined set of rows or columns.

In addition to operating on base tables, one View can be based on another, a view can also JOIN a view with a table (GROUP BY or UNION).

Materialized views are schema objects that can be used to summarize, precompute, replicate, and distribute data. E.g. to construct a data warehouse.

A materialized view provides indirect access to table data by storing the results of a query in a separate schema object. Unlike an ordinary view, which does not take up any storage space or contain any data.

The existence of a materialized view is transparent to SQL, but when used for query rewrites will improve the performance of SQL execution. An updatable materialized view lets you insert, update, and delete.

You can define a materialized view on a base table, partitioned table or view and you can define indexes on a materialized view.

A materialized view can be stored in the same database as its base table(s) or in a different database.

Materialized views stored in the same database as their base tables can improve query performance through query rewrites. Query rewrites are particularly useful in a data warehouse environment.

  Was this answer useful?  Yes


  • Dec 31st, 2010

A view is nothing but a SQL query, takes the output of a query and makes it appear like a virtual table, which does not take up any storage space or contain any data

But Materialized views are schema objects, it storing the results of a query in a separate schema object(i.e., take up storage space and contain datas). This indicates the materialized view is returning a physically separate copy of the table data

This is the major difference between View and Materialized View



  • Aug 19th, 2011

View is a logical representation of data--- m.view it is physical duplicate representation of data
view don’t holds data---m.view holds data
in view for every event on base tables view automatically update immediately---- in m.v we can update for a certain period of time

  Was this answer useful?  Yes


  • Jun 26th, 2014

In views query result in not stored in disk or database but materialized views allow to store query result in disk or database
when we create views using any table rowid of view is same as original table but materialized view rowid is different
performance of view is less than materialized
view occupied no space,view contains no data where as materialized view contains data and occupies space
view is not a database object but materialized view is database object
materialized view contains a separate copy of data in case of view its only logical representation of data no separate copy of data
in case of view we always get latest data where as in case of materialized view we need to refresh the view to get latest data
advantages of view
it will provide additional level of security by restricting access a row of table data
to hide the implementation of complex queries

  Was this answer useful?  Yes


  • Aug 22nd, 2017

A view doesnt hold any data it just shows you the statements ....
A materialized view holds data and it is used to connect to other server to access data from particular table and it just brings data from there and kept it in your local server whenever you update, commit the table gets effected in your local machine and dblink is for just to connect to other server to make read, write operation on other server it does not bring that particular table to ur local machine...........

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

Answer Question

Click here to Login / Register your free account

Send   Reset


Related Answered Questions


Related Open Questions