PRIMARY KEYВ Specify PRIMARY KEY to indicate that the primary key of all rows changed 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. 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. A primary key log, created either explicitly or by default, performs additional checking on the primary key constraint.

However, the database does not store primary key values implicitly if you specify only OBJECT ID or ROWID at create time.

If you omit this clause, or if you specify the clause without PRIMARY KEY, ROWID, or OBJECT ID, then the database stores primary key values by default. This clause also specifies whether the materialized view log records additional columns that might be referenced as filter columns, which are non-primary-key columns referenced by subquery materialized views, or join columns, which are non-primary-key columns that define a join in the subquery WHERE clause. You can also use this clause to add a sequence to the materialized view log to provide additional ordering information for its records. Use the WITH clause to indicate whether the materialized view log should record the primary key, rowid, object ID, or a combination of these row identifiers when rows in the master are changed. See Also: table_partitioning_clauses in the CREATE TABLE documentation Restriction on Master Tables of Materialized View LogsВ You cannot create a materialized view log for a temporary table or for a view.

Specify 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 table in the schema of the user SYS. Oracle Database creates the materialized view log in the schema of its master table. If you omit schema, then Oracle Database assumes the master table is contained in your own schema. Specify the schema containing the materialized view log master table. ( physical_attributes_clause::=, logging_clause::=, parallel_clause::=, table_partitioning_clauses ::= (in CREATE TABLE), new_values_clause::=)Дescription of the illustration physical_attributes_clause.gifДescription of the illustration logging_clause.gifДescription of the illustration parallel_clause.gifДescription of the illustration new_values_clause.gif See Also: Oracle Data Warehousing Guide for more information about the prerequisites for creating a materialized view logДescription of the illustration create_materialized_vw_log.gif 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. 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 system privileges, as well as either the SELECT object privilege on the master table or the SELECT ANY TABLE system privilege. If you own the master table, you can create an associated materialized view log if you have the CREATE TABLE privilege. 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. Oracle Database Concepts and Oracle Database Utilities for information on using direct loader logs To fast refresh a materialized join view, you must create a materialized view log for each of the tables referenced by the materialized view.Д¬REATE MATERIALIZED VIEW, ALTER MATERIALIZED VIEW, Oracle Database Concepts, Oracle Data Warehousing Guide, and Oracle Database Advanced Replication for information on materialized views in generalДЄLTER MATERIALIZED VIEW LOG for information on modifying a materialized view logДROP MATERIALIZED VIEW LOG for information on dropping a materialized view log Oracle Database can use this materialized view log to perform fast refreshes for all fast-refreshable materialized views based on the master table. A master table can have only one materialized view log defined on it.

Usually, a fast refresh takes less time than a complete refresh.ДЄ materialized view log is located in the master database in the same schema as the master table. This process is called a complete refresh. Without a materialized view log, Oracle Database must reexecute the materialized view query to refresh the materialized view. This process is called incremental or fast refresh. When DML changes are made to master table data, Oracle Database 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. The keyword SNAPSHOT is supported in place of MATERIALIZED VIEW for backward compatibility.

Materialize tables