Series: Subject: Topic:
Question: 26 of 133

Difference between a View and Materialized View

Asked by: shravanamsharat | Member Since Jun-2006 | Asked on: Jun 1st, 2006

View all questions by shravanamsharat   View all answers by shravanamsharat

Showing Answers 1 - 9 of 9 Answers

Answered On : 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 .

Login to rate this answer.
narendra kumar mshar

Answered On : Jun 12th, 2006

ANS:view: View is a logical or virtual memory which is based on select queryand 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 viewShambhu & Narendra

Login to rate this answer.

Answered On : 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

Login to rate this answer.

Answered On : 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.

Login to rate this answer.

materialized view is physical duplicate data in a table view is logical representation of table

Login to rate this answer.

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.

Login to rate this answer.

Answered On : Dec 31st, 2010

View all answers by selvaspak

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 dataBut 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 dataThis is the major difference between View and Materialized ViewRegards,SSG

Yes  1 User has rated as useful.
Login to rate this answer.

Answered On : Aug 19th, 2011

Login to rate this answer.

Answered On : 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

Login to rate this answer.

Give your answer:

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

Related Open Questions



twitter fb Linkedin GPlus RSS


Interview Question

 Ask Interview Question?


Latest Questions

Interview & Career Tips

Get invaluable Interview and Career Tips delivered directly to your inbox. Get your news alert set up today, Once you confirm your Email subscription, you will be able to download Job Inteview Questions Ebook . Please contact me if you there is any issue with the download.