2 Creating Relational Data Objects

About First Class Objects (FCOs)

A First Class Object (FCO) represents a component in the metadata repository that can be manipulated through the Warehouse Builder interface. FCOs often, but not always, own other objects. For example, a TABLE is an FCO that may own the following Second Class Objects: TABLE_COLUMN, UNIQUE_KEY, FOREIGN_KEY, and CHECK_CONSTRAINT.

For those accessing Warehouse Builder using the Design Center, FCOs generally appear on the navigation tree. Similarly, users who access Warehouse Builder through OMB*Plus can generalize FCOs as objects of OMBCREATE, OMBALTER, OMBRETRIEVE, and OMBDROP commands.

About Second Class Objects (SCOs)

A Second Class Object (SCO) represents a dependent object component. An SCO is always owned by another object, and can, in turn, own objects itself. For example, the FCO called MAPPING contains the SCO MAPPING_OPERATOR, which in turn contains ATTRIBUTES.

For those accessing Warehouse Builder through the Design Center, SCOs can only be manipulated through an FCO. Similarly, users who access Warehouse Builder through OMB*Plus can only manipulate SCO definitions through a command against an FCO.

About Third Class and Fourth Class Objects

Third Class and Fourth Class objects are relative rankings of objects owned by other objects. These refer only to objects whose ownership spans several layers. For example, INDEX_COLUMN is an SCO in the scenario where a DIMENSION_TABLE (which is a FCO) owns INDEX_COLUMN. However, INDEX_COLUMN becomes a Third Class Object in the scenario where the FCO CUBE_TABLE owns the SCO INDEX, which in turn owns INDEX_COLUMN.

Defining Error Tables for Data Objects

An error table is created for a data object only if you set the Error Table Name configuration parameter for the data object. If you do not specify an error table name for a data object, logical errors are not logged for that object. However, when a data object has data rules associated with it, even if you do not specify an error table name for the object, Warehouse Builder creates an error table using a default name. For example, if the name of the table for which you specified data rules is EMP, the error table is called EMP_ERR.

To create an error table for a data object:

1. In the Projects Navigator, right-click the data object for which you want to create an error table, and select Configure.

   The Configuration tab containing the configuration parameters for the data object is displayed.

2. In the Configuration tab, expand the Error Tables node.

3. Set the value of the Error Table Name parameter to the name of the error table for the data object.

If you modify the value of the Error Table Name parameter after the data object is deployed, you must drop the data object and then redeploy it. If this data object was used in mappings, ensure that you synchronize all operators that are bound to this data object and then redeploy the mappings.

Error Table Columns

In addition to the columns contained in the data object, error tables for a data object contain the columns listed in Table 2-3.

For scalar data types, if no data rules are applied to the data object, the columns in the error table are of data type VARCHAR2(4000). This allows physical data errors such as ORA-12899: value too large for column, to be captured. If data rules are applied, the columns in the error table are of the same data type as the columns in the data object.

For example, the table TEST has two columns C1, of data type NUMBER, and C2, of data type VARCHAR2(10). The error table generated for TEST will contain the DML error columns C1 and C2. If no data rules are applied to TEST, the data type for both C1 and C2 will be VARCHAR2(4000). If data rules are applied to TEST, C1 will be NUMBER and C2 will be of data type VARCHAR2(10).

Name Tab

Use the Name tab to specify the name and description of a table. This tab contains the following fields:

• Name: Represents the name for the table. The name should be unique within the module in which the table is defined.

• Description: Specify an optional description for the table.

Follow the rules in "Naming Conventions for Data Objects" to specify a name and an optional description.

Columns Tab

Use the Columns tab to define the columns in the table. This tab displays a table that you use to define columns. Each row in the table corresponds to the definition of one table column. Warehouse Builder generates the column position in the order in which you type in the columns. To reorder columns, see "Reordering Columns in a Table".

Enter the following details for each column:

• Name: Enter the name of the column. The column name should be unique within the table.

• Data Type: Select the data type of the column from the Data Type list. A default data type is assigned based on the column name. For example, if you create a column named start_date, the data type assigned is DATE. You can change the default assignment if it does not suit your data requirement.

  For a list of supported Oracle Database data types, see "Supported Data Types".

• Length: Specify the length of the column. Length is applicable to character data types only.

• Precision: Specify the total number of digits allowed for the column. Precision is applicable for numeric data types only.

• Scale: Specify the total number of digits to the right of the decimal point. Scale is applicable to numeric data types only.

• Seconds Precision: Used for TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE data types only. Specify the number of digits in the fractional part of the datetime field.

• Not Null: Select this field to specify that the column should not contain null values. By default, all columns in a table allow nulls.

• Default Value: Specify the default value for this column. If no value is entered for this column while you are loading data into the table, the default value is used. If you specify a value for this column while loading data, the default value is overridden and the specified value is stored in the column.

• Virtual: Select this option to indicate that the column is a virtual column.

  Virtual columns are not stored in the database. They are computed using the expression specified in the Expression field. You can refer to virtual columns just like any other column in the table, except that you cannot explicitly write to a virtual column.

• Expression: Specify the expression that is used to compute the value of the virtual column. The expression can include columns from the same table, constants, SQL functions, and user-defined PL/SQL functions.

• Description: Enter an optional description for the column.

Keys Tab

Use the Keys tab to create constraints on the table columns. You can create primary keys, foreign keys, unique keys, and check constraints.

For more information about creating constraints, see "Creating Constraints".

Indexes Tab

Use the Indexes tab to create indexes on the table. Indexes enable faster retrieval of data stored in your data warehouse. You can create the following types of indexes: unique, nonunique, bitmap, function-based, composite, and reverse.

For more information about creating these indexes, see "Creating Indexes".

