4
Deployment Templates Concepts and Architecture

Packaging and Instantiation Options

A number of possibilitie s for deployment template packaging and instantiation are available. Table 4-2 illustrates the pos sibilities, identifies the mechanism for packaging and instantiation, and lists the documentation to use when you perform an operatio n.

Table 4-2 Packaging and Instantiation Options 

See the instructions for packaging in Oracle Database Advanced Replication Management API Reference.

The Replication Management API (PL/SQL Packages and SQL*Plus)

After Instantiation

After instantiating a deployment template at a remote materialized view site, the structure created is exactly the same as if you had created the materialized view environment l ocally at the materialized view site. Specifically, Oracle creates the materialized view, with the specified name, and an index based on the primary key to maintain constraint consistency. Other objects in the template are also created as if they were created manual ly at the materialized view site.

With respect to offline instantiations, the longer the dura tion between the packaging at the server and the instantiation at the remote site, the longer it takes for the first refresh after in stantiation at the remote materialized view site. The materialized view site uses the materialized view log at the master site to per form the fast refresh from the time that the template was packaged. Recall that changes made to the master table are logged to the ma terialized view log as soon as you package the deployment template.

Materialized View Groups

Objects created by an instantiated deployment template are added automatically to a materi alized view group with the same name as the object's master group. For example, if you instantiated the dt_mviewenv depl oyment template, which contains objects from the personnel and technical master groups, then your template objects are added to materialized view groups personnel and technical, respectively. Remember that a materi alized view group helps to maintain organizational consistency with the target master group and, more importantly, is required for up datable materialized views.

Refresh Groups

When you firs t begin building a deployment template, you define the name of the refresh group to which the template's materialized view objects wi ll be added. After the instantiation process is finished, you can specify that the materialized views in the refresh group be refresh ed automatically at set intervals, assuming a constant network connection to the master site.

You can use the Replication Management tool, or DBMS_REFRESH.CHANGE procedure, to change the refresh interval and next refresh data of a refresh group. To change these settings in the Replication Management tool, select the refresh group and edit the N ext Date and Interval fields. To change these settings with the DBMS_REFRESH.CHANGE procedure, set the interval and next _date parameters appropriately. If materialized view sites do not have a constant network connection to the master site, then they ca n refresh their refresh groups on-demand.

The following are examples of simple date expressio ns that you can use to specify next_date and interval:

• A next_da te or interval of one hour is specifies as:

  SYSDATE + 1/24
  
  

A next_date or interval of seven days is specifies as:

SYSDATE + 7 

See Also: Oracle Database Administrator's Guide and Oracle Database SQL Reference for more information about date arithmetic

< /ul>

Deployment T emplate Design

The combination of deployment template parameters and s ubquery subsetting gives the database administrator a powerful tool to administer a widely distributed database environment using sub queries and row-subsetted data. Additional design consideration must be given to column subsetting requirements and data sets needed for a replication environment.

Materialized view data sets are defined based on the materiali zed view's query, meaning that the user only sees data that complies with the materialized view's defining query. Both row and column subsetting enable you to create materialized views that contain customized data sets. Such materialized views can be helpful for reg ional offices or sales forces that do not require the complete corporate data set.

See Also:

"Data Subsetting with Materialized Views" for more information on data subsetting

Column Subsetting with Deployment Templates

Colum n subsetting enables you to exclude columns that are in master tables from materialized views. You do this by specifying certain sele ct columns in the SELECT statement during materialized view creation. Column subsetting is only possible through the use of deployment templates. Before you begin assembling your deployment template, consider how to build your templates.

For example, in a mass deployment environment with many "lightweight" clients, you may need to replicate tab les that contain LOB data without actually replicating the LOB data itself. This goal can be achieved by excluding the LOB column fro m the selected columns to be replicated when defining the column subset.

You can select any s ubset of columns in a read-only materialized view. For an updatable materialized view, the subset of columns must contain the followi ng columns:

