Validation

• ODI Version 12.2.1.4.200721.1540
• Oracle DB System 19.7.0.0

Introduction

This article describes the steps required to enable Oracle Partition Exchange in Oracle Data Integrator (ODI).

The article features a new ODI knowledge module (KM), the IKM Oracle Partition Exchange Load, which uses the Oracle Partition Exchange technology to upload data, by partition or subpartition, into a partitioned table of an Oracle database.

This KM supports three partitioning strategies:  range, hash, and list.  Single-level partitioning and composite partitioning (sub-partitions) are supported by this KM as well.

Additionally, the KM includes options to support table maintenance operations that may be required before and after a partition exchange operation.  Some of these KM options include flow control, disabling and enabling table constraints, rebuilding local and global indexes, and gathering table statistics.

If you would like to download a copy of this knowledge module, go to Oracle Data Integrator Code Samples and download "IKM Oracle Partition Exchange Load."  Additionally, an ODI 12.1.3 repository with examples can be found at “ODI Repository Sample for Oracle Partition Exchange.”

For an overview of the benefits of using Oracle Partition Exchange with ODI and how to use the IKM Oracle Partition Exchange Load to perform data uploads for large partitioned tables, go to “Using Oracle Partition Exchange with Oracle Data Integrator (ODI).”

Main Article: Configuring Oracle Data Integrator (ODI) with Oracle Partition Exchange

In order to configure ODI with Oracle Partition Exchange, the user must perform three configuration tasks:

• Configure the IKM Oracle Partition Exchange Load options
• Configure the database privileges required for Oracle Partition Exchange
• Add the table partitions of the partitioned table into Oracle Data Integrator

The following sections describe how to perform these three configuration tasks.

Configuring the IKM Oracle Partition Exchange Load Options

The IKM Oracle Partition Exchange Load offers various options to efficiently manage your partition exchange upload operations.  Figure 1 shows a list of the KM options with default values.

Figure 1 - IKM Oracle Partition Exchange Load Options

The default values for the KM options are as follow:

• The Partition Name option uses the ODI getPop method to get the partition name from the Partition/Sub-Partition option – a property of the datastore in the logical design of the ODI mapping.
• The Exchange Type option has been set to PARTITION; thus, the partition exchange operation will be done at the partition level.  The user can modify this value to perform the exchange operation of a subpartition as well – by setting the value to SUBPARTITION.
• The Degree of Parallelism option has been set to PARALLEL; thus, the Oracle database will determine the most optimum degree of parallelism to be used when loading data into the flow table.
• The Select Optimizer Hint option has no value, but the user can specify an optimizer hint to speed up the query that selects data from the source datastores.
• The Flow Control option has been set to true; thus, the data in the flow table will be validated before the partition exchange operation.
• The Lock Partition option has been set to false.  The partition will not be locked before the exchange operation.
• The Partition Exchange Options have been configured to perform the exchange operation with validation; thus, Oracle will ensure that the data to be exchanged belongs in the partition you exchange.
• The Delete Temporary Objects option has been set to true.  All temporary objects will be deleted once the KM completes its execution successfully.
• All other KM options have been set to false.

All these options can be configured, changed, and customized by the user.  Some of these options can be configured with ODI variables as well.  The following sections describe in detail how to configure these KM options.

Partition Name

This option allows the user to specify the partition or subpartition name that the knowledge module will use to upload data into a partitioned table.  The user can specify the partition or subpartition name in three different ways:

• Use the default value for this option and specify the partition or subpartition name in the logical diagram of the ODI mapping.
• Use an ODI variable that contains the partition or subpartition name, so that the name can be set dynamically.
• Type in the actual partition or subpartition name (hard-coded value).

Using the Default Value

• Figure 2 shows the default value for this option:  <%=odiRef.getPop("PARTITION_NAME")%>.
• The default value uses the ODI getPop method to get the partition or subpartition name from a parameter value called PARTITION_NAME.
• PARTITION_NAME is the value found in the Partition/Sub-Partition option of the ODI datastore.
• The Partition/Sub-Partition option can be found in the Logical Diagram of the ODI mapping, under the General properties of the datastore (the partitioned table).
• If the user chooses to use the default value for this option, the database-defined partitions/subpartitions must be imported into the ODI Model, and the partition or subpartition name must be selected in the Partition/Sub-Partition option of the datastore.
• For detailed instructions on how to import the database-defined partitions/subpartitions into your ODI Models and how to set the Partition/Sub-Partition option for an ODI datastore, see section “Adding Your Table Partitions in Oracle Data Integrator”.