Partitions Tab

Use the Partitions tab to create partitions for the table. Partitions enable better manageability of larger tables. They also improve query and load performance. You can create the following types of partitions: range, hash, hash by quantity, list, range-hash, range-hash by quantity and range-list.

For more information about partitions and how to create each type of partition, see "Defining Partitions".

Attribute Sets Tab

Use the Attribute Sets tab to create attribute sets for the table.

For more information about creating attribute sets for a table, see "Defining Attribute Sets".

Data Rules Tab

Use the Data Rules tab to apply data rules to a table. Data rules enable you to determine legal data within a table and legal relationships between tables. When you apply a data rule to a table, Warehouse Builder ensures that the data in the table conforms to the specified data rule.

Warehouse Builder provides a set of predefined data rules that are listed in the DERIVED_DATA_RULES node under the Data Rules node of the Projects Navigator. You can define your own data rules by creating data rules under the Data Rules node.

Click Apply Rule to apply a data rule to a table. The Apply Data Rule Wizard is displayed. Use this wizard to select the data rule and the column to which the data rule should be applied.

After you apply a data rule to a table, it is listed on the Data rules tab. For the data rule to be applied to a table, ensure that the check box to the left of the data rule name is selected. Deselect this option if you do not want the data rule to be applied to the table.

Renaming Tables

Use one of the following methods to rename a table:

• On the Name tab of the table editor, click the Name field and enter the new name for the table. You can also modify the description stored in the Description field.

  Alternatively, select the table in the Projects Navigator to display the properties of the table in the Property Inspector. Edit the values of the Physical Name and Description properties.

• In the Projects Navigator, select the table you want to rename and press the F2 key. The table name is highlighted. Enter the new table name and press the Enter key.

• In the Projects Navigator, right-click the table name and select Rename. The table name is highlighted. Enter the new name for the table and press the Enter key.

Adding, Modifying, and Deleting Table Columns

Use the Columns tab of the Table Editor to add, modify, and remove table columns.

Adding Columns 

Navigate to the Columns tab. Click the Name field in an empty row and enter the details that define the new column. For more information, see "Columns Tab".

Modifying Columns 

Use the Columns tab of the Table Editor to modify column definitions. You can modify any of the attributes of the column definition either by entering the new value or selecting the new value from a list. For more information, see "Columns Tab".

Deleting Columns 

Navigate to the Columns tab. Right-click the gray cell to the left of the column name that you want to remove and select Delete.

Adding, Modifying, and Deleting Table Constraints

Navigate to the Keys tab of the Table Editor.

For details on adding and editing constraints, see "Creating Constraints" and "Editing Constraints" respectively.

To delete a constraint, select the row that represents the constraint by clicking the gray cell to the left of the column name. Click Delete at the bottom of the tab.

Adding, Modifying, and Deleting Attribute Sets

Use the Attribute Sets tab of the Table Editor to add, modify, or delete attribute sets in a table.

For details about adding attribute sets, see "Creating Attribute Sets". See "Editing Attribute Sets" for instructions on how to edit an attribute set.

To delete an attribute set, navigate to the Attribute Sets tab. Right-click the cell to the left of the attribute set that you want to remove and select Delete.

Reordering Columns in a Table

By default, columns in a table are displayed in the order in which they are created. This order is also propagated to the DDL script generated to create the table. If this default ordering does not suit your application needs, or if you want to further optimize query performance, you can reorder the columns.

To change the position of a column:

1. If the Table Editor is not already open for the table, open the editor.

   You can do this by double-clicking the name of the table in the Projects Navigator. Alternatively, you can right-click the name of the table in the Projects Navigator and select Open.

2. On the Columns tab, select the gray square located to the left of the column name.

   The entire row is highlighted.

3. Use the buttons on the left of the Columns tab to move the column to the required position.

   The position of the column is now updated.

4. Close the Table Editor.

For the change in the column position to be reflected in the table stored in the workspace you must deploy the changed table definition.

Name Tab

Use the Name tab to modify the name and description that you provided in the Create View dialog box. Ensure that the name and description follow the rules listed in "Naming Conventions for Data Objects".

Columns Tab

Use the Columns tab to define the columns in the view. For each view column, enter the following details: Name, Data Type, Length, Precision, Scale, Seconds Precision, Not Null, Default Value, Virtual, Expression, and Description.

Query Tab

Use the Query tab to define the query used to create the view. A view can contain data from tables that belongs to a different module than the one to which the view belongs. You can also combine data from more than one table using joins.

Ensure that the query statement you type is valid. Warehouse Builder does not validate the text in the Query tab and will attempt to deploy a view even if the syntax is invalid.

Keys Tab

Use the Keys tab to define logical constraints for a view. Although these constraints are not used when enumerating DDL for the view, they can be useful when the view serves as a data source in a mapping. The Mapping Editor can use the logical foreign key constraints to include the referenced dimensions as secondary sources in the mapping.

For more information about creating constraints, see "Creating Constraints".

Attribute Sets Tab

Use the Attribute Sets tab to define attribute sets for the view.

For more information about attribute sets and how to create them, see "Defining Attribute Sets".

Data Rules Tab

Use the Data Rules tab to specify the data rules that are applied to the view. Data rules help to ensure data quality by defining the legal data within a table, or legal relationships between tables.

For more information about the Data Rules tab, see "Data Rules Tab".

Renaming Views

Use the Name tab of the View Editor to rename views. Click the Name field and enter the new name for the view. You can also modify the description stored in the Description field. Enter the new name over the highlighted object name.

Alternatively, select the view in the Projects Navigator to display the view properties in the Property Inspector. Edit the values of the Physical Name and Description properties.