• Primary key column(s)
• All columns used for conflict resolution for the replicated columns (see Figure  ;4-6)

  Note: While it is possible to configure column subsetting within a column group, it is not recommended because it can result in data inconsistencies between sites. When using column subsetting, you should eliminate columns at the column group leve l.

Figu re 4-6 Replicate Column-Subsetted Data



Text description of the illustration repln069.gif

If you are adding a materialized view that replicates columns pk, empid, salary, a nd level (illustrated in Figure 4-6), then you also need to include the Time Stamp column because it is used for conflict resolution for columns contained in Column Group A.

Note: Column subsetting is only available when you add a materialized view to a deployment templ ate using the Replication Management tool. Column subsetting is not available when using the replication management API. The master definition site must be available when defining a column subset. If your deploymen t template contains column-subsetted materialized views from multiple master groups, then the master definition site for each group m ust be available.

Row Subsetting

Row subsetting enables you to exclude rows that are in master tables from materialized views by using a WHERE clause. For example, the following statement creates a materialized view based on the oe.orders@orc1.world master table and includes only the row s for the sales representative with a sales_rep_id number of 173:

C
REATE MATERIALIZED VIEW oe.orders REFRESH FAST FOR UPDATE AS
  SELECT * FROM oe.orders@orc1.world
  WHERE sales_rep_id = 173;

Rows of the orders tabl e with a sales_rep_id number other than 173 are excluded from this materialized view.

< /a>

Row Subsetting with an Assignment Table

In some situations, you may benefit from using row subsetting with an assignment table. An assignment table lets you relate one entity to anothe r entity in your database, without storing the assignment information in either of the tables for the two entities. This technique is best illustrated through an example.

In the oe schema, the product_id column is the primary key in the product_information table, and the warehouse_id column is the primary key in the warehouses table. In this schema, the inventories table functions as an assignment table because it assigns a product to a warehouse using the product_id column and the warehouse_id column. These two col umns form the primary key of the inventories table.

With these three tables in < code>oe schema (inventories, product_information, and warehouses), you can track which products are in which warehouses without storing the product_id information in the warehouses table, nor th e warehouse_id information in the product_information table. To illustrate why this is important, consider what would happen if the inventories table did not exist and the warehouse_id column was a foreign key in t he product_information table.

In this case, if a salesperson wants to store prod uct information for the nearest warehouse, then the sales person would need to specify the warehouse_id for the warehous e in the WHERE clause of the CREATE MATERIALIZED VIEW statement. For example, the salesperson might create the materialized view using the following statement:

CREATE MATERIA
LIZED VIEW oe.product_information REFRESH FAST FOR UPDATE AS 
  SELECT * FROM oe.product_information@orc1.world 
  WHERE warehouse_id = 1; 

The drawback to this c onfiguration is that the warehouse_id is "hard coded" into the materialized view definition. If the company closes wareh ouse 1 or opens a new warehouse that is even closer to the salesperson, then the preceding materialized view definitions would need t o be altered or re-created. With this in mind, if you use assignment tables in conjunction with row subsetting in a subquery, then yo u can easily control changes to a materialized view environment.

In the oe schem a, the warehouse_id column is not part of the product_information table. Instead, a product is assigned to a warehouse through the inventories table. This relationship between products and warehouses is illustrated in Figure 4-7.

Figure 4 -7 Product/Warehouse Relationship



Text description of the illustration repln091.gif

If new warehouses are built or other warehouses are closed, then you can use the inventories table to assign prod ucts to different warehouses. Besides creating a single point of administration, assignment tables, such as the inventories table, used in conjunction with row subsetting in subqueries, can ensure security. For example, if necessary, you can limit a cer tain salesperson to see data for some warehouses but not others.

If we assume that each sales person is responsible for a particular location and only requires product information for products that are stored in a warehouse in that location, then we can use the inventories table as an assignment table along with row subsetting in subqueries to c reate the product_information materialized view that contains only the relevant information for a particular salesperson. The followi ng statement provides a salesperson with the proper data:

