What is the differences between view and materialized view ?

Views

• Views evaluate the data in the tables based on the view definition. It is a logical view of tables without data stored anywhere else.

• The upside of a view is that it will always return the latest data to you. If the performance of a view depends on how good a select statement the view is based on then it is the downside of a view.

• If the select statement used by the view joins many tables, or uses joins based on non-indexed columns, the view could perform poorly.

Materialized views

• Materialized views are similar to regular views, that is they are a logical view of your data, however the underlying query result set has been saved to a table.

• The upside of this is that when you query a materialized view, you are querying a table, which may also be indexed. In addition, you pay the price of the join once rather than each time you select from the materialized view, because all the joins have been resolved at materialized view refresh time.

• In addition, with query rewrite enabled, Oracle can optimize a query that selects from the source of your materialized view in such a way that it instead reads from your materialized view.

• It speeds up the response time of end user application, when you create materialized views as forms of aggregate tables, or as copies of frequently executed queries.

• The downside though is that the data you get back from the materialized view is only as up to date as the last time the materialized view has been refreshed.

• Materialized views can be set to refresh manually, on a set schedule, or based on the database detecting a change in data from one of the underlying tables.

• It can be updated, by combining them with materialized view logs, thus changing data capture sources on the underlying tables.

View

Views are a logical virtual table created by “select query” but the result is not stored anywhere in the disk and every time we need to fire the query when we need data, so always we get updated or latest data from original tables.

Performance of the view depends on our select query. If we want to improve the performance of view we should avoid using join statement in our query or if we need multiple joins between table always try to use the index based column for joining as we know index based columns are faster than a non-index based column.

View also allows storing the definition of the query in the database itself.

Materialized View

Materialized views are also the logical view of our data-driven by the select query but the result of the query will get stored in the table or disk, also the definition of the query will also store in the database.

When we see the performance of Materialized view it is better than normal View because the data of materialized view will be stored in table and table may be indexed so faster for joining also joining is done at the time of materialized views refresh time so no need to every time fire join statement as in case of view.

Materialized views are disk based and are updated periodically based upon the query definition.

Views are virtual only and run the query definition each time they are accessed.