Adding, Modifying, and Deleting View Columns

Use the Columns tab to add, modify, or delete view columns.

Adding columns: On the Columns tab, click the Name field in an empty row and enter the details that define a column. For more information about these details, see "Columns Tab".

Editing columns: Use the Columns tab of the Table Editor to modify column definitions. You can modify any of the attributes of the column definition. For more information, see "Columns Tab".

Removing columns: On the Columns tab, right-click the gray cell to the left of the column name that you want to remove and select Delete.

Adding, Modifying, and Deleting View Constraints

Use the Keys tab of the View Editor to add, modify, or delete view constraints. For details on adding and editing constraints, see "Creating Constraints" and "Editing Constraints" respectively.

To delete constraints, on the Keys tab, select the row that represents the constraint. Click Delete at the bottom of the tab.

Adding, Modifying, and Deleting Attribute Sets

Use the Attribute Sets tab of the View Editor to add, modify, or delete attribute sets. For details about adding attribute sets, see "Creating Attribute Sets". See "Editing Attribute Sets" for instructions on how to edit an attribute set.

To delete an attribute set, navigate to the Attribute Sets tab. Right-click the cell to the left of the attribute set that you want to remove and select Delete.

Columns Tab

Use the Columns tab to define the materialized view columns. For each column, specify the following details: Name, Data Type, Length, Precision, Scale, Seconds Precision, Not Null, Default Value, and Description.

For more information about the details to be provided for each materialized view column, see "Columns Tab".

Query Tab

Use the Query tab to define the query used to create the materialized view. Ensure that you type a valid query in the Select Statement field. For column names, use the same names that you specified on the Columns Tab. If you change a column name on the columns page, you must manually change the name in the Query tab. Warehouse Builder does not validate the text in the Query tab and will attempt to deploy a materialized view even if the syntax is invalid.

Keys Tab

Use the Keys tab to define constraints for the materialized view. Defining constraints is optional. These constraints are for logical design purposes only and are not used when enumerating DDL for the materialized view.

You can create primary keys, foreign keys, and unique keys. For information about creating constraints, see "Creating Constraints".

Indexes Tab

Use the Indexes tab to define indexes on the materialized view. Defining indexes is optional. You can create the following types of indexes: Unique, non-Unique, Bitmap, Function-based, Composite, and Reverse.

For information about creating indexes, see "Creating Indexes".

Partitions Tab

Use the Partitions tab to define partitions on the materialized view. Partitioning a materialized view is optional. You can perform Index Partitioning, Range Partitioning, Hash Partitioning, Hash by Quantity Partitioning, List Partitioning, or Composite Partitioning.

Attribute Sets Tab

Use the Attribute Sets tab to define attribute sets for the materialized view. Defining attribute sets is optional.

For information about how to define attribute sets, see "Creating Attribute Sets".

Data Rules Tab

Use the Data Rules tab to specify data rules that must be applied to the materialized view data. For more information, see "Data Rules Tab".

Renaming Materialized Views

Double-click the Name field on the Name tab of the editor. This selects the name. Type the new name.

Alternatively, select the materialized view in the Projects Navigator to display the materialized view properties in the Property Inspector. Edit the values of the Physical Name and Description properties.

Adding, Modifying, and Deleting Materialized View Columns

Use the Columns tab to add, modify, or delete materialized view columns.

Adding columns: On the Columns tab, click the Name field in an empty row and enter the details for the column. For more information about these details, see "Columns Tab".

Removing columns: On the Columns tab, right-click the gray cell to the left of the column name that you want to remove and select Delete.

Adding, Modifying, and Deleting Materialized View Constraints

Use the Keys tab of the Materialized View Editor to add, modify, or delete materialized view constraints. For details on adding and editing constraints, see "Creating Constraints" and "Editing Constraints" respectively.

To delete a constraint, on the Keys tab, select the row that represents the constraint. Click Delete at the bottom of the tab.

Adding, Modifying, and Deleting Attribute Sets

Use the Attribute Sets tab to add, modify, or delete attribute sets in a materialized view. For details about adding attribute sets, see "Creating Attribute Sets". See "Editing Attribute Sets" for instructions on how to edit an attribute set.

To delete an attribute set, on the Attribute Sets tab, right-click the cell to the left of the attribute set that you want to remove and select Delete.

Defining Primary Key Constraints

To define a primary key constraint:

1. On the Keys tab, click the Add Constraint button.

   A blank row is displayed with the cursor positioned in the Name column.

2. Enter the name of the constraint in the Name column.

   Constraint names must be unique within the module that contains the data object on which the constraint is defined.

3. In the Type column, select Primary Key.

   Press the Tab key to exit from the Type column or use the mouse and click the empty space in the Keys tab.

4. Click Add Local Column.

   A new row is added below the current row that contains the constraint name and constraint type. This new row displays a list in the Local Columns column.

5. In the Local Columns list of the new row, select the name of the column that represents the primary key.

6. (Optional) To create a composite primary key, repeat Steps 4 and 5 for each column that you want to add to the primary key.

Defining Foreign Key Constraints

To define a foreign key constraint:

1. On the Keys tab, click the Add Constraint button.

   A blank row is displayed with the cursor positioned in the Name field.

2. Enter the name of the constraint in the Name column.

   Constraint names must be unique within the module that contains the data object on which the constraint is defined.

3. In the Type column, select Foreign Key.

   Press the tab key to navigate out of the Type column or use the mouse and click the empty space in the Keys tab.

4. In the References column, click the Ellipsis button.

   The Key Selector dialog box is displayed.

