Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
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.
1 2 3 4 5 6 7 8 | -- Normal CREATE MATERIALIZED VIEW view-name BUILD [IMMEDIATE | DEFERRED] REFRESH [FAST | COMPLETE | FORCE ] ON [COMMIT | DEMAND ] [[ENABLE | DISABLE] QUERY REWRITE] AS SELECT ...; |
1 2 3 4 5 6 7 8 | -- Pre-Built CREATE MATERIALIZED VIEW view-name ON PREBUILT TABLE REFRESH [FAST | COMPLETE | FORCE ] ON [COMMIT | DEMAND ] [[ENABLE | DISABLE] QUERY REWRITE] AS 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.
1 2 3 4 | 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.
1 2 3 4 5 6 7 8 9 10 | 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; |
Alternatively, we could have used a prebuilt table, as shown below.
1 2 3 4 5 6 7 8 9 10 11 12 13 | -- 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; |
Remember to gather stats after building the materialized view.
1 2 3 4 5 6 | BEGIN DBMS_STATS.gather_table_stats( ownname => 'SCOTT', tabname => 'EMP_MV'); END; / |
