SQL Statements (continued), 13 of 20

CREATE MATERIALIZED VIEW / SNAPSHOT

Syntax

refresh_clause::=

physical_attributes_clause: See "CREATE TABLE".

parallel_clause::=

subquery: See "SELECT and Subqueries".

LOB_storage_clause: See "CREATE TABLE".

partitioning_clauses: See "CREATE TABLE".

Purpose

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.

Keywords and Parameters

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;

schema	is the schema to contain the materialized view. If you omit schema, Oracle creates the materialized view in your schema.
materialized_view / snapshot	is 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.
*physical_attributes_clause*	establishes values for the `PCTFREE`, `PCTUSED`, `INITRANS`, and `MAXTRANS` parameters (or, when used in the `USING` `INDEX` clause, for the `INITRANS` and `MAXTRANS` parameters only) and the storage characteristics for the materialized view.
	See Also: "CREATE TABLE" for information on the `PCTFREE`, `PCTUSED`, `INITRANS`, and `MAXTRANS` parameters "storage_clause" for information about storage characteristics
`TABLESPACE`	specifies 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*	specifies the LOB storage characteristics. See Also: "CREATE TABLE" for detailed information about specifying the parameters of this clause.
`CLUSTER`	creates 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.
`LOGGING \| NOLOGGING`	establishes 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. See Also: "CREATE TABLE" for information about specifying `CACHE` or `NOCACHE`.
*partitioning_clauses*	specifies 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, as described in "CREATE TABLE".
*parallel_clause*	indicates 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.
	`NOPARALLEL`	specifies serial execution. This is the default.
	`PARALLEL`	causes 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	specifies 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.
	See Also: The Notes to the parallel_clause of "CREATE TABLE" for additional information.
`BUILD`	specifies when to populate the materialized view.
	`IMMEDIATE`	specifies that the materialized view is populated immediately. This is the default.
	`DEFERRED`	specifies 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`	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`	lets you 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`	requires 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`	establishes 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*	specifies 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`	specifies 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 INSERTs).
		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. 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.
		Materialized views are not eligible for fast refresh if the defining query contains an analytic function. See Also: "Analytic Functions".
	`COMPLETE`	specifies 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`	specifies 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`	specifies 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`	specifies 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`	specifies a date expression for the first automatic refresh time.
	`NEXT`	specifies 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`	specifies 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`	specifies 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`	specifies 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. Note: `DEFAULT` is most useful when modifying a materialized view, as described in "ALTER MATERIALIZED VIEW / SNAPSHOT". `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`	prevents 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`	allows 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`	specifies whether the materialized view is eligible to be used for query rewrite.
	`ENABLE`	enables 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`	specifies that the materialized view is not eligible for use by query rewrite. However, a disabled materialized view can be refreshed.
`AS` subquery	specifies 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: 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. For some additional caveats, see the `AS` subquery clause of "CREATE TABLE".
	Restrictions: 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.