Materialized View

A materialized view is a table that stores derived data (Data from main table for which view is created)

During its creation, we specify the SQL used to populate the materialized view.

For example, if you have a large SALES table, we may create a materialized view that sums the SALES data by region. If a user queries the SALES table for the sum of the SALES data for a region, ORACLE can redirect that query to use your materialized view in place of the SALES table.
As a result, we can reduce the number of accesses against our largest tables, improving the system performance.

Example:

create materialized view SALES_MONTH_MV
tablespace AGG_DATA
refresh complete
start with sysdate
next sysdate+1
enable query rewrite
as
select Sales_Month, SUM(Amount)
from SALES
group by Sales_Month;


The enable query rewrite clause enables the optimizer to redirect queries of SALES to SALES_MONTH_MV if appropriate.


The tablespace clause tells ORACLE where to store the local table for the materialized view.

The data will be automatically refreshed if you have enabled background job processes (via the JOB_QUEUE_PROCESSES init.ora parameter).

The start with and next clauses tell ORACLE when to schedule refreshes of the data.

In this example, a complete refresh of the view is chosen-each time the view is refreshed, its data will be completely deleted and re-created।

Fast refreshes of materialized views use materialized view logs. A materialized view log is a table stored along with the master table for the materialized view. As rows change in the master table, the changes are written to the materialized view log. During a fast refresh, the changed rows from the master table, as identified via the materialized view log, are sent to the materialized view. If the changes account for less than 25 percent of the rows in the master table, a fast refresh is generally faster than a complete refresh.

When DML changes are made to master table data, Oracle Database stores rows describing those changes in the materialized view log and then uses the materialized view log to refresh materialized views based on the master table. This process is called incremental or fast refresh. Without a materialized view log, Oracle Database must reexecute the materialized view query to refresh the materialized view. This process is called a complete refresh. Usually, a fast refresh takes less time than a complete refresh.

A materialized view log is located in the master database in the same schema as the master table। A master table can have only one materialized view log defined on it. Oracle Database can use this materialized view log to perform fast refreshes for all fast-refreshable materialized views based on the master table.

Example:

CREATE MATERIALIZED VIEW LOG ON customers WITH PRIMARY KEY, ROWID;

We can index the materialized view’s local table to improve the performance of queries against the materialized view.

Depending on the complexity of the materialized view, ORACLE may also create an index on the materialized view’s local table।

To enable a materialized view for query rewrite, all of the master tables for the materialized view must be in the materialized view’s schema, and you must have the QUERY REWRITE system privilege। If the view and the tables are in separate schemas, you must have the GLOBAL QUERY REWRITE system privilege. In general, you should create materialized views in the same schema as the tables on which they are based; otherwise, you will need to manage the permissions and grants required to create and maintain the materialized view.

Example:

CREATE MATERIALIZED VIEW products_mv
REFRESH FAST ON COMMIT
AS SELECT SUM(list_price - min_price), category_id
FROM product_information
GROUP BY category_id;