Monday , December 4 2023

Materialized Views in Oracle Database


A materialized view is a database object that contains the results of a query. The FROM clause of the query can name tables, views, and other materialized views. Collectively these objects are called master tables (a replication term) or detail tables (a data warehousing term). This reference uses “master tables” for consistency. The databases containing the master tables are called the master databases.

For replication purposes, materialized views allow you to maintain copies of remote data on your local node. The copies can be updatable with the Advanced Replication feature and are read-only without this feature. You can select data from a materialized view as you would from a table or view. In replication environments, the materialized views commonly created are primary key, rowid, object, and subquery materialized views.


The BUILD clause options are shown below.
• IMMEDIATE : The materialized view is populated immediately.
• DEFERRED : The materialized view is populated on the first requested refresh.
The following refresh types are available.
• FAST : A fast refresh is attempted. If materialized view logs are not present against the source tables in advance, the creation fails. Materialized view logs need to be created on master database if you need an incremental refresh of mview.
• COMPLETE : The table segment supporting the materialized view is truncated and repopulated completely using the associated query.
• FORCE : A fast refresh is attempted. If one is not possible a complete refresh is performed.
A refresh can be triggered in one of two ways.
• ON COMMIT : The refresh is triggered by a committed data change in one of the dependent tables.
• ON DEMAND : The refresh is initiated by a manual request or a scheduled task.

The QUERY REWRITE clause tells the optimizer if the materialized view should be consider for query rewrite operations. Queries involving joins between data-heavy tables can take such a long time is that the engine needs to compute aggregates and disentangle the joins between the tables before it can run the query. However, materialized views, by their very nature, have all that information precomputed. Therefore, if you can find the right materialized view and match it to the right query, you can cut running times dramatically.

This trick, called Query Rewrite, only works with SELECT statements. However, those statements may be hidden away in a CREATE TABLE … AS SELECT statement, or an INSERT INTO … SELECT statement, or they may be squirrelled away in any type of subquery or sub-clause.

Additionally, Query Rewrite must be enabled at the initialization parameter level as follows:

Alter session set query_rewrite_enabled = TRUE;

and within the materialized view itself by including the ENABLE QUERY REWRITE clause while creating the view, or by altering an existing materialized view to add it.

The ON PREBUILT TABLE clause tells the database to use an existing table segment, which must have the same name as the materialized view and support the same column structure as the query.


Check Privileges

Check the user who will own the materialized views has the correct privileges. At minimum they will require the CREATE MATERIALIZED VIEW privilege. If they are creating materialized views using database links, you may want to grant them CREATE DATABASE LINK privilege also.


Create Materialized View

Connect to the materialized view owner and create the database link and the materialized view itself.


Alternatively, we could have used a prebuilt table, as shown below.

Remember to gather stats after building the materialized view.


About Avinav Chadha

I have been supporting and managing oracle databases for almost 4 years now.

Leave a Reply

Your email address will not be published. Required fields are marked *