What is a materialized view?

Since Oracle 9i there are 3 types of views available in Oracle - normal/standard views, object views and materialized views, A standard view is just a query (of one or more tables) stored in the database. Views are useful to ensure that the exact same query is used in more than one place, saving development time but also saving the database from having to re-parse the statement every time. However the query still has to be run every time to retrieve the results.

Materialized views take the concept of storing the query in the database one stage further. Instead of just storing the sql statement that makes up the view, a materialized view stores the results as well. I.e. the query is materialized. In other words the sql statement doesn't have to be run each time to retrieve the results as they have already been produced.

Materialized views are often used in data warehouses to store the results of aggregations of the base data which might take hours to produce or be required by a large number of users thus saving considerable resources and/or time. For example in a data warehouse, sales information is required at various levels and by various dimensions (e.g. by time: week, month, quarter, year to date etc.; by organization hierarchy: sales team, department, group; by geography: area, country, region). It would take a long while and be wasteful to run the query to generate this information every time somebody wanted it.

Materialized views can be refreshed manually - on demand - or automatically either at scheduled intervals or when there is a change to the underlying data. Also queries against the base data can be rewritten automatically by the Oracle optimizer to query the materialized views instead.

See this question and answer on views for more help.

The Oracle Database Concepts manual has more details on materialized views and the refresh methods.

Click here to post comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.