5. In the Key Selector dialog box, select the primary key constraint that the foreign key being created references.

   For example, the DEPARTMENTS table has a primary key called DEPT_PK defined on the department_id column. To specify that the column department_id of the EMPLOYEES table is a foreign key that references the primary key DEPT_FK, select DEPT_FK under the node that represents the DEPARTMETNS table in the Key Selector dialog box.

6. Click OK.

Defining Unique Key Constraints

To define a unique key constraint:

1. On the Keys tab, click the Add Constraint button.

   A blank row is displayed with the cursor positioned in the Name field.

2. Enter the name of the constraint in the Name column and press the Enter key.

   You can also press the Tab key or click any other location in the editor.

   Constraint names must be unique within the module that contains the data object on which the constraint is defined.

3. In the Type column, select Unique Key.

   Press the tab key to navigate out of the Type column or use the mouse and click the empty space in the Keys tab.

4. Click Add Local Column.

   A new row is added below the current row that contains the constraint name and constraint type. This new row displays a list in the Local Columns column.

5. In the Local Columns list of the new row, select the name of the column on which a unique key should be created.

6. (Optional) To create a composite unique key, repeat steps 4 and 5 for each column that you want to add to the unique key.

Defining Check Constraints

1. On the Keys tab, click the Add Constraint button.

   A blank row is displayed with the cursor positioned in the Name field.

2. Enter the name of the constraint in the Name column and press the Enter key.

   You can also press the Tab key or click any other location in the editor.

   Constraint names must be unique within the module that contains the data object on which the constraint is defined.

3. In the Type column, select Check Constraint.

   Press the tab key to exit from the Type column or use the mouse and click the empty space in the Keys tab.

4. In the Check Condition column, enter the condition to be applied for the check constraint. For example, salary > 2000. If you leave this field blank, an error is generated during validation and you cannot generate valid code for this constraint.

   The column name referenced in the check condition must exactly match the physical column name defined in the table. Warehouse Builder does not check the syntax of the condition during validation. This may result in errors during deployment. If this happens, check the Repository Browser for details.

Example of Range Partitioning

Figure 2-1 shows how to define a partition for each quarter of a calendar year.

Figure 2-1 Example Table with Range Partitioning

Description of "Figure 2-1 Example Table with Range Partitioning"

You can also partition data for each month or week. When you design mappings using such a table, consider enabling Partition Exchange Loading (PEL). PEL is a data definition language (DDL) operation that swaps existing partitions on the target table with new partitions. Because it is not a data manipulation language (DML) operation, the exchange of partitions occurs instantaneously.

About the Subpartition Template

Use the subpartition template to specify the second partitioning method in composite partitioning. The steps you take depend on the type of composite partition you select.

For range-hash by quantity, enter the number of subpartitions only.

For range-hash, the subpartition template enables you to enter names for the subpartitions only.

For range-list, name the lists and enter comma-separated values. Be sure to preserve the last subpartition as set to DEFAULT.

Figure 2-4 shows a list subpartition based on the REGION key column and subpartitions for groups of countries. Warehouse Builder divides each partition (such as QTR_1 and QTR_2) into subpartitions (such as ASIA and WEST_EUR).

Creating Custom Subpartitions

Using the subpartition template is the most convenient and likely the most common way to define subpartitions. Entries that you specify under the Subpartition Template node apply uniformly to all the partitions under the partition node. However, in some cases, you may want to override the subpartition template.

For range-hash by quantity, select a partition and then click Add Hash Count. Warehouse Builder expands the partition node to enable you to specify the number of hash subpartitions that uniquely apply to that partition.

For range-hash, select a partition and then click Add Subpartition. Warehouse Builder expands the partition node and you can name subpartitions for that partition only.

For range-list, select a partition and then click Add Subpartition. Warehouse Builder expands the partition node to enable you to specify list subpartitions for that partition only. Be sure to preserve the last subpartition as set to DEFAULT.

Figure 2-5 shows that partition QTR_1 is subpartitioned into lists for UK, EUR, and ALL_OTHERS whereas the other quarters are partitioned according to the subpartition template.

Figure 2-5 Subpartitions Overriding Subpartition Template

Description of "Figure 2-5 Subpartitions Overriding Subpartition Template"

Index Performance Considerations

As you decide which type of index to use, consider that indexes rank in performance in the following order:

1. Unique and local index

2. Unique and global index

3. All other non-unique indexes (normal, bitmap, function-based) and local index.

Name Tab

Rename a sequence by typing the new name in the Name field. You can also rename a sequence by right-clicking the sequence name in the Projects Navigator and selecting Rename.

Follow the rules in "Naming Conventions for Data Objects" to specify the name and description.

To modify a sequence description, enter the new description in the Description field.

Columns Tab

The Columns tab displays the sequence columns CURRVAL and NEXTVAL.

Name Tab

Use the Name field to enter a name for the object type. Use the Description field to enter an optional description for the object type. To rename an object type, select the name and enter a new name.

Follow the rules in "Naming Conventions for Data Objects" to specify a name and description.

Columns Tab

Use the Columns tab to define the attributes in the object type. This tab displays a table that you can use to define attributes. Each row in the table corresponds to the definition of one object type attribute.

Specify the following details for each attribute:

• Name: Enter the name of the attribute. The attribute name must be unique within the object type.

• Data Type: Select the data type of the attribute from the Data Type list. Warehouse Builder assigns a default data type for the attribute based on the name. For example, if you create an attribute named start_date, the data type assigned is DATE. You can change the default assignment if it does not suit your data requirement.

  See Also:

  "Supported Data Types" for a list of supported Oracle Database data types

• Length: Specify the length of the attribute. Length is applicable to character data types only.

• Precision: Specify the total number of digits allowed for the attribute. Precision is applicable for to data types only.

• Scale: Specify the total number of digits to the right of the decimal point. Scale is applicable to numeric data types only.