Figure 2 - Knowledge Module Option – Partition Name Default Value

Using an ODI Variable

• If the user chooses to use an ODI variable for this option, the variable name must be prefixed with the ODI project code.
• Figure 3 shows an example of how to specify an ODI variable for this option.
• There is no need to import the database-defined partitions if an ODI variable is used with this option.

Figure 3 - Knowledge Module Option – Partition Name Variable

Using a Hard-coded Value

• If the user chooses to specify the actual partition or subpartition name, only the specified partition or subpartition can be used to upload data into the partitioned table.
• Figure 4 shows an example of a partition name used for this option: JUL2014.

Figure 4 - Knowledge Module Option – Partition Name

Exchange Type

This option allows the user to specify the type of exchange to be performed:  PARTITION or SUBPARTITION.

Use PARTITION if the exchange operation is for a partition.  Use SUBPARTION if the exchange operation is for a subpartition.  The value for this option can be hard-coded or an ODI variable containing the exchange type can be used.

Figure 5 shows the default value for this option.  By default, this option is set to PARTITION.  An ODI variable can be used with this option.

Figure 5 - Knowledge Module Option – Exchange Type

Degree of Parallelism

This option allows the user to specify the degree of parallelism (DOP) or the number of parallel threads that the Oracle database will use when inserting data into the ODI flow table.

The value for this option can be hard-coded or an ODI variable containing the degree of parallelism can be used instead.

Figure 6 shows the default value for this option: PARALLEL.  When using the default value for this option, the degree of parallelism is determined by the Oracle database as follow:

Degree of Parallelism = Number of CPUs available on all participating database instances * PARALLEL_THREADS_PER_CPU initialization parameter

Figure 6 - Knowledge Module Option – Degree of Parallelism

Optionally, the user can specify a number of parallel threads that the Oracle database will use when inserting data into the ODI flow table.  The syntax to specify the number of parallel threads is as follow:

PARALLEL <number_of_threads>

The <number_of_threads> is an integer value.  Each parallel thread may use one or two parallel execution servers.  Usually, the Oracle database calculates the optimum degree of parallelism; thus, it is not necessary to specify the number of threads.

For additional information on how use the degree of parallelism in an Oracle database, go to “Database VLDB and Partitioning Guide, Degree of Parallelism.”

Also, a white paper article on how to use the degree of parallelism in the Oracle database 12c can found at “Parallel Execution with Oracle Database 12c Fundamentals”.

Select Optimizer Hint

Oracle Optimizer Hints offer a mechanism to instruct the Oracle Optimizer to choose a certain query execution plan based on a criteria specified by the user.  Oracle Optimizer Hints can offer great performance benefits when they are used in conjunction with ODI knowledge modules.

The KM uses an INSERT AS SELECT (IAS) statement to populate the ODI flow table.  This KM option allows the user to specify an Optimizer hint after the SELECT keyword of the IAS statement.

By default, this option has no value.  The user can specify the actual Optimizer hint or use an ODI variable containing the hint.  The syntax for defining an Oracle Optimizer Hint is as follow:

/*+ hint [text] [hint[text]]... */

For example, Figure 7 shows an access path hint that instructs the Oracle Optimizer to use an index (ORDER_DT_IDX) when selecting data from a table called ORDER_DETAIL.

Figure 7 - Knowledge Module Option – Select Optimizer Hint

When changes are made to the database, Optimizer hints can become obsolete or even have a negative impact on the execution of the IAS statement; thus, it is recommended to store the Optimizer hints in ODI variables.  Alternatively, a database configuration table can be used to maintain the Optimizer hints, and the ODI variables can source the hint information from this configuration table.

Figure 8 shows an example of how to specify an ODI variable with this option.

Figure 8 - Knowledge Module Option – Select Optimizer Hint - ODI Variable

