So to clear this option, select another one at first. Materialized view log can have Object ID option selected only when it is created for object table. Sequence option is vailable only when creating materialized view log on Oracle 10g and higher.
You can select additional table columns to record them in materialized view log for all changed rows of master table in the Columns grid. Note, that if primary key option is set, you can not select primary key columns. In that case they are automatically implicitly included. A materialized view log cannot be created using a materialized view as the table or for tables in cache groups. Create a materialized view log on the employees table. Skip Headers.
All rights reserved. Book List. Fast refresh supports two types of materialized view logs: timestamp-based materialized view logs and commit SCN-based materialized view logs. Timestamp-based materialized view logs use timestamps and require some setup operations when preparing to refresh the materialized view.
Commit SCN-based materialized view logs use commit SCN data rather than timestamps, which removes the need for the setup operations and thus can improve the speed of the materialized view refresh. Otherwise, a time-stamp based materialized view log is created. Oracle Database Utilities for information on using direct loader logs. 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.
Specify the schema containing the materialized view log master table. If you omit schema , then Oracle Database assumes the master table is contained in your own schema. Oracle Database 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. Specify the name of the master table for which the materialized view log is to be created. Oracle Database encrypts any columns in the materialized view log that are encrypted in the master table, using the same encryption algorithm.
Restrictions on Master Tables of Materialized View Logs The following restrictions apply to master tables of materialized view logs:. Specify the tablespace in which the materialized view log is to be created. If you omit this clause, then the database creates the materialized view log in the default tablespace of the schema of the materialized view log. The default is the logging characteristic of the tablespace in which the materialized view log resides. 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. Partitioning of materialized view logs is the same as partitioning of tables. WITH Clause. 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. 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 , 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. A primary key log, created either explicitly or by default, performs additional checking on the primary key constraint.
Sequence numbers are necessary to support fast refresh after some update scenarios.
0コメント