• Seconds Precision: Specify the number of digits in the fractional part of the datetime field. Seconds precision is used for TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE data types.

• Not Null: Select this field to specify that the attribute should not contain NULL values. By default, all columns in a table allow nulls. This column is not applicable to object types.

• Default Value: Specify the default value for this attribute. If no value is entered for this column while data is stored in the table, then the default value is used. If you specify a value for this column while loading data, then the default value is overridden and the specified value is stored in the column. This column is not applicable for object types.

• Virtual: Select this option to indicate that the attribute behaves like a virtual column.

  Virtual columns are not stored in the database. They are computed using the expression specified in the Expression field. You can refer to virtual columns just like any other column in the table, except that you cannot explicitly write to a virtual column.

• Expression: Specify the expression that is used to compute the value of the virtual attribute. The expression can include columns from the same table, constants, SQL functions, and user-defined PL/SQL functions.

• Description: Type an optional description for the attribute.

Name Tab

Use the Name field to enter a name for the Varray. Use the Description field to enter an optional description for the Varray. To rename a Varray, select the name and enter a new name.

Follow the rules in "Naming Conventions for Data Objects" to specify a name and an optional description.

Details Tab

Use the Details tab to specify the value for the following fields:

• Data Type: Select the data type of the attribute from the Data Type list.

  See Also:

  "Supported Data Types" for a list of supported Oracle Database data types.

• Length: Specify the length of the Varray element. Length is applicable for character data types only.

• Precision: Specify the total number of digits allowed for the Varray element. Precision is applicable to numeric data types only.

• Scale: Specify the total number of digits to the right of the decimal point. Scale is applicable to numeric data types only.

• Seconds Precision: Specify the number of digits in the fractional part of the datetime field. Seconds precision is used for TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE data types only.

• Size: Specify the size of the Varray.

Name Tab

Use the Name field to enter a name for the nested table. Use the Description field to enter an optional description for the nested table. To rename a nested table, select the name and enter a new name.

Follow the rules in "Naming Conventions for Data Objects" to specify a name and an optional description.

Details Tab

Use the Details tab to specify the value for the following fields:

• Data Type: Select the data type of the attribute from the Data Type list.

  See Also:

  "Supported Data Types" for a list of supported data types

• Length: Specify the length of the nested table element. Length is applicable for character data types only.

• Precision: Specify the total number of digits allowed for the nested table element. Precision is applicable to numeric data types only.

• Scale: Specify the total number of digits to the right of the decimal point. Scale is applicable to numeric data types only.

• Seconds Precision: Specify the number of digits in the fractional part of the datetime field. Seconds precision is used for TIMESTAMP,TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE data types only.

Defining the Payload Type of Queue Tables

Use the Create Queue Table dialog box or the Edit Queue Table dialog box to provide additional details about the queue table such as the payload type and order in which the messages in the queue should be sorted. The following section describes the details to be provided for a queue table.

Name

The Name field represents the name of the queue table. The name should be unique within the Oracle module containing that queue table. For more details, see "Naming Conventions for Data Objects".

Description

Use the Description field to provide an optional description for the queue table.

Payload Type

Each queue contains a payload that stores the actual data that is to be transferred. The Payload Type represents the type of data that is permitted in the queue table.

You can select one of the following options for the payload type: Object Type, SYS.ANYDATA, RAW, SYS.AQ$_JMS_BYTES_MESSAGE, SYS.AQ$_JMS_MAP_MESSAGE, SYS.AQ$_JMS_MESSAGE, SYS.AQ$_JMS_STREAM_MESSAGE, SYS.AQ$_JMS_TEXT_MESSAGE, and XMLTYPE.

Search For

Use the Search For field to search for an object. This field is enabled only when you select Object Type as the Payload Type.

The area below the Search For field displays the object types in your workspace. Object types are listed under the module to which they belong. To search for an object with a specific name, enter the name of the object in the Search For field and click Go.

Enable Transactional Property for Messages

Select Enable Transactional Property for Messages to enable message grouping. This option is enabled for all object-typed queues except SYS.ANYDATA queues.

Message grouping enables messages belonging to one queue to be grouped so that they form a set that can only be consumed by one user at a time. All messages belonging to a group must be created in the same transaction, and all messages created in one transaction belong to the same group.

This feature enables users to segment complex messages into simple messages. It is also useful if the message payload contains complex large objects such as images and video that can be segmented into smaller objects.

Secured Queue Table

Select Secured Queue Table if you want to create a secure queue. This option is enabled for all object-typed queues except SYS.ANYDATA queues.

Secure queues are queues for which Oracle Streams Advanced Queuing (AQ) agents must be associated explicitly with one or more database users who can perform queue operations, such as enqueue and dequeue. The owner of a secure queue can perform all queue operations on the queue, but other users cannot perform queue operations on a secure queue, unless they are configured as secure queue users.

Sort Messages By

Use the Sort Messages By list to specify the order in which the messages contained in the payload should be sorted. The options you can use to sort messages are:

• Enqueue_time: Sorts by the arrival time of the message.

• Priority: Sorts by message priority.

• Enqueue_time, priority: Sorts by the arrival time of the message and, for messages with the same arrival time, sorts by message priority.

• Priority, enqueue_time: Sorts by message priority and, for messages with the same priority, sorts by arrival time of the message.

Specifying the Queue Table on which the AQ is Based

Use the Create Advanced Queue dialog box to specify details such as the advanced queue name and the queue table on which the AQ is based. You can choose an existing queue table or create a new one. Use the Edit Advanced Queue dialog box to modify the name, description, or the queue table on which the AQ is based.

Name

The Name field represents the name of the advanced queue. The name should be unique within the Oracle module containing that advanced queue. For more details, see "Naming Conventions for Data Objects".