For additional information on how to use Oracle Optimizer Hints, refer to “Oracle Database Performance Tuning Guide, Using Optimizer Hints.”

Flow Control

If set to True, this option invokes the check knowledge module (CKM) for the Oracle technology (CKM Oracle) to validate the data stored in the ODI flow table before the partition exchange operation.

The data stored in the ODI flow table is validated against the constraints of the partitioned table.  If invalid records are found in the flow table, they are copied into an error table and removed from the flow table before the partition exchange operation.

Set this option to true if the data in the flow table is not guaranteed to meet the constraints of the partitioned table, and invalid records must be removed from the flow table before the exchange operation.

If the data stored in the flow table does not meet the constraints of the partitioned table and this KM option is not set to true, the partition exchange operation may fail.

Set this option to false if the data stored in the flow table is guaranteed to meet the constraints of the partitioned table.

Figure 9 shows the default value for this option.  By default, this option is set to true.

Figure 9 - Knowledge Module Option – Flow Control

Table constraints such as check constraints, not null constraints, primary keys, alternate keys, and unique keys must be reverse-engineered in ODI prior using the check knowledge module.

For additional information on how to use check knowledge modules and reverse-engineering knowledge modules (RKM), go to “Oracle Fusion Middleware Knowledge Module Developer's Guide for Oracle Data Integrator.”

Lock Partition

If set to True, this option locks the partition or subpartition of the partitioned table before the partition exchange command is executed.

If the KM Exchange Type option is set to PARTITION, the locking of the partitioned table is done at the partition level only.   If the table is composite-partitioned, then the database locks all the subpartitions of the specified partition.

If the KM Exchange Type option is set to SUBPARTITION, the locking of the partitioned table is done at the subpartition level only.

The knowledge module instructs the Oracle database to lock the partition or subpartition in share mode.  Hence, users can query the locked partition or subpartition.  When the partition or subpartition is locked, no other updates can be performed against the locked partition or subpartition.  The locked partition or subpartition will remain locked until the partition exchange operation is complete.

Figure 10 shows the default value for this option.  By default, this option is set to false.

Figure 10 - Knowledge Module Option – Lock Partition

Partition Exchange Options

During the partition exchange operation, additional options can be specified to control the behavior of the exchange operation.  For instance, Oracle can ensure that data from the ODI flow table belongs to the partition to be exchanged, or local indexes in the ODI flow table can be exchanged with the partitioned table.  Other operations such as updating global indexes during the exchange operation are supported as well.

Table 1 shows a list of options that can be specified as part of the partition exchange operation.

Table 1 - Partition Exchange Options

Figure 11 shows the default value for this option.  By default, data will be exchanged with validation.  It is recommended to review each of these values, and choose the one that works best for your ELT environment.  An ODI variable can be used with this KM option.

Figure 11 - Knowledge Module Option – Partition Exchange Options

Set Incremental Statistics

When data is added into a partitioned table, two types of statistics should be gathered:  partition-level statistics, and global statistics.  For large tables, the task of gathering global statistics is a resource-intensive and time-consuming operation, since a full table scan is required.

Starting with Oracle Database 11g, a new feature was introduced to improve the performance of gathering global statistics on large partitioned tables: Incremental Statistics.  The Incremental Statistics feature gathers separate statistics for each partition.  It then updates the global statistics by scanning only those partitions that have been modified.  Global statistics are generated by aggregating the partition-level statistics, thus eliminating the need for performing a full table scan on the partitioned table.

Figure 12 shows an example of how Incremental Statistics are gathered for a partitioned table called Orders Fact.  In this example, seven partition-level statistics are gathered first.  Then, global statistics are generated by aggregating the seven partition-level statistics.

Figure 12 - Oracle Incremental Statistics

If this KM option is set to true, the knowledge module will change the statistics preference for the partitioned table to incremental; thus, global statistics for the partitioned table will be gathered incrementally as illustrated on Figure 12, above.

If this KM option is set to false, and the statistics preference for the partitioned table is not set to incremental, a full table scan may be performed by the database to maintain the global statistics.

If the statistics preference for the partitioned table is already set to incremental (in the database), and this KM option is set to false, the statistics preference for the partitioned table is not modified; thus, global statistics for the partitioned table will be gathered incrementally.

