Skip Headers

Oracle9i SQL Reference
Release 2 (9.2)

Part Number A96540-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page Go to next page
View PDF

CREATE MATERIALIZED VIEW LOG

Purpose

Use the CREATE MATERIALIZED VIEW LOG statement to create a materialized view log, which is a table associated with the master table of a materialized view.


Note:

The keyword SNAPSHOT is supported in place of MATERIALIZED VIEW for backward compatibility.


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 an incremental or 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. A master table can have only one materialized view log defined on it. 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 the tables referenced by the materialized view.

See Also:

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.

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.

See Also:

Oracle9i Data Warehousing Guide for more information about the prerequisites for creating a materialized view log

Syntax

create_materialized_vw_log::=

Text description of statements_620.gif follows
Text description of create_materialized_vw_log


(physical_attributes_clause::=, logging_clause::=, parallel_clause::=, table_partitioning_clauses (in CREATE TABLE), new_values_clause::=)

physical_attributes_clause::=

Text description of statements_621.gif follows
Text description of physical_attributes_clause


(storage_clause)

logging_clause::=

Text description of statements_642.gif follows
Text description of logging_clause


parallel_clause::=

Text description of statements_62.gif follows
Text description of parallel_clause


new_values_clause::=

Text description of statements_650.gif follows
Text description of new_values_clause


Semantics

schema

Specify 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

Specify the name of the master table for which the materialized view log is to be created.

Restriction on Master Tables of Materialized View Logs

You cannot create a materialized view log for a temporary table or for a view.

See Also:

"Creating a Materialized View Log: Examples"

physical_attributes_clause

Use the physical_attributes_clause to establish values for physical and storage characteristics for the materialized view log.

See Also:

TABLESPACE Clause

Specify 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 schema of the materialized view log.

logging_clause

Specify either LOGGING or NOLOGGING to establish the logging characteristics for the materialized view log. The default is the logging characteristic of the tablespace in which the materialized view log resides.

See Also:

logging_clause for a full description of this clause

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 least recently used (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. The default is NOCACHE.


Note:

NOCACHE has no effect on materialized view logs for which you specify KEEP in the storage_clause.


See Also:

CREATE TABLE for information about specifying CACHE or NOCACHE

parallel_clause

The parallel_clause lets you indicate whether parallel operations will be supported for the materialized view log.


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 than that documented.


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.

See Also:

"Notes on the parallel_clause" for CREATE TABLE

table_partitioning_clauses

Use the table_partitioning_clauses to indicate 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.

See Also:

table_partitioning_clauses of CREATE TABLE

WITH Clause

Use the WITH clause to indicate whether the materialized view log should record the primary key, the rowid, object ID, or a combination of these row identifiers when rows in the master are changed. You can also use this clause to add a sequence to the materialized view log to provide additional ordering information for its records.

This clause also specifies whether the materialized view log records additional columns that might be referenced as filter columns (non-primary-key columns referenced by subquery materialized views) or join columns (non-primary-key columns that define a join in the subquery WHERE clause).

If you omit this clause, or if you specify the clause without PRIMARY KEY, ROWID, or OBJECT ID, then Oracle stores primary key values by default. However, Oracle does not store primary key values implicitly if you specify only OBJECT ID or ROWID at create time. A primary key log, created either explicitly or by default, performs additional checking on the primary key constraint.

OBJECT ID

Specify OBJECT ID to indicate that the system-generated or user-defined object identifier of every modified row should be recorded in the materialized view log.

Restriction on OBJECT ID

You can specify OBJECT ID only when creating a log on an object table, and you cannot specify it for storage tables.

PRIMARY KEY

Specify PRIMARY KEY to indicate that the primary key of all rows changed should be recorded in the materialized view log.

ROWID

Specify ROWID to indicate that the rowid of all rows changed should be recorded in the materialized view log.

SEQUENCE

Specify SEQUENCE to indicate that a sequence value providing additional ordering information should be recorded in the materialized view log. Sequence numbers are necessary to support fast refresh after some update scenarios.

See Also:

Oracle9i Data Warehousing Guide for more information on the use of sequence numbers in materialized view logs and for examples that use this clause

column

Specify the columns whose values you want to be recorded in the materialized view log for all rows that are changed. Typically these columns are filter columns (non-primary-key columns referenced by materialized views) and join columns (non-primary-key columns that define a join in the WHERE clause of the subquery).

Restrictions on the WITH Clause

NEW VALUES Clause

The NEW VALUES clause lets you indicate whether Oracle saves both old and new values in the materialized view log.

See Also:

"Including New Values in Materialized View Logs: Example"

INCLUDING

Specify INCLUDING to save 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

Specify EXCLUDING to disable 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.

Examples

Creating a Materialized View Log: Examples

The following statement creates a materialized view log on the oe.customers table that specifies physical and storage characteristics:

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

This materialized view log supports fast refresh for primary key materialized views only. The following statement creates another version of the materialized view log with the ROWID clause, which enables fast refresh for more types of materialized views:

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

This materialized view log makes fast refresh possible for rowid materialized views and for materialized join views. To provide for fast refresh of materialized aggregate views, you must also specify the SEQUENCE and INCLUDING NEW VALUES clauses, as shown in the next statement.

Specifying Filter Columns for Materialized View Logs: Example

The following statement creates a materialized view log on the sh.sales table, and is used in "Creating Materialized Aggregate Views: Example". It specifies as filter columns all of the columns of the table referenced in that materialized view.

CREATE MATERIALIZED VIEW LOG ON sales 
   WITH ROWID, SEQUENCE(amount_sold, time_id, prod_id)
   INCLUDNG NEW VALUES; 
Specifying Join Columns for Materialized View Logs: Example

The following statement creates a materialized view log on the order_items table of the sample oe schema. The log records primary keys and product_id, which is used as a join column in "Creating a Fast Refreshable Materialized View: Example".

CREATE MATERIALIZED VIEW LOG ON order_items WITH (product_id);
Including New Values in Materialized View Logs: Example

The following example creates a materialized view log on the oe.product_information table that specifies INCLUDING NEW VALUES:

CREATE MATERIALIZED VIEW LOG ON product_information 
   WITH ROWID, (list_price, min_price, category_id) 
   INCLUDING NEW VALUES;

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

CREATE MATERIALIZED VIEW products_mv 
   REFRESH FAST ON COMMIT
   AS SELECT SUM(list_price - min_price), category_id
         FROM product_information 
         GROUP BY category_id;

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