CREATE MATERIALIZED VIEW

Purpose

Use the CREATE MATERIALIZED VIEW statement to create a materialized view. The terms snapshot and materialized view are synonymous in Oracle documentation. This reference uses "materialized view" for consistency. Both refer to a database object that contains the results of a query of one or more tables.

The tables in the query are called master tables (a replication term) or detail tables (a data warehouse 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, and subquery materialized views.

For data warehousing purposes, the materialized views commonly created are materialized aggregate views, single-table materialized aggregate views, and materialized join views. All three types of materialized views can be used by query rewrite, an optimization technique that transforms a user request written in terms of master tables into a semantically equivalent request that includes one or more materialized view. In a data warehousing environment, all master tables must be local.

See Also: Oracle8i Replication for information on the types of materialized views used to support replication Oracle8i Data Warehousing Guide for information on the types of materialized views used to support data warehousing

Prerequisites

The privileges required to create a materialized view should be granted directly.

To create a materialized view in your own schema:

• You must have been granted either the CREATE MATERIALIZED VIEW or CREATE SNAPSHOT system privilege and either the CREATE TABLE or CREATE ANY TABLE system privilege.

• You must also have access to any master tables of the materialized view that you do not own, either through a SELECT object privilege on each of the tables or through the SELECT ANY TABLE system privilege.

To create a materialized view in another user's schema:

• You must have the CREATE ANY MATERIALIZED VIEW or CREATE ANY SNAPSHOT system privilege and access to any master tables of the materialized view that you do not own, either through a SELECT object privilege on each of the tables or through the SELECT ANY TABLE system privilege.

• The owner or the materialized view must have the CREATE TABLE system privilege. The owner must also have access to any master tables of the materialized view that the schema owner does not own and to any materialized view logs defined on those master tables, either through a SELECT object privilege on each of the tables or through the SELECT ANY TABLE system privilege.

To create the materialized view with query rewrite enabled, in addition to the preceding privileges:

• The owner of the master tables must have the QUERY REWRITE system privilege.

• If you are not the owner of the master tables, you must have the GLOBAL QUERY REWRITE system privilege.

• If the schema owner does not own the master tables, then the schema owner must have the GLOBAL QUERY REWRITE privilege.

The user whose schema contains the materialized view must have sufficient quota in the target tablespace to store the materialized view's master table and index, or must have the UNLIMITED TABLESPACE system privilege.

When you create a materialized view, Oracle creates one internal table and at least one index, and may create one view, all in the schema of the materialized view. Oracle uses these objects to maintain the materialized view's data. You must have the privileges necessary to create these objects.

See Also: CREATE TABLE, CREATE VIEW, and CREATE INDEX for information on these privileges Oracle8i Replication for information about the prerequisites that apply to creating replication materialized views Oracle8i Data Warehousing Guide for information about the prerequisites that apply to creating data warehousing materialized views

Syntax

refresh_clause::=

segment_attributes_clause: See CREATE TABLE.

parallel_clause::=

build_clause::=

subquery: See SELECT and subquery.

LOB_storage_clause: See CREATE TABLE.

partitioning_clauses: See CREATE TABLE.

Keywords and Parameters

schema

Specify the schema to contain the materialized view. If you omit schema, Oracle creates the materialized view in your schema.

materialized_view

Specify the name of the materialized view to be created. Oracle generates names for the table and indexes used to maintain the materialized view by adding a prefix or suffix to the materialized view name.

segment_attributes_clause

Use the segment_attributes_clause to establish values for the PCTFREE, PCTUSED, INITRANS, and MAXTRANS parameters (or, when used in the USING INDEX clause, for the INITRANS and MAXTRANS parameters only), the storage characteristics for the materialized view, to assign a tablespace, and to specify whether logging is to occur.

See Also: CREATE TABLE for information on the PCTFREE, PCTUSED, INITRANS, and MAXTRANS, TABLESPACE, and LOGGING|NOLOGGING parameters for information about storage characteristics

TABLESPACE

Specify the tablespace in which the materialized view is to be created. If you omit this clause, Oracle creates the materialized view in the default tablespace of the owner of the materialized view's schema.

LOB_storage_clause

The LOB_storage_clause lets you specify the LOB storage characteristics.

See Also: CREATE TABLE for detailed information about specifying the parameters of this clause

LOGGING | NOLOGGING

Specify LOGGING or NOLOGGING to establish the logging characteristics for the materialized view.

See Also: CREATE TABLE for a description of logging characteristics

CACHE | NOCACHE

For data that will be accessed frequently, CACHE specifies that the blocks retrieved for this table are placed at the most recently used end of the LRU list in the buffer cache when a full table scan is performed. This attribute is useful for small lookup tables. NOCACHE specifies that the blocks are placed at the least recently used end of the LRU list.

Note: NOCACHE has no effect on materialized views for which you specify KEEP in the storage_clause.

See Also: CREATE TABLE for information about specifying CACHE or NOCACHE

CLUSTER

Use the CLUSTER clause to create the materialized view as part of the specified cluster. A clustered materialized view uses the cluster's space allocation. Therefore, do not use the physical_attributes_clause or the TABLESPACE clause with the CLUSTER clause.

partitioning_clauses

The partitioning_clauses let you specify that the materialized view is partitioned on specified ranges of values or on a hash function. Partitioning of materialized views is the same as partitioning tables.

See Also: CREATE TABLE

parallel_clause

The parallel_clause lets you indicate whether parallel operations will be supported for the materialized view and sets the default degree of parallelism for queries and DML on the materialized view after creation.

Note: The syntax of the parallel_clause supersedes syntax appearing in earlier releases of Oracle. Superseded syntax is still supported for backward compatibility, but may result in slightly different behavior.

NOPARALLEL	Specify NOPARALLEL for serial execution. This is the default.
PARALLEL	Specify PARALLEL if you want Oracle to select a degree of parallelism equal to the number of CPUs available on all participating instances times the value of the PARALLEL_THREADS_PER_CPU initialization parameter.
PARALLEL integer	Specification of integer indicates the degree of parallelism, which is the number of parallel threads used in the parallel operation. Each parallel thread may use one or two parallel execution servers. Normally Oracle calculates the optimum degree of parallelism, so it is not necessary for you to specify integer.

build_clause

The build_clause lets you specify when to populate the materialized view.

IMMEDIATE	Specify IMMEDIATE to indicate that the materialized view is populated immediately. This is the default.
DEFERRED	Specify DEFERRED to indicate that the materialized view will be populated by the next REFRESH operation. The first (deferred) refresh must always be a complete refresh. Until then, the materialized view has a staleness value of UNUSABLE, so it cannot be used for query rewrite.

ON PREBUILT TABLE

The ON PREBUILT TABLE clause lets you register an existing table as a preinitialized materialized view. This is particularly useful for registering large materialized views in a data warehousing environment. The table must have the same name and be in the same schema as the resulting materialized view.

If the materialized view is dropped, the preexisting table reverts to its identity as a table.

Caution: This clause assumes that the table object reflects the materialization of a subquery. Oracle Corporation strongly recommends that you ensure that this assumption is true in order to ensure that the materialized view correctly reflects the data in its master tables.

Restrictions:

• Each column alias in subquery must correspond to a column in table_name, and corresponding columns must have matching datatypes.

• If you specify this clause, you cannot specify a NOT NULL constraint for any column that is unmanaged (that is, not referenced in subquery) unless you also specify a default value for that column.

  WITH REDUCED PRECISION	Specify WITH REDUCED PRECISION to authorize the loss of precision that will result if the precision of the table or materialized view columns do not exactly match the precision returned by subquery.
  WITHOUT REDUCED PRECISION	Specify WITHOUT REDUCED PRECISION to require that the precision of the table or materialized view columns match exactly the precision returned by subquery, or the create operation will fail. This is the default.

USING INDEX

The USING INDEX clause lets you establish the value of INITRANS, MAXTRANS, and STORAGE parameters for the index Oracle uses to maintain the materialized view's data. If USING INDEX is not specified, then default values are used for the index.

Restriction: You cannot specify the PCTUSED or PCTFREE parameters in this clause.

refresh_clause

Use the refresh_clause to specify the default methods, modes, and times for Oracle to refresh the materialized view. If a materialized view's master tables are modified, the data in a materialized view must be updated to make the materialized view accurately reflect the data currently in its master tables. This clause lets you schedule the times and specify the method and mode for Oracle to refresh the materialized view.

Note: This clause only sets the default refresh options. For instructions on actually implementing the refresh, refer to Oracle8i Replication and Oracle8i Data Warehousing Guide.

FAST	Specify FAST to indicate the incremental refresh method, which performs the refresh according to the changes that have occurred to the master tables. The changes are stored either in the materialized view log associated with the master table (for conventional DML changes) or in the direct loader log (for direct-load INSERT operations).
	You can create a materialized aggregate view even if you have not yet created materialized view logs for the underlying master tables. However, if you are creating any other type of materialized view, the CREATE statement will fail unless those materialized view logs already exist. (Oracle creates the direct loader log automatically when a direct-load INSERT takes place. No user intervention is needed.)
	After create time, Oracle will perform the fast refresh for conventional DML only if the appropriate materialized view logs exist. For both conventional DML changes and for direct-path loads, other conditions may restrict the eligibility of a materialized view for fast refresh. Materialized views are not eligible for fast refresh if the defining query contains an analytic function. See Also: - Oracle8i Replication for restrictions on fast refresh in replication environments - Oracle8i Data Warehousing Guide for restrictions on fast refresh in data warehouse environments - "Analytic Functions"
COMPLETE	Specify COMPLETE to indicate the complete refresh method, which is implemented by executing the materialized view's defining query. If you request a complete refresh, Oracle performs a complete refresh even if a fast refresh is possible.
FORCE	Specify FORCE to indicate that when a refresh occurs, Oracle will perform a fast refresh if one is possible or a complete refresh otherwise. If you do not specify a refresh method (FAST, COMPLETE, or FORCE), FORCE is the default.
ON COMMIT	Specify ON COMMIT to indicate that a fast refresh is to occur whenever Oracle commits a transaction that operates on a master table of the materialized view. Restriction: This clause is supported only for materialized join views and single-table materialized aggregate views. See Also: Oracle8i Replication and Oracle8i Data Warehousing Guide
ON DEMAND	Specify ON DEMAND to indicate that the materialized view will be refreshed on demand by calling one of the three DBMS_MVIEW refresh procedures. If you omit both ON COMMIT and ON DEMAND, ON DEMAND is the default. See Also: - Oracle8i Supplied PL/SQL Packages Reference for information on these procedures - Oracle8i Data Warehousing Guide on the types of materialized views you can create by specifying REFRESH ON DEMAND
If you specify ON COMMIT or ON DEMAND, you cannot also specify START WITH or NEXT.
START WITH	Specify a date expression for the first automatic refresh time.
NEXT	Specify a date expression for calculating the interval between automatic refreshes.
Both the START WITH and NEXT values must evaluate to a time in the future. If you omit the START WITH value, Oracle determines the first automatic refresh time by evaluating the NEXT expression with respect to the creation time of the materialized view. If you specify a START WITH value but omit the NEXT value, Oracle refreshes the materialized view only once. If you omit both the START WITH and NEXT values, or if you omit the refresh_clause entirely, Oracle does not automatically refresh the materialized view.
WITH PRIMARY KEY	Specify WITH PRIMARY KEY to indicate that a primary key materialized view is to be created. This is the default, and should be used in all cases except those described for WITH ROWID. Primary key materialized views allow materialized view master tables to be reorganized without affecting the materialized view's ability to continue to fast refresh. The master table must contain an enabled primary key constraint. See Also: Oracle8i Replication for detailed information about primary key materialized views
WITH ROWID	Specify WITH ROWID to indicate that a rowid materialized view is to be created. Rowid materialized views provide compatibility with master tables in releases of Oracle prior to 8.0.
	You can also use rowid materialized views if the materialized view does not include all primary key columns of the master tables. Rowid materialized views must be based on a single remote table and cannot contain any of the following: Distinct or aggregate functions GROUP BY or CONNECT BY clauses Subqueries Joins Set operations Rowid materialized views cannot be fast refreshed after a master table reorganization until a complete refresh has been performed.
USING ROLLBACK SEGMENT rollback_segment	Specify the remote rollback segment to be used during materialized view refresh, where rollback_segment is the name of the rollback segment to be used.
	DEFAULT specifies that Oracle will choose automatically which rollback segment to use. If you specify DEFAULT, you cannot specify rollback_segment. DEFAULT is most useful when modifying a materialized view. See Also: ALTER MATERIALIZED VIEW
	MASTER specifies the remote rollback segment to be used at the remote master site for the individual materialized view.
	LOCAL specifies the remote rollback segment to be used for the local refresh group that contains the materialized view. See Also: Oracle8i Replication for information on specifying the local materialized view rollback segment using the DBMS_REFRESH package.
	If you do not specify MASTER or LOCAL, Oracle uses LOCAL by default. If you do not specify rollback_segment, Oracle automatically chooses the rollback segment to be used. The master rollback segment is stored on a per-materialized-view basis and is validated during materialized view creation and refresh. If the materialized view is complex, the master rollback segment, if specified, is ignored.
NEVER REFRESH	Specify NEVER REFRESH to prevent the materialized view from being refreshed with any Oracle refresh mechanism or procedure. If you issue a REFRESH statement on the materialized view, Oracle returns an error.

FOR UPDATE

Specify FOR UPDATE to allow a subquery, primary key, or rowid materialized view to be updated. When used in conjunction with Advanced Replication, these updates will be propagated to the master.

See Also: Oracle8i Replication

QUERY REWRITE

The QUERY REWRITE clause lets you specify whether the materialized view is eligible to be used for query rewrite.

ENABLE	Specify ENABLE to enable the materialized view for query rewrite. See Also: Oracle8i Data Warehousing Guide for more information on query rewrite
	Notes: Query rewrite is disabled by default, so you must specify this clause to make materialized views eligible for query rewrite. Be sure to analyze the materialized view after you create it. Oracle needs the statistics generated by the ANALYZE operation to optimize query rewrite.
	Restrictions: You can enable query rewrite only if all user-defined functions in the materialized view are DETERMINISTIC. You can enable query rewrite only if expressions in the statement are repeatable. For example, you cannot include CURRENT_TIME or USER, sequence values (such as the CURRVAL or NEXTVAL pseudocolumns), or the SAMPLE clause (which may sample different rows as the contents of the materialized view change). See Also: CREATE FUNCTION and Oracle8i Data Warehousing Guide
DISABLE	Specify DISABLE to indicate that the materialized view is not eligible for use by query rewrite. However, a disabled materialized view can be refreshed.

AS subquery

Specify the materialized view's defining query. When you create the materialized view, Oracle executes this query and places the results in the materialized view. This query is any valid SQL query. However, not all queries are fast refreshable, nor are all queries eligible for query rewrite.

Notes on the materialized view subquery:

• Oracle does not execute the query immediately if you specify BUILD DEFERRED.

• Oracle recommends that you qualify each table and view in the FROM clause of the materialized view query with the schema containing it.

  See Also: the AS subquery clause of CREATE TABLE for some additional caveats

Restrictions on the materialized view subquery:

• A materialized view query can select from tables or views owned by the user SYS, but you cannot enable QUERY REWRITE on such a materialized view.

• You cannot refer to a user-defined type anywhere in the materialized view query.

• Materialized join views and materialized aggregate views with a GROUP BY clause cannot select from an index-organized table.

• Materialized views cannot contain columns of datatype LONG.

• If the subquery refers to a temporary table, you cannot create a materialized view log for this materialized view, nor can you specify the QUERY REWRITE clause of CREATE MATERIALIZED VIEW or ALTER MATERIALIZED VIEW.

• If the FROM list of the materialized view references another materialized view, you must control the refresh order of the materialized views manually. That is, you must refresh the materialized view depended upon and then the dependent materialized view in order to maintain freshness.

If you are creating a materialized view enabled for query rewrite:

• The subquery cannot contain (either directly or through a view) references to ROWNUM, USER, SYSDATE, remote tables, sequences, or PL/SQL functions that write or read database or package state.

• The materialized view and the master tables of the materialized view must be local.

If you want the materialized view to be eligible for fast refresh using a materialized view log, some additional restrictions may apply.

See Also: Oracle8i Data Warehousing Guide for more information on restrictions relating to data warehousing Oracle8i Replication for more information on restrictions relating to replication

Examples

Materialized Aggregate View Examples

The following statement creates and populates a materialized aggregate view and specifies the default refresh method, mode, and time:

CREATE MATERIALIZED VIEW mv1 REFRESH FAST ON COMMIT
   BUILD IMMEDIATE
   AS SELECT t.month, p.prod_name, SUM(f.sales) AS sum_sales
      FROM time t, product p, fact f
      WHERE f.curDate = t.curDate AND f.item = p.item
      GROUP BY t.month, p.prod_name;

The following statement creates and populates the materialized aggregate view sales_by_month_by_state. The materialized view will be populated with data as soon as the statement executes successfully. By default, subsequent refreshes will be accomplished by reexecuting the materialized view's query:

CREATE MATERIALIZED VIEW sales_by_month_by_state
   TABLESPACE my_ts PARALLEL (10) 
   ENABLE QUERY REWRITE
   BUILD IMMEDIATE
   REFRESH COMPLETE
   AS SELECT t.month, g.state, SUM(f.sales) AS sum_sales
      FROM fact f, time t, geog g
      WHERE f.cur_date = t.cur_date AND f.city_id = g.city_id
      GROUP BY month, state;

Prebuilt Materialized View Example

The following statement creates a materialized aggregate view for the preexisting summary table, sales_sum_table:

CREATE TABLE sales_sum_table
   (month DATE, state VARCHAR2(25), sales NUMBER);

CREATE MATERIALIZED VIEW sales_sum_table
   ON PREBUILT TABLE 
   ENABLE QUERY REWRITE
   AS SELECT t.month, g.state, SUM(f.sales) AS sum_sales
      FROM fact f, time t, geog g
      WHERE f.cur_date = t.cur_date AND f.city_id = g.city_id
      GROUP BY month, state;

In this example, the materialized view has the same name as the prebuilt table and also has the same number of columns with the same datatypes as the prebuilt table.

Materialized Join View Example

The following statement creates the materialized join view mjv:

CREATE MATERIALIZED VIEW mjv
   REFRESH FAST
      AS SELECT l.rowid as l_rid, l.pk, l.ofk, l.c1, l.c2,
         o.rowid as o_rid, o.pk, o.cfk, o.c1, o.c2,
         c.rowid as c_rid, c.pd, c.c1, c.c2
      FROM l, o, c
      WHERE l.ofk = o.pk(+) AND o.ofk = c.pk(+);

Subquery Materialized View Example

The following statement creates a subquery materialized view based on the orders and customers tables in the sales schema at a remote database:

CREATE MATERIALIZED VIEW sales.orders FOR UPDATE
   AS SELECT * FROM sales.orders@dbs1.acme.com o
   WHERE EXISTS
     (SELECT * FROM sales.customers@dbs1.acme.com c
      WHERE o.c_id = c.c_id);

Primary Key Example

The following statement creates the primary-key materialized view human_genome:

CREATE MATERIALIZED VIEW human_genome   
   REFRESH FAST START WITH SYSDATE NEXT  SYSDATE + 1/4096 
   WITH PRIMARY KEY 
   AS SELECT * FROM genome_catalog; 

Rowid Example

The following statement creates a rowid materialized view:

CREATE MATERIALIZED VIEW emp_data REFRESH WITH ROWID 

AS SELECT * FROM emp_table73; 

Periodic Refresh Example

The following statement creates the primary key materialized view emp_sf and populates it with data from scott's employee table in New York:

CREATE MATERIALIZED VIEW emp_sf 
   PCTFREE 5 PCTUSED 60 
   TABLESPACE users 
   STORAGE (INITIAL 50K NEXT 50K)
   REFRESH FAST NEXT sysdate + 7 
   AS SELECT * FROM scott.emp@ny; 

The statement does not include a START WITH parameter, so Oracle determines the first automatic refresh time by evaluating the NEXT value using the current SYSDATE. Provided that a materialized view log currently exists for the employee table in New York, Oracle performs a fast refresh of the materialized view every 7 days, beginning 7 days after the materialized view is created.

Because the materialized view conforms to the conditions for fast refresh, Oracle will perform a fast refresh. The above statement also establishes storage characteristics that Oracle uses to maintain the materialized view.

Automatic Refresh Times Example

The following statement creates the complex materialized view all_emps that queries the employee tables in Dallas and Baltimore:

CREATE MATERIALIZED VIEW all_emps 
   PCTFREE 5 PCTUSED 60 
   TABLESPACE users 
   STORAGE INITIAL 50K NEXT 50K 
   USING INDEX STORAGE (INITIAL 25K NEXT 25K)
   REFRESH START WITH ROUND(SYSDATE + 1) + 11/24 
   NEXT NEXT_DAY(TRUNC(SYSDATE, 'MONDAY') + 15/24 
   AS SELECT * FROM fran.emp@dallas 
         UNION
      SELECT * FROM marco.emp@balt; 

Oracle automatically refreshes this materialized view tomorrow at 11:00 am and subsequently every Monday at 3:00 pm. The default refresh method is FORCE. all_emps contains a UNION, which is not supported for fast refresh, so Oracle will automatically perform a complete refresh.

The above statement also establishes storage characteristics for both the materialized view and the index that Oracle uses to maintain it:

• The first storage_clause establishes the sizes of the first and second extents of the materialized view as 50 kilobytes each.

• The second storage_clause (appearing with the USING INDEX clause) establishes the sizes of the first and second extents of the index as 25 kilobytes each.

Rollback Segment Example

The following statement creates the primary key materialized view sales_emp with rollback segment master_seg at the remote master and rollback segment snap_seg for the local refresh group that contains the materialized view:

CREATE MATERIALIZED VIEW sales_emp 
   REFRESH FAST START WITH SYSDATE NEXT SYSDATE + 7
   USING MASTER ROLLBACK SEGMENT master_seg 
   LOCAL ROLLBACK SEGMENT snap_seg  
   AS SELECT * FROM bar;  

The following statement is incorrect and generates an error because it specifies a segment name with a DEFAULT rollback segment:

CREATE MATERIALIZED VIEW bogus 
   REFRESH FAST START WITH SYSDATE NEXT SYSDATE + 7
   USING DEFAULT ROLLBACK SEGMENT snap_seg 
   AS SELECT * FROM faux;