Conceptually, a view is a virtual table within a database. The row/column data in the view is not physically stored in the database: it is derived from actual data in the underlying source tables. If the view definition is relatively simple (for example, if the view is a simple row/column subset of a single table, or a simple join based on foreign key relationships), it is fairly easy for the DBMS to translate database operations on the view into operations on the underlying tables. In this situation, the DBMS will perform this translation on the fly, operation by operation as it processes database queries or updates. In general, operations that update the database through a view (INSERT, UPDATE, or DELETE operations) will always be carried out in this way—by translating the operation into one or more operations on the source tables.
If the view definition is more complicated, the DBMS may need to actually materialize the view to carry out a query against it. That is, the DBMS will actually carry out the query that defines the view and store the query results in a temporary table within the database. Then the DBMS carries out the requested query against this temporary table to obtain the requested results. When the query processing has finished, the DBMS discards the temporary table. Figure 14-6 shows this materialization process. Clearly, materializing the view contents can be a very high-overhead operation. If the typical database workload contains many queries that require view materialization, the total throughput capacity of the DBMS can be dramatically reduced.
To address this problem, some commercial DBMS products support materialized views. When you define a view as a materialized view, the DBMS will carry out the query that defines the view once (typically when the materialized view is defined), store the results (i.e., the data that appears in the view) within the database, and then permanently maintain this copy of the view data. To maintain the accuracy of the materialized view data, the DBMS must automatically examine every change to the data in the underlying source tables and make the corresponding changes in the materialized view data. When the DBMS must process a query against the materialized view, it has the data already at hand and can process the query very efficiently. Figure 14-7 shows DBMS operation with a materialized view.
Materialized views provide a trade-off between the efficiency of updates on the data contained in the view and the efficiency of queries on the view data. In a nonmaterialized view, updates to the source tables for a view are unaffected by the view definition; they proceed at normal DBMS processing speed. However, queries against a nonmaterialized view are much less efficient than queries against ordinary database tables, since the DBMS must do a great deal of on-the-fly work to process the queries.
Materialized views reverse this balance of work. When a materialized view is defined, updates to the source tables for the view are much less efficient than updates to ordinary database tables, since the DBMS must calculate the impact of the updates and change the materialized view data accordingly. However, queries against a materialized view can proceed at the same speed as queries against actual database tables, since the materialized view is represented within the database in the same form as a real table. Thus, a materialized view is most useful when the volume of updates to the underlying data is relatively small, and the volume of queries against the view is relatively high.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.