This topic fits into our data management coverage and preparing the data for reporting, other applications, and data services. Materialized views are valuable tools to use in your database environment.

An MV allows you to execute a SQL query at a point in time and store the result set in a table (either locally or in a remote database). After the MV is initially populated, you can later rerun the MV query and store the fresh results in the underlying table. There are ways to automate refreshes as well as real-time MVs.

There are three main uses for MVs:

•     Replicating of data to offload query workloads to separate reporting databases

•     Improving performance of queries by periodically computing and storing the results of complex aggregations of data, which lets users query point-in-time results

•     Stopping the query from executing if the query rewrite does not happen

The MV can be a query based on tables, views, and other MVs. The base tables are often referred to as master tables. When you create an MV, Oracle internally creates atable (with the same name as the MV) as well as an MV object (visible in DBA/ALL/USER_ OBJECTS).

MV Terminology

There are many terms related to refreshing MVs. You should be familiar with these terms before implementing the features. Table 9-1 defines the various terms relevant to MVs.

Table9-1.MVTerminology

This table will serve as a good reference as you read the rest of the chapter. The examples will further explain these terms and concepts.

Just like with other objects in the database and what we saw with JSON duality views, there are data dictionary views that are helpful when working with MVs. Table 9-2 describes the MV-related data dictionary views.

Table9-2.MVDataDictionaryViewDefinitions

DBA/ALL/USER_REFRESH DBA_RGROUP

DBA_RCHILD