Difference between normal and materialized view

Normal View:-

• It is a stored select statement
• It is a virtual component
• It allows DESC, DML, SELECT on it
• It is stored permanently in “user_views” system table.
• It can be shared with other users
• DML on view are reflected in table and DML on table are reflected in view
• It is used to share “selected rows and columns” with other rows
• It is used for reporting purpose
• It will improve the performance while manipulating or retrieving data through views

Materialized view:-

·         It is a static view

·         It holds data in it

·         It will not support DML on it

·         DML on table will not be reflected in view

·         To create it “create materialized view “permission is required.

·         It is used to maintain historic data.

·         It is used for data analysis and reporting purpose.

·         It is same as SNAP SHOT (defined by DBA only)