Oracle8i SQL Reference
Release 2 (8.1.6)

A76989-01

Library

Product

Contents

Index

Prev Up Next

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:

 

Prerequisites

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

To create a materialized view in your own schema:

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

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

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:

 

Keywords and Parameters

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:

 

 

 

 

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:

 

 

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:

 

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:

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; 



Prev Up Next
Oracle
Copyright © 1999 Oracle Corporation.

All Rights Reserved.

Library

Product

Contents

Index