SQL Statements (continued), 14 of 20

CREATE MATERIALIZED VIEW LOG / SNAPSHOT LOG

Syntax

physical_attributes_clause::=

storage_clause: See "storage_clause".

parallel_clause::=

partitioning_clauses: See "CREATE TABLE".

Purpose

To create a materialized view log. A materialized view log is a table associated with the master table of a materialized view. The terms snapshot and materialized view are synonymous. Both refer to a table that contains the results of a query of one or more tables, each of which may be located on the same or on a remote database.

When DML changes are made to the master table's data, Oracle stores rows describing those changes in the materialized view log and then uses the materialized view log to refresh materialized views based on the master table. This process is called a fast refresh. Without a materialized view log, Oracle must reexecute the materialized view query to refresh the materialized view. This process is called a complete refresh. Usually, a fast refresh takes less time than a complete refresh.

A materialized view log is located in the master database in the same schema as the master table. You need only a single materialized view log for a master table. Oracle can use this materialized view log to perform fast refreshes for all fast-refreshable materialized views based on the master table.

To fast refresh a materialized join view (a materialized view containing a join), you must create a materialized view log for each of its master tables.

See Also:

Oracle8i Data Warehousing Guide and Oracle8i Replication for information on materialized views in general.

"CREATE MATERIALIZED VIEW / SNAPSHOT" and Oracle8i Concepts for more information on materialized views.

"ALTER MATERIALIZED VIEW LOG / SNAPSHOT LOG" for information on modifying a materialized view log.

"DROP MATERIALIZED VIEW LOG / SNAPSHOT LOG" for information on dropping a materialized view log.

Oracle8i Concepts for information on using direct loader logs.

Prerequisites

The privileges required to create a materialized view log directly relate to the privileges necessary to create the underlying objects associated with a materialized view log.

If you own the master table, you can create an associated materialized view log if you have the CREATE TABLE privilege.
If you are creating a materialized view log for a table in another user's schema, you must have the CREATE ANY TABLE and COMMENT ANY TABLE privileges, as well as either the SELECT privilege for the master table or SELECT ANY TABLE.

In either case, the owner of the materialized view log must have sufficient quota in the tablespace intended to hold the materialized view log or must have the UNLIMITED TABLESPACE system privilege.

For detailed information about the prerequisites for creating a materialized view log, see Oracle8i Replication.

Keywords and Parameters

Examples

Primary Key Examples

The following statement creates a materialized view log on an employee table that records only primary key values:

CREATE MATERIALIZED VIEW LOG ON emp WITH PRIMARY KEY;

Oracle can use this materialized view log to perform a fast refresh on any simple primary key materialized view subsequently created on the EMP table.

The following statement also creates a materialized view log that record only the primary keys of updated rows:

CREATE MATERIALIZED VIEW LOG ON emp 
   PCTFREE 5 
   TABLESPACE users 
   STORAGE (INITIAL 10K NEXT 10K);

ROWID Example

The following statement creates a materialized view log that records both the primary keys and the rowids of updated rows:

CREATE MATERIALIZED VIEW LOG ON sales WITH ROWID, PRIMARY KEY;

Filter Column Example

The following statement creates a materialized view log that records primary keys and updates to the filter column ZIP:

CREATE MATERIALIZED VIEW LOG ON address WITH (zip);

NEW VALUES Example

The following example creates a master table, then creates a materialized view log that specifies INCLUDING NEW VALUES:

CREATE TABLE agg
   (u NUMBER, a NUMBER, b NUMBER, c NUMBER, d NUMBER);

CREATE MATERIALIZED VIEW LOG ON agg 
   WITH ROWID (u,a,b,c,d) 
   INCLUDING NEW VALUES;

You could create the following materialized aggregate view to use the AGG log:

CREATE MATERIALIZED VIEW sn0 
   REFRESH FAST ON COMMIT
   AS SELECT SUM(b+c), COUNT(*), a, d, COUNT(b+c) 
         FROM agg 
         GROUP BY a,d;

This materialized view is eligible for fast refresh because the log it uses includes both old and new values.

schema	is the schema containing the materialized view log's master table. If you omit schema, Oracle assumes the master table is contained in your own schema. Oracle creates the materialized view log in the schema of its master table. You cannot create a materialized view log for a table in the schema of the user `SYS`.
table	is the name of the master table for which the materialized view log is to be created. You cannot create a materialized view log for a view.
*physical_attributes_clause*	establishes values for physical and storage characteristics for the materialized view log. See the descriptions of these parameters in "CREATE TABLE" and "storage_clause".
`TABLESPACE`	specifies the tablespace in which the materialized view log is to be created. If you omit this clause, Oracle creates the materialized view log in the default tablespace of the owner of the materialized view log's schema.
`LOGGING \| NOLOGGING`	establishes the logging characteristics for the materialized view log. For a description of logging characteristics, see "CREATE TABLE".
`CACHE \| NOCACHE`	For data that will be accessed frequently, `CACHE` specifies that the blocks retrieved for this log 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: "ALTER TABLE" for information about specifying `CACHE` or `NOCACHE`.
*parallel_clause*	specifies whether parallel operations will be supported for the materialized view log. For additional information, see the Notes to the parallel_clause of "CREATE TABLE".
	`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.
*partitioning_clauses*	specifies that the materialized view log is partitioned on specified ranges of values or on a hash function. Partitioning of materialized view logs is the same as partitioning of tables, as described in "CREATE TABLE".
`WITH`	specifies whether the materialized view log should record the primary key, the rowid, or both the primary key and rowid when rows in the master are updated.
	This clause also specifies whether the materialized view log records filter columns, which are non-primary-key columns referenced by subquery materialized views.
	`PRIMARY KEY`	specifies that the primary key of all rows updated should be recorded in the materialized view log. The primary key of updated rows in the master table must be recorded in the materialized view log.
	`ROWID`	specifies that the rowid of all rows updated should be recorded in the materialized view log. The rowid must be recorded in the materialized view log.
	filter_column	is a comma-separated list that specifies the filter columns to be recorded in the materialized view log. For fast-refreshable primary-key materialized views defined with subqueries, all filter columns referenced by the defining subquery must be recorded in the materialized view log.
	Oracle records the primary key of all rows updated in the master by default.
`NEW VALUES`	specifies whether Oracle saves both old and new values in the materialized view log.
	`INCLUDING`	saves both new and old values in the log. If this log is for a table on which you have a single-table materialized aggregate view, and if you want the materialized view to be eligible for fast refresh, you must specify `INCLUDING`.
	`EXCLUDING`	disables the recording of new values in the log. This is the default. You can use this clause to avoid the overhead of recording new values. However, do not use this clause if you have a fast-refreshable single-table materialized aggregate view defined on this table.