Oracle connector partition type

For writes to a range-partitioned, list-partitioned or interval-partitioned table, the Oracle connector partition type ensures that the distribution of input records matches the organization of the partitions in the table.

When the Oracle connector partition type is selected, the connector first gets the partitioning information for the table. In most cases, the connector uses the partitioning information from the table to which the connector writes the data; the name of this table is usually specified in the Table name property or is implicitly specified in the INSERT, UPDATE, or DELETE SQL statement. To configure the connector to use the partitioning information from one table but write the data to a different table, you specify the table name in the Table name for partitioned writes property.

After the connector determines the table name for the partitioned write, the connector determines the set of nodes on which to run. The connector determines the number of partitions that are on the table and associates one node with each partition. The number of partitions must match the number of nodes. A mismatch between the number of nodes and the number of partitions can occur in the following situations:

The configuration of the parallel processing nodes specifies a resource constraint. If the configuration specifies a constraint, the connector cannot dynamically modify the set of processing nodes. As a result, the connector reports an error and stops the operation.
The list of nodes that are configured for the stage contains more nodes than the number of partitions in the table. In this case, the connector removes the excess nodes from the end of the list.
The list of nodes that are configured for the stage contains fewer nodes than the number of partitions in the table. In this case, the connector adds nodes to the end of the list. The definition for each node that is added matches the definition of the last node in the original list.

Next, the connector determines the node to send each input record to. For each incoming record, the connector inspects the data in the fields that correspond to the table columns that constitute the partition key for the table. The connector compares those values to the boundary values that are specified for the individual partitions of the table and determines the partition that will store the records. Because the number of nodes matches the number of partitions and each partition has only one node assigned to it, the connector routes the records to the node that is associated with each partition, and the node writes the records into the database.

For the connector to determine both the number of partitions in a table and the partitioning type that was used to partition the table, the table must exist in the database before you run the job. The only exception to this rule is when the Table action property is set to Create or Replace and the Create statement property specifies a CREATE TABLE statement. In this case, the connector analyzes the CREATE TABLE statement to determine the number of partitions and the partition type that the table will have when it is created at run time. The connector uses this information to determine the number of nodes that the stage will run on.

Conditions that cause the stage to run in sequential mode, report errors, or both

If the table uses a supported partition type but the partition key in the table includes a virtual column, the connector does not force sequential execution. Instead, the connector runs on the number of nodes that is equal to the number of table partitions. However, because only one node processes the data, the connector effectively runs in sequential mode.

If the Table action property is set to Create or Replace and the Generate create statement at runtime property is set to Yes, the connector does not create the table as a partitioned table. Therefore, the connector cannot associate the table partitions with the nodes. In this case, the connector logs a warning and runs the stage in sequential mode.

If the table does not exist and the Before SQL statement property or the Before SQL (node) statement property specifies the CREATE TABLE statement, the connector reports an error. The error is reported because the connector tries to determine the number of partitions and the partition type before it runs the before SQL statement that creates the table.

When the Table scope property is set to Single partition or Single subpartition, the connector runs the stage in sequential mode and logs a warning. In this case, the connector is explicitly configured to write data to only one partition or subpartition; therefore, only one node is assigned to that partition or subpartition.