Description

Use the Description field to provide an optional description for the advanced queue.

Select a Queue Table

Use the Select a Queue Table list to select the queue table that stores messages contained in the advanced queue.

Create New Queue Table

Select Create New Queue Table to create a new queue table that will contain the advanced queue data. The Create Queue Table dialog box that guides you through the process of defining a queue table is displayed. For more information about defining queue tables, see "Creating Queue Table Definitions".

Selecting a Target Queue for Propagation

Use the Create Queue Propagation dialog box or the Edit Queue Propagation dialog box to specify the target queue for propagation. The following sections describe the fields contained in this page.

Name

The Name field represents the name of the queue propagation. The name should be unique within the advanced queue under which the queue propagation is defined.

To rename a queue propagation, select the name and enter the new name.

Description

The Description field represents the description of the queue propagation. Providing a description is optional.

Select a Target Queue for Propagation

Use the Select a Target Queue for Propagation section to define the target queue. The area below this section displays a node tree containing the advanced queues defined in the current project. Select the advanced queue which will be used as a target. Messages from the AQ under which you define the queue propagation can then be propagated to the AQ defined as the target queue.

Use the Search For field to search for a particular object using the object name. Enter the name of the object in this field and click Go.

Deployment System Type

PL/SQL Generation Mode: Defines the target database type. The options you can select are: Default, Oracle 10g, Oracle10gR2, Oracle11gR1, Oracle11gR2, Oracle8i, and Oracle9i. Code generation is based on your choice in this field. For example, select Oracle 9i to ensure the use of Oracle 9i code constructs. If you select Oracle8i, row-based code is generated.

Each release introduces new functionality, some of which you may use only in conjunction with the latest version of the Oracle Database. For example, if you select Oracle8i as the PL/SQL Generation Mode, you cannot access some Oracle 9i Warehouse Builder components such as table functions and external tables.

Generation Preferences

End of Line: Defines the end of line markers for flat files. This depends on the platform to which you are deploying your warehouse. For UNIX, use \n, and for Windows NT, use \r\n.

Generation Target Directories

ABAP Extension: File name extension for ABAP scripts. The default is .abap.

ABAP Run Parameter File: Suffix for the parameter script in an ABAP job. The default is _run.ini.

ABAP Spool Directory: The location where ABAP scripts are buffered during script generation processing.

DDL Directory: Enter a location for the scripts that create database objects in the target schema. The default is ddl\.

DDL Extension: Enter a file name extension for DDL scripts. The default is .ddl

DDL Spool Directory: Enter a buffer location for DDL scripts during the script generation processing. The default is ddl\log.

LIB Directory: Enter a location for the scripts that generate Oracle functions and procedures. The default is lib\.

LIB Extension: Enter a suffix to be appended to a mapping name. The default is .lib.

LIB Spool Directory: Enter a location for the scripts that generate user-defined functions and procedures. The default is lib\log\.

LOADER Directory: Enter a location for the control files. The default is ctl\.

LOADER Extension: Enter a suffix for the loader scripts. The default is .ctl.

LOADER Run Parameter File: Enter a suffix for the parameter initialization file. The default is _run.ini.

PL/SQL Directory: Enter a location for the PL/SQL scripts. The default is pls\.

PL/SQL Run Parameter File: Enter a suffix for the parameter script in a PL/SQL job. The default is _run.ini.

PL/SQL Spool Directory: Enter a buffer location for PL/SQL scripts during the script generation processing. The default is pls\log\.

PL/SQL Extension: Enter a file name extension for PL/SQL scripts. The default is .pls.

SQLPlus Directory: Enter a location for the PL/SQL scripts. The default is sql\.

SQLPlus Extension: Enter a file name extension for PL/SQL scripts. The default is .sql.

SQLPLus Run Parameter File: Enter a suffix for the parameter script in a PL/SQL job. The default is _run.ini.

Staging File Directory: For all ABAP configuration related to SAP tables. The default is abap\.

Tcl Directory: Enter a location for the tcl scripts. The default is tcl\.

Identification

Application Short Name: This parameter is obsolete and is no longer used.

Deployable: Select this option to indicate that the objects contained in the module can be deployed.

Location: Represents the location with which the module is associated. If the module is a source module, this value represents the location from which the data is sourced. If the module is a target module, this value represents the location to which the generated code and object data are deployed.

Main Application Short Name: This parameter is obsolete and is no longer used.

Top Directory: Represents the name of the directory to which the generated code is stored. The default value for this parameter is ..\..\codegen\. You can change this value to any directory in which you want to store generated code.

Run Time Directories

Archive Directory: Not currently used. The default is archive\.

Input Directory: Not currently used. The default is input\.

Invalid Directory: Directory for SQL*Loader error and rejected records. The default is invalid\.

Log Directory: Log directory for the SQL*Loader. The default is log\.

Receive Directory: Not currently used. The default is receive\.

Sort Directory: Not currently used. The default is sort\.

Work Directory: Not currently used. The default is work\.

Tablespace Defaults

Default Index Tablespace: Defines the name of each tablespace where indexes are created. The default is null. If you configure an index tablespace at the target module level and not at the object level, the tablespace value configured at the target module level is used during code generation. If you configure a tablespace for each index at the object level, the tablespace value configured at the target module level is overwritten.

Default Object Tablespace: Defines the name of each tablespace where objects are created (for example, tables, views, or materialized views). The default is null. If you configure object tablespace at the target module level and not at the individual object level, the value configured at the target module level is used during code generation. If you configure a tablespace for each individual object, the tablespace value configured at the target module level is overwritten.

Error Table

Error Table Name: Indicates the name of the error table that stores the rows that were not loaded into the table during a load operation.

