4 Deployment Templates Concepts and Architecture

General Template Information

Oracle deployment templates center around the general template information, which consists of the template name, target refresh group, and private/public status. As illustrated in Figure 4-1, the REFRESH_TEMPLATE_NAME is used in all aspects of deployment template data dictionary views. You add the materialized view environment objects to the template prior to releasing the template for distribution according to the specified template identification (see Figure 4-2).

A deployment template is defined at a single master site. While you cannot have two deployment templates at the master site with the same name, you can copy a deployment template to another site using the same deployment template name.

Figure 4-1 Deployment Template View Relationships

Description of "Figure 4-1 Deployment Template View Relationships"

Figure 4-2 Deployment Template Elements Added to Template

Description of "Figure 4-2 Deployment Template Elements Added to Template"

Template Object Definitions

After the template has been defined, add objects to the template. When the template is instantiated at the materialized view site, the object DDL (that is, CREATE MATERIALIZED VIEW, CREATE TABLE, and so on) is executed to create the appropriate objects at the materialized view site.

You can add objects to a deployment template that are based on a existing master objects, but if necessary, you can create a new template object by defining DDL to create the object. Oracle checks any new object DDL to ensure that it is lexically correct, which prevents the execution of faulty DDL. Updatable materialized views added to a deployment template must be based on a table in a master group, but other objects, such as read-only materialized views, can be based on objects that are not in master groups.

In most cases, you add materialized views to the template, but if necessary, you can add other objects. For example, constraints can be added to enforce data integrity at the materialized view site, views can be added for displaying data, or tables can be added for local data storage. In some cases, you might even include all objects for an application in a deployment template. Materialized views created using a deployment template are automatically added to the refresh group defined for the template.

You cannot use deployment templates to instantiate the following types of objects:

• User-defined types

• User-defined type bodies

• User-defined operators

• Indextypes

Nor can you use deployment templates to instantiate any objects based on these types of objects.

Template Parameters

If each target materialized view site requires a data set unique to its site, then you can define variables in the object DDL. These variables create a parameterized template that allows for custom data sets when the template is instantiated, allowing different materialized view sites to have different data sets. These parameters are embedded in the object DDL. During template instantiation, the individual user values for these parameters are substituted.

Oracle enables you to specify default values and user-specific parameter values for a template. You can enter the parameter values during the creation of the deployment template or after the template is created, but you must enter the parameter values before the template is instantiated. Users cannot enter values for parameters during instantiation.

If user-specific parameter values exist, then these values are automatically used when the specified user instantiates the template. For example, consider the variable region. Suppose you establish the following user-specific parameter values for template sales_temp:

The defining SELECT statement for the materialized view is the following:

SELECT cust_id, sales_to_date, status FROM table_x WHERE region_id=:region;

When users fay and baer instantiate template sales_temp, their resulting materialized view data sets are the following:

Template Parameters in the WHERE Clause and Security

In addition to creating customized data subsets, you can use template parameters in the WHERE clause of a CREATE MATERIALIZED VIEW statement to securely limit the materialized view site to viewing and changing only the data that satisfies the WHERE clause. For example, suppose you have specified the following for the region parameter in the user specific parameters list:

Users accessing the materialized view instantiated by user fay only see data for region east and can only view, update, or delete data that complies with this WHERE clause. In other words, a user of this materialized view cannot view, update, or delete data for region west, because the materialized view only contains data for region east.

User Authorization

Deployment templates can be either public or private. You set this when you create the template. If a template is public, then any user with access to the master site can instantiate the template.

If a template has been created for private use, then only authorized users can instantiate the target template. To enforce private use, create a list of authorized users at the master site. If an unauthorized user attempts to instantiate the target template, then the instantiation process fails.

Deployment Sites

Maintaining the emphasis on centralized control, you can monitor and manage certain characteristics of the instantiated environment at the remote materialized view site. Specifically, you have the ability to view the sites that have instantiated a deployment template, which includes the deployment template name, authorized user, and status of the instantiated environment.

Online Instantiation

Online instantiation allows a materialized view site to instantiate a deployment template while connected to the target master site. During the online instantiation process, the structure of the materialized view site is created, and the specified data subset is pulled from the master site and stored in the appropriate materialized views.

Figure 4-3 Online Instantiation

Description of "Figure 4-3 Online Instantiation"

Packaging a deployment template for online instantiation means generating a script file that, when run at the materialized view site, creates the materialized view objects and connects to the master site to populate the materialized views with data. SQL statements such as CREATE MATERIALIZED VIEW ... AS SELECT are used to populate the materialized views with data over a network from the master site.

One of the benefits of online instantiation is that the data subset is current as of the instantiation process. This data currency, however, comes at a cost. Online instantiation requires a "live" connection between the materialized view and master sites, which, depending on the size of the materialized view environment created, might increase network traffic.

