View source for Materialized View Logs Wizard (Oracle)

{{Parent|Oracle Object Wizards}}

Materialized view logs are tables that maintain a history of modifications to the master table, and they are used to refresh simple materialized views. When you create a materialized view log, Oracle automatically creates a log table to track data changes in the master table and a log trigger to maintain the data in the log table. A log can refresh the materialized view incrementally and is therefore often less time-consuming than a complete refresh.

The Materialized View Log Wizard lets you:

* Specify the materialized view log owner and master table.
* Select refresh types and select column filters.
* Specify how Oracle should allocate data blocks to store the materialized view log.
* Specify how Oracle should manage the growth of the materialized view.
* Specify if you want the data for the materialized view log cached, if you want updates logged, and to enable parallel query.
* Specify if you want the log to hold new values.

'''To Open the Materialized View Logs Wizard'''

#On the {{DatasourceWindow}}, find the datasource where you want to create a Materialized View Log and expand the Schema node.
#Right-click the '''Materialized View Log''' node, and select '''New'''.

The table that follows describes the fields you may encounter as you complete the wizard:

{|
|- valign="top"
! Required Information
! Description
|- valign="top"
|
Who owns the materialized view log’s master table?
|
Self-explanatory
|- valign="top"
|
Which table will serve as the materialized view log’s master table?
|
Self-explanatory
|- valign="top"
|
On which tablespace do you want to place the log?
|
Self-explanatory
|- valign="top"
|
Which refresh types would you like to use?
|
Primary Key - The log records changes to the master table based on the primary key of affected rows. A primary key's values uniquely identify the rows in a table. Changes are propagated according to row changes as identified by the primary key value of the row. Only one primary key can be defined for each table.The primary key of the master table is the basis for this refresh option, which is the default option. ROWID - The log records changes to the master table based on the RowID of the affected rows. A ROWID is a globally unique identifier for a row in a database based on the physical row identifiers. A ROWID is created at the time the row is inserted into a table, and destroyed when it is removed from a table. ROWID materialized views cannot contain distinct or aggregate functions or GROUP BY subqueries, joins and set operations.
|- valign="top"
|
Optional: Select any filter column(s) to be recorded in the materialized view log.
|
A filter column is a column whose values you want to be recorded in the materialized view log for any rows that are changed. You can specify only one primary key, one ROWID, and one filter column list per materialized view log.
|- valign="top"
|
How many transaction entries are allowed for each data block in the materialized view log?
|
A transaction is a logical unit of work that contains one or more SQL statements. Each transaction that updates a data block requires a transaction entry. Initial (1-255) - Ensures that a minimum number of concurrent transactions can update a data block, avoiding the overhead of allocating a transaction entry dynamically. Maximum (1-255) - Limits concurrency on a data block.
|- valign="top"
|
What is the percent of space reserved for future updates?
|
Percent Free (0 99) - This sets the percentage of a data block to be reserved for possible row updates that are included in the block. The value you set is the percent kept free.
|- valign="top"
|
What is the minimum percentage of used space that Oracle maintains for each data block?
|
Percent Used (0-99) - Set the amount of space to be used for each datablock. NOTE: The sum of percent free and the percent used cannot exceed 100.
|- valign="top"
|
How large are the materialized view log’s extents?
|
Initial Extent (KB) -The default is the value specified for the tablespace where the materialized view log resides.
|- valign="top"
|
Should the data for a materialized view log be cached?
|
Yes/No - Select Yes if you want Oracle to put data you access frequently at the most recently used end of the least recently used list in the buffer cache when a full table scan is performed. This option is useful for small lookup tables. No indicates that your most frequently accessed data blocks are put at the least recently used end of the least recently used list of the buffer cache.
|- valign="top"
|
Do you want updates to be logged?
|
Yes/No
|- valign="top"
|
Do you want to enable parallel query for the log?
|
Degree. The integer is number of parallel threads used in the parallel operation. The Parallel server query option lets you process queries using many query server processes running against multiple CPUs. This option provides substantial performance gains such as reduction of the query completion time.
|- valign="top"
|
Should the log hold new values?
|
Yes/No - Yes indicates both old and new values should be saved in the materialized view log. No disables recording of new values in the log. This is the default.
|}