What is the difference between view and materialized view

Showing Answers 1 - 5 of 5 Answers


  • Jun 18th, 2006

view - store the SQL statement in the database and let you use it as a table. Everytime you access the view,  the SQL statement executes.

materialized view - stores the results of the SQL in table form in the database. SQL statement only executes once and after that everytime you run the query, the stored result set is used. Pros include quick query results.


  Was this answer useful?  Yes


  • Jun 28th, 2006

hi,VIEW : This is a PSEUDO table that is not stored in the database and it is just a query.MATERIALIZED VIEWS: These are similar to a view but these are permantely stored in the database and often refreshed. This is used in optimization for the faster data retrieval and is useful in aggregation and summarization of data.

  Was this answer useful?  Yes


  • Jul 3rd, 2006

Normal Views are for Access purpose ie One need to give access the end user to specific data.

Materialized Views are to optimize the performance . WHen materialzied view are used in the queries ,stored results are fetched (Instead of fecthing from individual talbes) which improves the performance. Refreshing option for materialised view can be set.

  Was this answer useful?  Yes


  • Jul 13th, 2006

View is a logical reference to a database table. But Meterial View is actual table and we can refresh data in time intervels. If you made any change in database table that change will effect into view but not meterialize view..

  Was this answer useful?  Yes


  • Dec 18th, 2008


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
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.

Give your answer:

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


Related Answered Questions


Related Open Questions