Figure 13 shows the default value for this option.  By default, this option is set to false.

Figure 13 - Knowledge Module Option – Set Incremental Statistics

To check the statistics preference of a partitioned table, type the following SQL statement:

select dbms_stats.get_prefs('INCREMENTAL','<SCHEMA_NAME>','<PARTITIONED _TABLE_NAME>') from dual

For additional information on Incremental Statistics, go to Gathering Incremental Statistics on Partitioned Objects.  For additional information on table preferences, go to Setting Table Preferences in Oracle.

Additional statistics preferences can be defined for a schema, or database.   Global statistics preferences are available as well.  For additional information on how to set statistics preferences, see the Oracle SET*PREFS procedures at Oracle DBMS_STATS Sub-Programs

Set Publish Statistics

If set to true, this option modifies the publish preference for the partitioned table to true.  The publish preference for a table is used by the Oracle database to determine if newly gathered statistics can be published immediately into the dictionary tables.

Starting with Oracle Database 11g, Release 1, users have the ability to gather statistics and delay their publication.  This new table preference allows users to test the new statistics before publishing them.  Set this KM option to true if you wish to publish newly gathered statistics immediately.

The Oracle database also requires this option to be set to true if you wish to gather incremental statistics for the partitioned table.

If the publish preference for the partitioned table is already set to true (in the database), and this KM option is set to false, the publish preference for the partitioned table is not modified; thus, newly gathered statistics will be published immediately into the database dictionary tables.

Figure 14 shows the default value for this option.  By default, this option is set to false.

Figure 14 - Knowledge Module Option – Set Publish Statistics

Disable Constraints before Exchange

If set to true, this option disables the integrity constraints of the partitioned table before the partition exchange operation.

If this option is set to false, and the integrity constraints are enabled in the database, Oracle performs the partition exchange operation with validation to maintain the integrity of the constraints; thus, increasing the time it takes to perform the exchange operation.

If the user is confident that the data to be exchanged belongs to the partition and the data does not violate the integrity constraints of the partitioned table, then it is recommended to set this option to true, and perform the exchange operation without validation.

Figure 15 shows the default value for this option.  By default, this option is set to false.

Figure 15 - Knowledge Module Option – Disable Constraints before Exchange

If the user plans to perform an initial upload for the partitioned table and multiple partitions will be loaded in parallel, it is recommended to disable the integrity constraints for the entire initial upload operation.  The integrity constraints can be re-enabled once all partitions have been loaded successfully.

If an incremental load for the partitioned table is performed and a single partition is loaded, the integrity constraints can be disabled and enabled, respectively, before and after the exchange operation.  However, if the incremental load operation can be performed with enabled constraints and the performance of the upload operation is acceptable, then the integrity constraints of the partitioned table should not be disabled.

See the KM option called Enable Constraints after Exchange for details on how to enable integrity constraints after the exchange operation.

The knowledge module disables only those integrity constraints with a current status of enabled.

The integrity constraints of the partitioned table are disabled before the creation of the ODI flow table.  Disabled integrity constraints are not added into the ODI flow table in order to ensure a successful exchange operation.

Additional database privileges may be required when disabling integrity constraints for the partitioned table.  See section “Configuring Your Database Privileges” for more information.

Rebuild Local Indexes

When a partition exchange operation is performed on a partitioned table, the local index of the exchanged partition becomes unusable, and the index must be rebuilt.  If set to true, this option rebuilds the unusable local indexes of the partition that has been exchanged.

If the partition that has been exchanged is single-level (a partition without sub-partitions), the knowledge module rebuilds the unusable indexes of the partition.

If the partition that has been exchanged is composite (a partition with sub-partitions), the knowledge module only rebuilds the unusable sub-partition indexes of the partition.

Alternatively, the user can specify the INCLUDING INDEXES clause in the Partition Exchange Options of this knowledge module to automatically include the local indexes during the partition exchange operation. This will prevent local indexes from becoming unusable.

Figure 16 shows the default value for this option.  By default, this option is set to false.

Figure 16 - Knowledge Module Option – Rebuild Local Indexes