Furthermore, laptop users connected by a modem might need to stay connected for a long time. The duration of the connection depends on the number of objects created, the complexity of the materialized view subqueries, and the amount of data transmitted, especially over low bandwidth modem lines.

Offline Instantiation

To decrease server loads during peak usage periods and reduce remote connection times, you can choose offline instantiation of the template for your environment. Packaging a template for offline instantiation means generating a script or a binary file that contains the DDL and data manipulation language (DML) to build the materialized view environment defined in the deployment template and populate the environment with data. You package the script or binary file and save the file to some type of storage media (such as tape, CD-ROM, and so on), and then provide a means of transferring the script or binary file to the materialized view site. Each materialized view site requires a separate offline instantiation script.

When you package a template for instantiation, the materialized view logs for each master table on which a materialized view is based in the template begin to log changes. The materialized view log for a particular master table does not clear these changes until every materialized view based on the master table refreshes after instantiation. Therefore, to prevent the materialized view log from growing large, the template should be instantiated, and the materialized views should be refreshed as soon as possible after packaging.

During instantiation, the template and data are pulled from the storage media, instead of being pulled from the master site. This operation has the benefit of reducing network traffic and eliminating the need for a constant network connection. However, after instantiation, the data in the materialized view site reflects the master site data at packaging time and must be made current by a refresh.

Figure 4-4 Offline Instantiation

Description of "Figure 4-4 Offline Instantiation"

Offline instantiation is an ideal solution for mass deployment situations where many laptops and other disconnected computers are instantiating the target template.

Scenarios for Instantiating a Deployment Template

Table 4-1 summarizes the scenarios for instantiating of a deployment template.

Either you (the DBA) or the target user can package the deployment template. Either use the Advanced Replication interface's Create Deployment Template Wizard to package a template for offline instantiation, or the replication management API to package a template for offline or online instantiation. End-users use the public API to package a deployment template, while DBAs generally use the private API for packaging.

Typically, when a deployment template will be instantiated offline, the DBA performs the packaging, but when the deployment template will be instantiated online, the user can perform the packaging. However, there are no restrictions on users or DBAs performing either online or offline packaging, other than the use of different API calls.

The following replication management API functions can be used to package a deployment template.

Private functions (DBA only):

• DBMS_REPCAT_RGT.INSTANTIATE_OFFLINE function

• DBMS_REPCAT_RGT.INSTANTIATE_ONLINE function

Public functions:

• DBMS_REPCAT_INSTANTIATE.INSTANTIATE_OFFLINE function

• DBMS_REPCAT_INSTANTIATE.INSTANTIATE_ONLINE function

Use of Standard DDL

Template object definitions are created using the same DDL that is used to create the objects locally at the materialized view site. For example, you can issue the following statement to create a materialized view:

CREATE MATERIALIZED VIEW hr.departments_mv 
      REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT 
      department_id, department_name, manager_id, location_id
    FROM hr.departments@orc1.example.com;

To add this same materialized view to a deployment template, you can use the Advanced Replication interface's Create Deployment Template Wizard, or execute the CREATE_TEMPLATE_OBJECT function, as shown in the following example:

DECLARE
   tempstring VARCHAR2(3000);
   a NUMBER;
BEGIN
   tempstring := 'CREATE MATERIALIZED VIEW hr.departments_mv 
      REFRESH FAST WITH PRIMARY KEY FOR UPDATE AS SELECT 
      department_id, department_name, manager_id, location_id
      FROM hr.departments@orc1.example.com';
   a := DBMS_REPCAT_RGT.CREATE_TEMPLATE_OBJECT (
           refresh_template_name => 'hr_refg_dt',
           object_name => 'departments_mv',
           object_type => 'MATERIALIZED VIEW',
           ddl_text => tempstring,
           master_rollback_seg => 'rbs');
END;
/

Executing the preceding function adds the materialized view definition to the deployment template named dt_mviewenv. When this particular materialized view is instantiated, the materialized view mview_test is created. In addition to creating materialized views, you can add table, trigger, procedure, index, and other object definitions to the deployment template.

Whenever you create a materialized view, always specify the schema name of the table owner in the query for the materialized view. In the preceding example, hr is specified as the owner of the employees table.

Packaging a Deployment Template for Online Instantiation

When a deployment template is packaged for online instantiation, the resulting DDL that is required to create the remote materialized view environment is generated and all template parameter substitutions are performed. Where this generated DDL is stored depends on the type of materialized view client.

The online instantiation script is stored locally on the hard drive of the computer from which replication management API is executed to package the template. If this computer is not the materialized view site computer, then the online instantiation file must be transferred to the materialized view site for online instantiation.

Packaging a Deployment Template for Offline Instantiation

When a deployment template is packaged for offline instantiation, the DDL that is required to create the remote materialized view environment and the DML that is required to populate the environment with the data are both stored in a generated file. Also, during packaging, all template parameter substitutions are performed.

