<\/p>\n
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.<\/p>\n
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.<\/p>\n
-- Normal\r\nCREATE MATERIALIZED VIEW view-name\r\nBUILD [IMMEDIATE | DEFERRED]\r\nREFRESH [FAST | COMPLETE | FORCE ]\r\nON [COMMIT | DEMAND ]\r\n[[ENABLE | DISABLE] QUERY REWRITE]\r\nAS\r\nSELECT ...;<\/pre>\n-- Pre-Built\r\nCREATE MATERIALIZED VIEW view-name\r\nON PREBUILT TABLE\r\nREFRESH [FAST | COMPLETE | FORCE ]\r\nON [COMMIT | DEMAND ]\r\n[[ENABLE | DISABLE] QUERY REWRITE]\r\nAS\r\nSELECT ...;<\/pre>\n<\/p>\n
The BUILD clause options are shown below.<\/strong>
\n\u2022 IMMEDIATE : The materialized view is populated immediately.
\n\u2022 DEFERRED : The materialized view is populated on the first requested refresh.
\nThe following refresh types are available. <\/strong>
\n\u2022 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.
\n\u2022 COMPLETE : The table segment supporting the materialized view is truncated and repopulated completely using the associated query.
\n\u2022 FORCE : A fast refresh is attempted. If one is not possible a complete refresh is performed.
\nA refresh can be triggered in one of two ways.<\/strong>
\n\u2022 ON COMMIT : The refresh is triggered by a committed data change in one of the dependent tables.
\n\u2022 ON DEMAND : The refresh is initiated by a manual request or a scheduled task.<\/p>\nThe QUERY REWRITE <\/strong> 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.<\/p>\n
This trick, called Query Rewrite, only works with SELECT statements. However, those statements may be hidden away in a CREATE TABLE \u2026 AS SELECT statement, or an INSERT INTO \u2026 SELECT statement, or they may be squirrelled away in any type of subquery or sub-clause.<\/p>\n
Additionally, Query Rewrite must be enabled at the initialization parameter level as follows:<\/p>\n
Alter session set query_rewrite_enabled = TRUE;<\/p>\n
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.<\/p>\n
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.<\/p>\n
<\/p>\n
Check Privileges<\/h3>\n
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.<\/p>\n
CONNECT sys@db2\r\n\r\nGRANT CREATE MATERIALIZED VIEW TO scott;\r\nGRANT CREATE DATABASE LINK TO scott;<\/pre>\n<\/p>\n
Create Materialized View<\/h3>\n
Connect to the materialized view owner and create the database link and the materialized view itself.<\/p>\n
CONNECT scott\/tiger@db2\r\n\r\nCREATE DATABASE LINK DB1.WORLD CONNECT TO scott IDENTIFIED BY tiger USING 'DB1.WORLD';\r\n\r\nCREATE MATERIALIZED VIEW emp_mv\r\nBUILD IMMEDIATE \r\nREFRESH FORCE\r\nON DEMAND\r\nAS\r\nSELECT * FROM emp@db1.world;<\/pre>\n<\/p>\n
Alternatively, we could have used a prebuilt table, as shown below.<\/p>\n
-- Create the table first. This could be populated\r\n-- using an export\/import.\r\n\r\nCREATE TABLE emp_mv AS\r\nSELECT * FROM emp@db1.world;\r\n\r\n-- Build the materialized view using the existing table segment.\r\nCREATE MATERIALIZED VIEW emp_mv\r\nREFRESH FORCE\r\nON DEMAND\r\nON PREBUILT TABLE\r\nAS\r\nSELECT * FROM emp@db1.world;<\/pre>\n\nRemember to gather stats after building the materialized view.<\/p>\n
BEGIN\r\n DBMS_STATS.gather_table_stats(\r\n ownname => 'SCOTT',\r\n tabname => 'EMP_MV');\r\nEND;\r\n\/<\/pre>\n<\/div>\n