If the local index is already in an unusable state, the index must be rebuilt with this KM option.  The UPDATE INDEXES clause does not update an index that is already in an unusable state.

This KM option rebuilds unusable local indexes sequentially, one at the time.  Table 2 shows a list of factors to consider when selecting an option to maintain local indexes:

Table 2 - Considerations When Maintaining Local Indexes

Rebuild Global Indexes

Similarly to a local index, when a partition exchange operation is performed on a partitioned table, the global index of the partitioned table becomes unusable, and the entire index must be rebuilt.  If set to true, this option rebuilds the unusable global indexes of the partitioned table.

Alternatively, the user can specify the update [global] indexes clause in the Partition Exchange Options to automatically update the global indexes during the exchange operation.  This will prevent the global indexes from becoming unusable.

If the global index is already in an unusable state, the index must be rebuilt with this KM option.  The update [global] indexes clause does not update an index that is already in an unusable state.

Figure 17 shows the default value for this option.  By default, this option is set to false.

Figure 17 - Knowledge Module Option – Rebuild Global Indexes

This KM option rebuilds unusable global indexes sequentially, one at the time.  The update [global] indexes clause of the Partition Exchange Options, however, can update global indexes in parallel.  Table 3 shows a list of factors to consider when selecting an option to maintain global indexes:

Table 3 - Considerations When Maintaining Global Indexes

Enable Constraints after Exchange

If set to true, this option enables the integrity constraints of the partitioned table after the successful execution of the partition exchange operation.

The knowledge module enables only those integrity constraints with a current status of disabled.

If an initial load for the partitioned table is performed and multiple partitions are loaded in parallel, the user can disable the integrity constraints before the first partition exchange operation and enable them after the last partition exchange operation.

If an incremental load for the partitioned table is performed, and a single partition is loaded, the user can disable and enable the integrity constraints, respectively, before and after the single exchange operation.

Figure 18 shows the default value for this option.  By default, this option is set to false.

Figure 18 - Knowledge Module Option – Enable Constraints after Exchange

Additional database privileges may be required when enabling constraints for the partitioned table.  See section “Configuring Your Database Privileges” for more information.

Gather Table Statistics

If set to true, this option instructs the Oracle Database to gather statistics on the exchanged partition of the partitioned table.

Incremental statistics will be gathered if the following two conditions are met:

• • The KM option called Set Incremental Statistics has been set to true; thus, the statistics preference for the partitioned table has been set to incremental in the database.
  • The KM option called Set Publish Statistics has been set to true; thus, the publish preference for the partitioned table has been set to true in the database.

If the above two conditions are not met, then a full table scan will be performed to maintain global statistics for the partitioned table.

Figure 19 shows the default value for this option.  By default, this option is set to false.

Figure 19 - Knowledge Module Option – Gather Table Statistics

Two additional conditions to gather incremental statistics have been already configured in the knowledge module:

• When invoking the GATHER_TABLE_STATS procedure, the ESTIMATE_PERCENT parameter is set to AUTO_SAMPLE_SIZE.
• When invoking the GATHER_TABLE_STATS procedure, the GRANULARITY parameter is set to ALL; thus, table statistics will be gathered at all three levels:  sub-partition (if composite-partition), partition, and table level.

For additional information on gathering table statistics, go to "Gathering Table Statistics on Partitioned Tables".

Delete Temporary Objects

This option allows the user to delete the temporary objects created by the knowledge module during the partition exchange operation.

The knowledge module creates only one temporary object, the ODI flow table.  If this option is set to true, the KM drops the flow table once the partition exchange operation is complete.  Users can set this option to false to keep the flow table and troubleshoot failures during the partition exchange operation.

Figure 20 shows the default value for this option.  By default, this option is set to true.

Figure 20 - Knowledge Module Option – Delete Temporary Objects

Configuring your Database Privileges for Oracle Partition Exchange

Depending how the ODI Topology is configured, additional database privileges may be required when using the IKM Oracle Partition Exchange Load.

A detailed list of privileges is shown in Table 4.  These privileges should be set on the target data server.

Table 4 - Database Privileges for the Partition Exchange Load

Adding your Table Partitions in Oracle Data Integrator

If you plan to use the default value for the KM option called Partition Name, use this section to import your database-defined partitions into your ODI Models.  Otherwise, skip this section.