When a template is packaged, a script or binary file is created for offline instantiation and is saved to a storage device, such as hard disk, CD-ROM, tape, and so on. Either the Advanced Replication interface's Create Deployment Template Wizard or the replication management API can be used to package a deployment template for offline instantiation.

The offline instantiation script is stored locally on the hard drive of the computer from which the request is made to package the template. If this computer is not the materialized view site computer, then the offline instantiation file must be transferred to the materialized view site for offline instantiation.

When the remote materialized view site instantiates the template, the script or binary file is executed from the storage media or from the local hard drive. This execution creates the materialized view environment and populates the environment according to the data set defined during the packaging process. Recall that any template parameters that define the data set for individual sites are defined during the packaging process.

Online Instantiation

During the online instantiation process, the structure of the materialized view site is created, and the specified data subset is pulled from the master site and stored in the appropriate materialized views. Also, after the remote materialized view site begins the online instantiation process, Oracle evaluates the parameters that have been defined for the deployment template. Any values defined for these parameters are used when the object DDL in the template is executed so that custom data sets can be installed at the remote materialized view site. At the same time, the materialized views are registered at the master site, and the materialized view logs begin logging the changes to the master tables.

Two possible methods can be used to define template parameter values: default parameter values and user parameter values. Oracle checks to see if these parameter values exist and then uses them according to the hierarchy:

1. User Parameter Values

2. Default Parameter Values

If user parameter values have been defined and a listed user is instantiating the template, then the user parameter values are used when instantiating the template. If no user parameter values have been defined, then Oracle uses the default parameter values. Figure 4-5 shows the parameter checking process.

Figure 4-5 Checking for Parameters During Online Instantiation

Description of "Figure 4-5 Checking for Parameters During Online Instantiation"

After the parameters are checked, the objects created by the template are added to the refresh group specified when the template was created.

Offline Instantiation

In a mass deployment environment, most materialized view environments use the offline instantiation method to create the necessary materialized view environment. When you package the deployment template, a script or binary file is created to store the DDL needed to create the materialized view environment, the parameter values used during the instantiation process, and the DML necessary to populate the materialized view environment with data.

The script or binary file can be copied to a CD-ROM, floppy disk, or other storage media or can be posted on a Web or FTP site to be downloaded to the remote materialized view site. It can also be transferred using the DBMS_FILE_TRANSFER package. The flexibility in delivery mechanisms allows you and your users to choose the most effective method for instantiating a deployment template.

Packaging and Instantiation Options

A number of possibilities for deployment template packaging and instantiation are available. Table 4-2 illustrates the possibilities, identifies the mechanism for packaging and instantiation, and lists the documentation to use when you perform an operation.

Materialized View Groups

Objects created by an instantiated deployment template are added automatically to a materialized view group with the same name as the object's master group. For example, if you instantiated the dt_mviewenv deployment 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 materialized view group helps to maintain organizational consistency with the target master group and, more importantly, is required for updatable materialized views.

Refresh Groups

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

You can use the Advanced Replication interface in Oracle Enterprise Manager, or DBMS_REFRESH.CHANGE procedure, to change the refresh interval and next refresh data of a refresh group. To change these settings in the Advanced Replication interface, select the refresh group and edit the Next 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 can refresh their refresh groups on-demand.

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

• A next_date or interval of one hour is specifies as:

  SYSDATE + 1/24
  

• A next_date or interval of seven days is specifies as:

  SYSDATE + 7 
  

Row Subsetting with an Assignment Table

In some situations, you can benefit from using row subsetting with an assignment table. An assignment table lets you relate one entity to another 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 columns form the primary key of the inventories table.

With these three tables in 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 the 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 the product_information table.

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

CREATE MATERIALIZED VIEW oe.product_information REFRESH FAST FOR UPDATE AS 
  SELECT * FROM oe.product_information@orc1.example.com 
  WHERE warehouse_id = 1; 

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

In the oe schema, 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

Description of "Figure 4-7 Product/Warehouse Relationship"

If new warehouses are built or other warehouses are closed, then you can use the inventories table to assign products 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 certain salesperson to see data for some warehouses but not others.

If we assume that each salesperson 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 create the product_information materialized view that contains only the relevant information for a particular salesperson. The following 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.example.com pi
  WHERE EXISTS 
    (SELECT * FROM oe.inventories@orc1.example.com inv
    WHERE pi.product_id = inv.product_id 
    AND EXISTS
      (SELECT * FROM oe.warehouses@orc1.example.com w
      WHERE inv.warehouse_id = w.warehouse_id
      AND EXISTS
        (SELECT * FROM hr.locations@orc1.example.com loc
        WHERE w.location_id = loc.location_id 
        AND loc.postal_code = :p_code)));

The product_information materialized view is populated with product information 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 VIEW statement.

With this flexibility, managers can easily control materialized view data sets by making simple changes to the inventories table, without requiring modification of the SQL for the materialized 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.