Materialized Views in Oracle Database

Avinav Chadha

6 years ago

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.

-- Normal
CREATE MATERIALIZED VIEW view-name
BUILD [IMMEDIATE | DEFERRED]
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[[ENABLE | DISABLE] QUERY REWRITE]
AS
SELECT ...;

-- Normal

CREATE MATERIALIZED VIEW view-name

BUILD [IMMEDIATE | DEFERRED]

REFRESH [FAST | COMPLETE | FORCE ]

ON [COMMIT | DEMAND ]

[[ENABLE | DISABLE] QUERY REWRITE]

SELECT ...;

-- Pre-Built
CREATE MATERIALIZED VIEW view-name
ON PREBUILT TABLE
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[[ENABLE | DISABLE] QUERY REWRITE]
AS
SELECT ...;

-- Pre-Built

CREATE MATERIALIZED VIEW view-name

ON PREBUILT TABLE

REFRESH [FAST | COMPLETE | FORCE ]

ON [COMMIT | DEMAND ]

[[ENABLE | DISABLE] QUERY REWRITE]

SELECT ...;

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.

CONNECT sys@db2

GRANT CREATE MATERIALIZED VIEW TO scott;
GRANT CREATE DATABASE LINK TO scott;

CONNECT sys@db2

GRANT CREATE MATERIALIZED VIEW TO scott;

GRANT CREATE DATABASE LINK TO scott;

Create Materialized View

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

CONNECT scott/tiger@db2

CREATE DATABASE LINK DB1.WORLD CONNECT TO scott IDENTIFIED BY tiger USING 'DB1.WORLD';

CREATE MATERIALIZED VIEW emp_mv
BUILD IMMEDIATE 
REFRESH FORCE
ON DEMAND
AS
SELECT * FROM emp@db1.world;

CONNECT scott/tiger@db2

CREATE DATABASE LINK DB1.WORLD CONNECT TO scott IDENTIFIED BY tiger USING 'DB1.WORLD';

CREATE MATERIALIZED VIEW emp_mv

BUILD IMMEDIATE

REFRESH FORCE

ON DEMAND

SELECT * FROM emp@db1.world;

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

-- Create the table first. This could be populated
-- using an export/import.

CREATE TABLE emp_mv AS
SELECT * FROM emp@db1.world;

-- Build the materialized view using the existing table segment.
CREATE MATERIALIZED VIEW emp_mv
REFRESH FORCE
ON DEMAND
ON PREBUILT TABLE
AS
SELECT * FROM emp@db1.world;

-- Create the table first. This could be populated

-- using an export/import.

CREATE TABLE emp_mv AS

SELECT * FROM emp@db1.world;

-- Build the materialized view using the existing table segment.

CREATE MATERIALIZED VIEW emp_mv

REFRESH FORCE

ON DEMAND

ON PREBUILT TABLE

SELECT * FROM emp@db1.world;

Remember to gather stats after building the materialized view.

BEGIN
  DBMS_STATS.gather_table_stats(
    ownname => 'SCOTT',
    tabname => 'EMP_MV');
END;
/

BEGIN

DBMS_STATS.gather_table_stats(

ownname => 'SCOTT',

tabname => 'EMP_MV');

END;