Unknown May 22, at PM. Unknown July 27, at AM. Unknown June 1, at AM. Unknown January 20, at AM. Unknown April 30, at AM. Dayakar February 6, at PM. Anonymous March 7, at AM. Unknown April 24, at AM. Unknown January 3, at AM.
Unknown September 20, at AM. UnKnown November 26, at PM. Newer Post Older Post Home. Subscribe to: Post Comments Atom. An object table is a special kind of table in which each row represents an object. Each row in an object table is a row object.
You can also replicate collections. Oracle9i Application Developer's Guide - Object-Relational Features for detailed information about user-defined types, Oracle objects, and collections. This section assumes a basic understanding of the information in that book. To replicate schema objects based on user-defined types, the user-defined types themselves must exist, and must be exactly the same, at all replication sites.
In addition, Oracle Corporation recommends that you add a user-defined type to the replication group in which it is used, but doing so is not required. When replicating user-defined types and the schema objects on which they are based, the following conditions apply:. To ensure that a user-defined type is exactly the same at all replication sites, you must create the user-defined type at the materialized view site in one of the following ways:.
Oracle Corporation recommends that you use the replication management API to create, modify, or drop any replicated object at a materialized view site, including user-defined types. If you do not use the replication management API for these actions, then replication errors may result. This procedure creates the type and adds it to a materialized view group. It may be necessary to do this if you want to create a read-only materialized view that uses the type, and you do not want to add the read-only materialized view to a materialized view group.
Now that you know the OID for the type at the master site, complete the following steps to create the type at the materialized view site:. A read-only materialized view can replicate specific attributes of a column object without replicating other attributes. You can create the following read-only materialized view at a remote materialized view site:. An updatable materialized view must replicate the entire column object.
It cannot replicate some attributes of a column object but not others. Column subsetting is supported only through the use of deployment templates. That is, an object materialized view is composed of row objects. If a materialized view that is based on an object table is created without using the OF type clause, then the materialized view is read-only and is not an object materialized view.
That is, such a materialized view has regular rows, not row objects. To create a materialized view based on an object table, the types on which the materialized view depends must exist at the materialized view site, and each type must have the same object identifier as it does at the master site. After the required types are created at the materialized view site, you can create an object materialized view by specifying the OF type clause.
The types must be exactly the same at the materialized view site and master site. See "Type Agreement at Replication Sites" for more information. If you create a materialized view based on an object table without using the OF type clause, then the materialized view is read-only, and it loses the object properties of the object table on which it is based. That is, the resulting read-only materialized view contains one or more of the columns of the master, but each row functions as a row in a relational table.
The rows are not row objects. An object materialized view inherits the object identifier OID specifications of its master. If the master has a primary key-based OID, then the OIDs of row objects in the materialized view are primary key-based.
Also, the OID of each row in the object materialized view matches the OID of the same row in the master, and the OIDs are preserved during refresh of the materialized view.
Consequently, REF s to the rows in the object table remain valid at the materialized view site. Collection columns are columns based on varray and nested table datatypes. Oracle supports the creation of materialized views with collection columns. You must specify a primary key for the storage table if you plan to create materialized views based on its parent table. If you want to create materialized views that can be fast refreshed, then create a materialized view log on both the parent table and the storage table, specifying the nested table column as a filter column for the parent table's materialized view log:.
At the materialized view site, create the required types, ensuring that the object identifier for each type is the same as the object identifier at the master site. If you do not specify this clause, Oracle automatically names the storage table. The following actions are not allowed directly on the storage table of a nested table in a materialized view:.
These actions can occur indirectly when they are performed on the materialized view that contains the nested table. In addition, you cannot replicate a subset of the columns in a storage table.
You can create materialized views with REF columns. A REF is an Oracle built-in datatype that is a logical "pointer" to a row object in an object table. A scoped REF is a REF that can contain references only to a specified object table, while an unscoped REF can contain references to any object table in the database that is based on the corresponding object type.
As you will see in the following section, you can rescope a REF column to a local materialized view or table at the materialized view site during creation of the materialized view. If you do not rescope the REF column, then they continue to point to the remote master. Unscoped REF columns always continue to point to the master. If you are creating a materialized view based on a master that has a scoped REF column, then you can rescope the REF to a different object table or object materialized view at the materialized view site.
Typically, you would rescope the REF column to the local object materialized view instead of the original remote object table. In this case, the REF s are considered dangling because they point back to the object table at the remote master site.
If you create a materialized view based on a remote master with an unscoped REF column, then the REF column is created in the materialized view, but the REF s are considered dangling because they point to a remote database. Oracle can find the object referenced directly using the rowid contained in the REF , without the need to fetch the rowid from the OID index.
Invalid rowid hints can cause performance problems. At the master site and master materialized view site, an Oracle database automatically registers information about a materialized view based on its master table s or master materialized view s.
The following sections explain more about Oracle's materialized view registration mechanism. A level 1 materialized view or materialized view group is registered at its master site. A level 2 or higher multitier materialized view or materialized view group is registered at its master materialized view site, not at the master site.
Administrators can use this information to monitor materialized view activity and coordinate changes to materialized view sites if a master table or master materialized view needs to be dropped, altered, or relocated.
Oracle automatically registers a materialized view at its master site or master materialized view site when you create the materialized view, and unregisters the materialized view when you drop it. The same applies to materialized view groups. When you drop a master materialized view, Oracle does not automatically drop the materialized views based on it. You must drop these materialized views manually.
If you do not drop such a materialized view and the materialized view tries to refresh to a master materialized view that has been dropped, Oracle returns an error. Oracle cannot guarantee the registration or unregistration of a materialized view at its master site or master materialized view site during the creation or drop of the materialized view, respectively.
If Oracle cannot successfully unregister a materialized view when you drop the materialized view, then the registration information for the materialized view persists in the master site or master materialized view site until it is manually unregistered.
It is possible that complex materialized views may not be registered. If necessary, you can maintain registration manually. The objects used in materialized view replication are depicted in Figure Some of these objects are optional and are used only as needed to support the created materialized view environment. For example, if you have a read-only materialized view, then you do not have an updatable materialized view log nor an internal trigger at the materialized view site.
Also, if you have a complex materialized view that cannot be fast refreshed, then you may not have a materialized view log at the master site.
Notice that a master materialized view may have both a materialized view log and an updatable materialized view log. Make sure you account for the extra space required by these logs when you are planning for your master materialized view site.
The three mechanisms displayed in Figure are required at a master site and at a master materialized view site to support fast refreshing of materialized views.
Master materialized views contain the mechanisms described in "Materialized View Site Mechanisms" in addition to the mechanisms described in this section. The master table or master materialized view is the basis for the materialized view. A master table is located at the target master site while a master materialized view is located at a master materialized view site. If the master is a master table, then this table may be involved in both materialized view replication and multimaster replication.
Remember that a materialized view points to only one master site or master materialized view site. Changes made to the master table or master materialized view, as recorded by the materialized view log, are propagated to the materialized view during the refresh process.
Fast refreshable materialized views can be created based on master tables and master materialized views only. Materialized views based on a synonym or a view must be complete refreshed.
When changes are made to the master table or master materialized view using DML, an internal trigger records information about the affected rows in the materialized view log. This information includes the values of the primary key, rowid, or object id, or both, as well as the values of the other columns logged in the materialized view log. This is an internal AFTER ROW trigger that is automatically activated when you create a materialized view log for the target master table or master materialized view.
This trigger is always the last trigger to fire. When the materialized view contains a subquery, you may need to log columns referenced in a subquery. See "Data Subsetting with Materialized Views" for information on subquery materialized views and "Logging Columns in the Materialized View Log" for more information about the columns that must be logged. A materialized view log is required on a master if you want to fast refresh materialized views based on the master. When you create a materialized view log for a master table or master materialized view, Oracle creates an underlying table as the materialized view log.
A materialized view log can hold the primary keys, rowids, or object identifiers of rows, or both, that have been updated in the master table or master materialized view. A materialized view log can also contain other columns to support fast refreshes of materialized views with subqueries.
The materialized view log is created in the same schema as the target master. One materialized view log can support multiple materialized views on its master table or master materialized view. As described in the previous section, the internal trigger adds change information to the materialized view log whenever a DML transaction has taken place on the target master. A combination materialized view log works in the same manner as a materialized view log that tracks only one type of value, except that more than one type of value is recorded.
For example, a combination materialized view log can track both the primary key and the rowid of the affected row are recorded. Though the difference between materialized view logs based on primary keys and rowids is small one records affected rows using the primary key, while the other records affected rows using the physical rowid , the practical impact is large. Using rowid materialized views and materialized view logs makes reorganizing and truncating your master tables difficult because it prevents your ROWID materialized views from being fast refreshed.
If you reorganize or truncate your master table, then your rowid materialized view must be COMPLETE refreshed because the rowids of the master table have changed. You can create materialized view logs on object tables. When you create an object table based on this type, you can either specify that the object identifier should be system-generated or primary key-based:. When you create a materialized view log on an object table, you must log the object identifier by specifying the WITH OBJECT ID clause, but you can also specify that the primary key is logged if the object identifier is primary key-based.
Materialized views and materialized view logs are exported with the schema name explicitly given in the DDL statements. Therefore, materialized views and materialized view logs cannot be imported into a schema that is different than the schema from which they were exported.
When a materialized view is created, several additional mechanisms are created at the materialized view site to support the materialized view. Specifically, a base table, at least one index, and possibly a view are created. If you create an updatable materialized view, then an internal trigger and a local log the updatable materialized view log are also created at the materialized view site.
If the materialized view site is a master materialized view site, then it contains the mechanisms described in the previous section in addition to the mechanisms described in this section. Figure , "Materialized View Replication Objects". The way materialized view base tables function depends on the compatibility level of your materialized view database.
A view is created only to support materialized view replication with Oracle release 8. At least one index is created at the remote materialized view site for each primary key materialized view.
Additional indexes may be created by Oracle at the remote materialized view site to support fast refreshing of materialized views with subqueries. A read-only materialized view does not create this log, and Oracle does not use this log during a complete refresh because, in this case, the entire materialized view is replaced.
If there is a conflict between an updatable materialized view and a master, then, during a refresh, the conflict may result in an entry in the updatable materialized view log that is not in the materialized view log at the master site or master materialized view site.
In this case, Oracle uses the updatable materialized view log to remove or overwrite the row in the materialized view. The updatable materialized view log is also used when you fast refresh a writeable materialized view, as illustrated in the following scenario:.
A read-only materialized view does not create this trigger. In addition to the materialized view mechanisms described in the previous section, several other mechanisms organize the materialized views at the materialized view site. These mechanisms maintain organizational consistency between the materialized view site and its master site or master materialized view site, as well as transactional read consistency with the target replication group.
These mechanisms are materialized view groups and refresh groups. A materialized view group in a replication system maintains a partial or complete copy of the objects at the target replication group at its master site or master materialized view site.
Materialized view groups cannot span the boundaries of the replication group at the master site or master materialized view site. Figure displays the correlation between Groups A and B at the master site and Groups A and B at the materialized view site. Group A at the materialized view site see Figure contains only some of the objects in the corresponding Group A at the master site.
Group B at the materialized view site contains all objects in Group B at the master site. Under no circumstances, however, could Group B at the materialized view site contain objects from Group A at the master site. As illustrated in Figure , a materialized view group has the same name as the master group on which the materialized view group is based. For example, a materialized view group based on a personnel master group is also named personnel. In addition to maintaining organizational consistency between materialized view sites and their master sites or master materialized view sites, materialized view groups are required for supporting updatable materialized views.
If a materialized view does not belong to a materialized view group, then it must be a read-only or writeable materialized view. A materialized view group owner enables you to have multiple materialized view groups based on a single replication group at a master site or master materialized view site.
For example, if you need to support multiple users within the same database at a materialized view site, then you may want to create multiple materialized view groups for a target master group. Doing so enables you to define different subqueries for your materialized view definitions in each materialized view group, and allows each user to access only his or her subset of the data.
Defining multiple materialized view groups gives you the ability to control data sets at a group level. For example, if you create different materialized view groups named hr , personnel , and manufacturing for these departments, then you can administer each department as a group, instead of as individual objects.
For example, you can drop the objects as a group. To accommodate multiple materialized view groups at the same materialized view site that are based on a single replication group at the master site or master materialized view site, you can specify a group owner as an additional identifier when defining your materialized view group. They are local copies of data located remotely, or are used to create summary tables based on aggregations of a table's data.
Materialized views, which store data based on remote tables are also, know as snapshots. A materialized view can query tables, views, and other materialized views. Collectively these are called master tables a replication term or detail tables a data warehouse term. For replication purposes, materialized views allow you to maintain copies of remote data on your local node.
These copies are read-only. If you want to update the local copies, you have to use the Advanced Replication feature. You can select data from a materialized view as you would from a table or view. For data warehousing purposes, the materialized views commonly created are aggregate views, single-table aggregate views, and join views. In this article, we shall see how to create a Materialized View in Oracle and discuss Refresh Option of the view.
In replication environments, the materialized views commonly created are primary key, rowid, and subquery materialized views. The following statement creates the primary-key materialized view on the table emp located on a remote database.
0コメント