🛢DBMS: Materialized View (Definitions)

"A view whose rows take up space. When you select from a view, the DBMS can elect to do one of two things: (a) it can get the rows from the original table, convert any derived columns, and pass the results to the application or (b) it can create a temporary table and put the rows from the original table(s) into the temporary table, then select from the temporary copy. The latter case results in a materialized view. Materialization is often necessary when there is no one-to-one correspondence between the original table's rows and the view's rows (because there is a grouping) or when many tables are affected and concurrency would be harmed (because there is a join)." (Peter Gulutzan & Trudy Pelzer, "SQL Performance Tuning", 2002)

"A view that stores the results of the query the view is based on, in addition to the SQL join statement of the view itself. Materialized views may be refreshed manually (on demand), on a regular basis, or when there is a change in the underlying tables on which that view is based." (Bob Bryla, "Oracle Database Foundations", 2004)

"A physically preconstructed view of data containing data copied into the materialized view. Materialized views can be highly efficient in read-only environments and are often used for replication, distribution and in data warehouses." (Gavin Powell, "Beginning Database Design", 2006)

"A view that physically exists through a number of underlying indexes that have been created on it." (Marilyn Miller-White et al, "MCITP Administrator: Microsoft® SQL Server™ 2005 Optimization and Maintenance 70-444", 2007)

"A dynamic table that not only contains the SQL query command to generate the rows but also stores the actual rows. The materialized view is created the first time the query is run and the summary rows are stored in the table. The materialized view rows are automatically updated when the base tables are updated." (Carlos Coronel et al, "Database Systems: Design, Implementation, and Management 9th Ed", 2011)

"A view that is actually stored as a separate object in order to optimize performance." (DAMA International, "The DAMA Dictionary of Data Management", 2011)

"A view in which the query result is cached as a concrete table that may be updated from the original base tables from time to time." (Microsoft, "SQL Server 2012 Glossary", 2012)

"A schema object that stores a query result. All materialized views are either read-only or updatable." (Oracle, "Database SQL Tuning Guide Glossary", 2013)

"An optimized database object that contains the results of a query. Materialized views can be considered as a form of caching." (Piethein Strengholt, "Data Management at Scale", 2020)