Tablespace: Indicates the name of the tablespace in which the error table is stored.

Foreign Keys

The Foreign Keys node is displayed if your table contains a foreign key definition. A separate node is displayed, under the Foreign Keys node, for each foreign key in the table. Under this node, parameters are listed under the following categories: Creation Method and Identification.

The Creation Method category contains the following parameters:

• Constraint Checking: Indicates if the checking of this constraint can be deferred until after the transaction is committed. Set this parameter to DEFERABLE to indicate that, in subsequent transactions, you can set the SET CONSTRAINTS clause to defer checking this constraint until after the transaction is committed. Set this parameter to NOT DEFERABLE to indicate that you cannot use the SET CONSTRAINTS clause ti defer checking this constraint until after the transaction is committed. The default is NOT DEFERABLE.

• Constraint State: Indicates if the constraint should be enabled. Select ENABLE to apply the constraint to the table data. Select DISABLE to disable the integrity constraint. The default is ENABLE.

• Constraint Validation: The options you can set are NOVALIDATE or VALIDATE. The effect of setting this parameter is different based on whether the constraint is enabled or disabled.

• EXCEPTIONS INTO: Indicates the name of the exceptions table. You cannot use this parameters when you set NOVALIDATE as the Constraint Validation.

• INITIALLY: The options you can set for this parameter are IMMEDIATE or DEFERRED. The default setting is IMMEDIATE. IMMEDIATE indicates that a deferrable constraint must be checked at the end of each SQL statement. DEFERRED indicates that a deferrable constraint must be checked at the end of subsequent transactions.

• NOVALIDATE mode: The options you can set for this parameter are NORELY and RELY, with NORELY being the default setting. Setting this parameter to RELY activates a constraint in NOVALIDATE mode for query rewrite.

• ON DELETE: During a delete operation, this parameter indicates how to handle foreign keys that depend on the record being deleted. The options you can set for this parameter are CASCADE or SET NULL. CASCADE deletes dependent foreign key values. NOT NULL sets the dependent foreign key values to null.

Identification

Deployable: Select this option to indicate that you want to deploy this table. Scripts are generated only for tables marked deployable.

Error Table Only: Select this option to perform generation or deployment actions only on the error table associated with the table. Use this option to add an error table to an existing table. This setting only controls the actions of the Deployable parameter, but does not override it.

Deselect this option to deploy the error table along with the table.

Parallel

Parallel Access Mode: Enables parallel processing when the table has been created. The default is PARALLEL.

Parallel Degree: Indicates the degree of parallelism. This is the number of parallel threads used in the parallel operation.

Performance Parameters

Buffer Cache: Indicates how Oracle Database should store rows in the buffer cache.

Data Segment Compression: Indicates whether data segments should be compressed. Compressing reduces disk use. The default is NOCOMPRESS.

Logging Mode: Indicates whether the DML actions are logged in the redo log file. To improve performance, set this parameter to NOLOGGING. The default is LOGGING.

Row-level Dependency: Indicates whether row-level dependency tracking.

Row Movement: Indicates if Oracle Database can move a table row.

Statistics Collection: Indicates if statistics should be collected for the table. Specify MONITORING if you want modification statistics to be collected on this table.

Partition Parameters

Partition Tablespace List: Specify a comma-separated list of tablespaces. For simple partitioned objects, it is used for a HASH BY QUANTITY partition tablespace. For composite partitioned tables, it is used for subpartition template to store the list of tablespaces.

Overflow Tablespace List: Specify a comma separated list of tablespaces for overflow data. For simple partitioned objects, it is used for HASH BY QUANTITY partition overflow tablespaces. The number of tablespaces does not have to equal the number of partitions. If the number of partitions is greater than the number of tablespaces, then Oracle Database cycles through the names of the tablespaces.

Storage Space

Storage parameters enable you to define how the table is stored in the database. This category contains parameters such as BUFFER_POOL, FREELIST GROUPS, FREELISTS, INITIAL, MINEXTENTS, MAXEXTENTS, NEXT, and PCTINCREASE.

The Tablespace parameter defines the name of each tablespace where the table is created. The default value is null. If you accept the default value of null, the table is generated based on the tablespace value set in the configuration parameters of the target module. If you configure the tablespace for individual objects, the tablespace value configured for the target module is overwritten.

Change Data Capture

Enable: Indicates if change data capture is enabled for the table. Select True for this parameter to enable change data capture for the table.

Table Position: Indicates the position of the table in the change data capture.

Materialized View Parameters

The following are parameters for materialized views:

• Base Tables: Specify a comma-separated list of base tables referenced by the materialized view. Separate each table name with a comma. If a table name is not in uppercase, enclose the name in double quotation marks.

• BUILD: Indicates when the materialized view is populated. The options are Immediate (default), Deferred, and Prebuilt.

  Immediate: Populates the materialized view when it is created.

  Deferred: Delays the population of the materialized view until the next refresh operation. You can select this option when you are designing a materialized view and the metadata for the base tables is correct but the data is not.

  Prebuilt: Indicates that the materialized view is prebuilt.

• Default Rollback Segment: The options are DEFAULT, DEFAULT MASTER, DEFAULT LOCAL, and NONE. The default setting is DEFAULT LOCAL. Specify DEFAULT to indicate that Oracle Database should choose which rollback segment to use. Specify DEFAULT MASTER for the remote rollback segment to be used at the remote site. Specify DEFAULT LOCAL for the remote rollback segment to be used for the local refresh group that contains the materialized view. Specify NONE to name both master and local segments.

• FOR UPDATE: Select Yes to allow a subquery, primary key, row ID, or object materialized view to be updated. The default setting is No.

