What is the differences between view and materialized view ?
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.
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 Materialised 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
BASIS FOR COMPARISON | VIEW | MATERIALIZED VIEW |
---|---|---|
Basic | A View is never stored it is only displayed. | A Materialized View is stored on the disk. |
Define | View is the virtual table formed from one or more base tables or views. | Materialized view is a physical copy of the base table. |
Update | View is updated each time the virtual table (View) is used. | Materialized View has to be updated manually or using triggers. |
Speed | Slow processing. | Fast processing. |
Memory usage | View do not require memory space. | Materialized View utilizes memory space. |
Syntax | Create View V As | Create Materialized View V Build [clause] Refresh [clause] On [Trigger] As |
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.
BASIS FOR COMPARISON | VIEW | MATERIALIZED VIEW |
---|---|---|
Basic | A View is never stored it is only displayed. | A Materialized View is stored on the disk. |
Define | View is the virtual table formed from one or more base tables or views. | Materialized view is a physical copy of the base table. |
Update | View is updated each time the virtual table (View) is used. | Materialized View has to be updated manually or using triggers. |
Speed | Slow processing. | Fast processing. |
Memory usage | View do not require memory space. | Materialized View utilizes memory space. |
Syntax | Create View V As | Create Materialized View V Build [clause] Refresh [clause] On [Trigger] As |