CREATE MATERIALIZED VIEW oe.product
_information REFRESH FAST FOR UPDATE AS
  SELECT * FROM oe.product_information@orc1.world pi

  WHERE EXISTS 
    (SELECT * FROM oe.inventories@orc1.world inv
    WHERE pi.product_id = in
v.product_id 
    AND EXISTS
      (SELECT * FROM oe.warehouses@orc1.world w

      WHERE inv.warehouse_id = w.warehouse_id
      AND EXISTS
        (SELECT * FROM hr.
locations@orc1.world loc
        WHERE w.location_id = loc.location_id 
        AND loc.posta
l_code = :p_code)));

The product_information materialized view is populated with product infor mation for the products that are stored in the warehouse located at the postal code specified with the p_code variable. Notice the p_code variable in the last line of the CREATE MATERIALIZED < code>VIEW statement.

With this flexibility, managers can easily control materialized v iew data sets by making simple changes to the inventories table, without requiring modification of the SQL for the mater ialized view creation statements. For example, if a new product is added to a particular warehouse, then the manager would simply add a row to the inventories table that assigns the product to the warehouse. After the next materialized view refresh, the data for the product is added to the materialized view site that tracks product information for the warehouse.

< /a>

Data Sets

Additional Design Consid erations

Finally, consider what other objects need to be created at th e remote materialized view site. Consider the following questions:

• < /a>Do you need to include the DDL to create the necessary database links from the materialized view site to the master site?
• What triggers or procedures does the materialized view environment require?
• Do any tables need to be created that store non-replicated data?
• Are any extra indexes required?

Local Control of Materialized View Creation

A deployment template is the most effective method of building and distributing a materialized view environm ent. Even if distribution is limited to only two or three sites, you still significantly reduce the amount of steps needed to build a materialized view environment by using deployment templates as opposed to individually creating the materialized view environment at those two or three sites. With deployment templates, you build once and distribute as needed.

However, one question remains: If a deployment template is the most effective means for building and distributing a materialized vi ew environment, then when should you locally build the materialized view environment at the remote materialized view site? In most ca ses, you should build a materialized view environment using the Materialized View Group Wizard or locally at the materialized view si te when local control must be maintained at the materialized view site.

One scenario where yo u might find local control of materialized view creation helpful is when it is desirable for the materialized view site to control wh at data it receives. For example, this is especially true of decision support sites (DSS), which are typically read-only materialized view sites. A DSS site may occasionally need to run complex queries and they do not want to slow the OLTP site, or bother the DBA at the OLTP site.

Local Materialized View Control

One of the major benefits of deploy ment templates is that control is maintained centrally by the DBA building the deployment template. In some cases, however, the mater ialized view site must retain some control.

Local control may be required if the materialized view site:

• Has an experienced DBA
• Is considered a trusted site
• Is a materialized view inst ead of a master site because of row subsetting requirements

Because materialized view g roups are created with the Replication Management tool's Materialized View Group Wizard locally at the materialized view site by its DBA, or perhaps a systems analyst with SQL knowledge, control can also be maintained at the materialized view site.

Consider the following as a perfect example for maintaining local control. Because multimaster replication doe s not allow for row and column data subsetting, updatable materialized view sites are sometimes created primarily for their ability t o subset data. These sites are typically secure, have experienced DBAs, and require the ability to maintain control locally to meet u ser and application requirements. Materialized view groups created with the Materialized View Group Wizard or with the replication ma nagement API allow for the localized control necessary to meet the requirements of the secure updatable materialized view sites.

Also, remember that when a materialized view environment is created with a deployment template, a ll objects in the materialized view environment are added to the same refresh group. While this might be fine for most installations, certain situations may require that the objects in a materialized view group are assigned to several different refresh groups.

---

N ote: To create this oe.product_information materialized view, postal_code in must be logged in the materialized view log for the hr.locations table. See "Logging Columns in the Materialized View Log" for more information.

Previous Next

Copyright © 1996, 2003 Oracle Corporation All Rights Reserved.

Home Book List Contents Index Master&n bsp;Index Feedback