• Local Rollback Segment: Specify a named remote rollback segment to be used for the local refresh group of the materialized view. The default is null.

• Master Rollback Segment: Indicates the name of the remote rollback segment to be used at the remote master site for the materialized view.

• NEXT (date): Indicates the interval between automatic refreshes. Specify a datetime value for this parameter.

• Query Rewrite: Indicates if the materialized view is eligible for query rewrite. The options are ENABLE and DISABLE. The default is DISABLE.

  Enable: Enables query rewrite. For other query rewrite requirements, see "Fast Refresh for Materialized Views".

  Disable: Disables query rewrite. You can disable query rewrite when you know that the data in the materialized view is stale or when you want to make changes to the query statement.

• REFRESH: Indicates the refresh method. The options are Complete, Fast, Force, and Never. The default setting is Force.

  Complete: Oracle Database truncates the materialized view and reexecutes the query upon refresh.

  Fast: Uses materialized views to only apply changes to the base table data. There are many requirements for fast refresh to operate properly. For more information, see "Fast Refresh for Materialized Views".

  Force: Oracle Database attempts to refresh using the fast mode. If unable to refresh in fast mode, the Oracle server reexecutes the query upon refresh.

  Never: Prevents the materialized view from being refreshed.

• Refresh On: The options are COMMIT and DEMAND. Specify COMMIT to indicate that a fast refresh is to occur whenever the database commits a transaction that operates on a master table of the materialized view. Specify DEMAND to indicate that the materialized view should be refreshed on demand. You can do this by using one of the refresh procedures of the DBMS_MVIEW package. The default setting is DEMAND.

• Start With: Indicates the first automatic refresh time. Specify a datetime value for this parameter.

• Using Constraints: The options that you can select for this parameter are TRUSTED or ENFORCED. Select TRUSTED to allow Oracle Database to use dimension and constraint information that has been declared trustworthy by the DBA but has not been validated by Oracle Database. ENFORCED is the default setting.

• WITH: Select PRIMARY_KEY to create a primary key materialized view. Select ROWID to create a ROWID materialized view. The default setting is PRIMARY_KEY.

Performance Parameters

Buffer Cache: Indicates how the blocks retrieved for this table are placed in the buffer cache. The options you can select are CACHE or NOCACHE. When you select CACHE, the blocks 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. Setting the Buffer Cache parameter to CACHE is useful for frequently accessed tables, such as small lookup tables. When you select NOCACHE, the blocks are placed at the least recently used end of the LRU list in the buffer cache.

Data Segment Compression: Indicates if segments should be compressed on disk to reduce space usage. The options you can set for this parameter are COMPRESS, COMPRESS ALL, or NOCOMPRESS. The default is NOCOMPRESS. Set this parameter to COMPRESS to compress data only during a direct path INSERT, when it is productive to do so. Set this parameter to COMPRESS ALL compresses data during all DML operations on the table.

Logging Mode: Indicates whether the DML actions are logged in the redo log file. To improve performance, set this parameter to NOLOGGING. The default is LOGGING.

Error Table

• Error Table Name: Indicates the name of the error table that stores the rows that were not loaded into the table during a load operation.

• Tablespace: Indicates the name of the tablespace in which the error table is stored.

Parallel

• Parallel Access Mode: Enables parallel processing when the table has been created. The default is PARALLEL.

• Parallel Degree: Indicates the degree of parallelism. This is the number of parallel threads used in the parallel operation.

Identification

• Deployable: Select TRUE to indicate if you want to deploy this materialized view. Warehouse Builder generates scripts only for materialized views marked deployable.

• Error Table Only: Select this option to perform generation or deployment actions only on the error table associated with the materialized view. Use this option to add an error table to an existing materialized view. This setting controls the actions of the Deployable parameter, but does not override it.

  Deselect this option to deploy the error table along with the materialized view.

Hash Partition Parameters

• Hash Partition Tablespace List: Indicates the tablespace that stores the partition or subpartition data. To specify multiple tablespaces, use a comma-separated list.

Materialized View Log Parameters

You can configure the following materialized view log parameters.

Record Primary Key: Select PRIMARY KEY to indicate that the primary key of all rows changed should be recorded in the materialized view log.

Record ROWID: Select ROWID to indicate that the row ID of all rows changed should be recorded in the materialized view log.

Record SEQUENCE: Select SEQUENCE to indicate that a sequence value providing additional ordering information should be recorded in the materialized view log.

Sequence numbers are necessary to support fast refresh after some update scenarios.

COLUMNS: Specify the columns whose values you want to be recorded in the materialized view log for all rows that are changed. Typically these columns are filter columns and join columns.

Generate Materialized View Log: Select YES to generate DDL for materialized view log. The default is YES.

New Values: Specify INCLUDING to save both new and old values in the log. Specify EXCLUDING to disable the recording of new values in the log. EXCLUDING is the default.

Fast Refresh for Materialized Views

You can configure a materialized view to refresh incrementally. When you update the base tables for a materialized view, the database stores updated record pointers in the materialized view log. Changes in the log tables are used to refresh the associated materialized views.

To ensure incremental refresh of materialized views, verify the following conditions:

• The Refresh parameter must be set to Fast and the Base Tables parameter must list all base tables.

• Each base table must have a PK constraint defined. Warehouse Builder generates a create statement based on the PK constraint and utilizes that log to refresh the dependent materialized views.

• The materialized view must not contain references to nonrepeating expressions such as SYSDATE, ROWNUM, and nonrepeatable PL/SQL functions.

• The materialized view must not contain references to RAW and LONG RAW data types.

• There are additional restrictions for materialized views with statements for joins, aggregations, and unions. For information about additional restrictions, see Oracle Database Data Warehousing Guide.