Follow these steps in order to import the database-defined partitions, so the partitions can be selected from the Partition/Sub-Partition option of the ODI datastore.

There are 5 easy steps in order to import the database-defined partitions of a table into the ODI repository:

• In the ODI Studio, open the ODI Model that contains the partitioned table (datastore), and select the Reverse Engineer tab.
• Select the Customized option of the Reverse Engineer tab.
• Enter the name of the partitioned table in the Mask textbox, so only metadata of the partitioned table will be imported.
• Select the Knowledge Module called RKM Oracle.  If the knowledge module has not been imported yet into your ODI Project, follow these instructions to import the RKM Oracle: Importing Knowledge Modules in ODI.  By default, the RKM Oracle is located in the following directory:

<ODI_HOME>/sdk/xml-reference/

• Save your ODI Model changes, and select the Reverse Engineer option.  ODI will launch a script to import the database-defined partitions into the ODI model.  Verify that the script completes successfully by reviewing the logs in the ODI Operator.

Figure 21 shows an example of how to import the database-defined partitions of a table called W_ORDERS_F.

Figure 21 - Importing Database-Defined Partitions into ODI

Once the database-defined partitions have been imported into your ODI repository, you can view the partitions by opening the ODI datastore and selecting the Partitions tab.

Figure 22 shows an example of the database-defined partitions for the datastore called W_ORDERS_F.  There are 12 Composite Range-List partitions defined in the database and each partition has 3 sub-partitions.

Figure 22 - Partitions List for an ODI Datastore

Alternately, partitions and sub-partitions can be added manually into an ODI datastore by selecting the Add Partition or Add Sub-Partition option, respectively.

ODI variables can be added manually in the Partitions list as well; thus, the partition name can be assigned dynamically by refreshing the ODI variable at runtime.  Figure 22, above, shows two ODI variables that have been added into the Partitions list of the W_ORDERS_F datastore: #A_TEAM.PARTITION_NAME and #A_TEAM.SUB_PARTITION_NAME.

Once the database-defined partitions have been added into the ODI models, they can be used in ODI mappings to select data from a partition, upload data into a partition, and exchange partitions with the IKM Oracle Partition Exchange Load.

Figure 23 shows how to assign a partition name to a datastore in an ODI mapping.  In this example, the ODI variable called #A_TEAM.PARTITION_NAME has been used as the partition name for the W_ORDERS_F datastore.

To assign a partition name or an ODI variable to a datastore in an ODI mapping, follow these steps:

• • In the Logical Diagram of your ODI mapping, select the datastore and open the Properties window.  Expand the General option.
  • Locate the Partition/Sub-Partition section.
  • Open the List Box of the Partition/Sub-Partition section, and select the desired partition name or ODI variable.

Figure 23 - Setting the Partition Name in an ODI Mapping

For each datastore in your ODI mapping, you can choose different partition names or ODI variables.

Follow these recommendations when choosing a hard-coded partition name or an ODI variable to specify the partition of the datastore in your mapping:

• Choose a partition name if you plan to use your mapping to load data for a single partition.
• Choose an ODI variable if you plan to use the same mapping to upload data for more than one partition.  This will allow you to assign partition names dynamically.

Conclusion

If your Oracle data warehouse has partitioned tables, Oracle Partition Exchange offers a fast method for uploading data into your last partitioned tables.

If you would like to download a copy of this knowledge module, go to Oracle Data Integrator Code Samples and download "IKM Oracle Partition Exchange Load."  Additionally, an ODI 12.1.3 repository with examples can be found at “ODI Repository Sample for Oracle Partition Exchange.”

For an overview of the benefits of using Oracle Partition Exchange with ODI and how to use the IKM Oracle Partition Exchange Load to perform data uploads for large partitioned tables, go to “Using Oracle Partition Exchange with Oracle Data Integrator (ODI).”

For more Oracle Data Integrator best practices, tips, tricks, and guidance that the A-Team members gain from real-world experiences working with customers and partners, visit “Oracle A-team Chronicles for Oracle Data Integrator (ODI).”

Related Articles

Using Oracle Partition Exchange with Oracle Data Integrator (ODI)