Difference between a View and Materialized View

Questions by shravanamsharat   answers by shravanamsharat

Showing Answers 1 - 9 of 9 Answers

iamgreasy

  • 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
 

ANS:

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

deva

  • 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

Sravan

  • 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

selvaspak

  • 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

Regards,
SSG

SURESH REDDI

  • 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

annaiah

  • 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

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