Skip to content

GatorSet Partitioning Guide

Purpose

Partitioning allows GatorSet to read large database tables in parallel by splitting a JDBC read into multiple concurrent queries. This can significantly improve throughput for large datasets, provided that the database can handle the resulting parallel connections and the selected partitioning strategy distributes rows evenly.

This document covers:

  • how Spark JDBC partitioning works
  • manual partitioning for supported relational databases
  • Oracle auto-partitioning on the source and target sides
  • Oracle ORA_HASH partitioning for composite, non-numeric or unevenly distributed keys
  • configuration, sizing guidance, limitations and troubleshooting.

Table of Contents

  1. How Partitioning Works
  2. Partitioning Modes
  3. Manual Partitioning
  4. Oracle Auto-Partitioning
  5. Oracle ORA_HASH Partitioning
  6. Source Auto-Partitioning Flow
  7. Target Auto-Partitioning Flow
  8. Configuration Reference
  9. Worked Examples
  10. Sizing Guidelines
  11. Requirements and Limitations
  12. Troubleshooting
  13. Implementation Reference

How Partitioning Works

GatorSet uses Apache Spark's JDBC parallel-read mechanism. When partitioning is enabled, Spark divides a numeric range into numPartitions slices and issues one JDBC query per slice. These queries run concurrently across Spark executors.

For example, with:

partitionColumn = ID
lowerBound      = 0
upperBound      = 1000
numPartitions   = 10

Spark creates ten partition predicates similar to:

WHERE ID >= 0   AND ID < 100
WHERE ID >= 100 AND ID < 200
...
WHERE ID >= 900 AND ID <= 1000

Each partition normally results in a separate JDBC connection. Increasing numPartitions can improve throughput, but it also increases load on the database.

Important: lowerBound and upperBound are used to calculate partition strides. They are not filtering criteria. Spark may still read values outside the configured bounds.

Partitioning Modes

GatorSet supports two main modes.

1. Manual partitioning

Manual partitioning is explicitly configured for a source. It is appropriate when:

  • a specific partition column or expression should be used
  • the table has no suitable automatically detected key
  • exact bounds and concurrency need to be controlled
  • an Oracle MOD(ORA_HASH(...), N) expression is required to reduce skew.

Manual partitioning takes precedence over auto-partitioning.

2. Oracle auto-partitioning

When manual partitioning options are absent, GatorSet can automatically partition eligible Oracle reads. It discovers a suitable key, determines a numeric partition expression, calculates its bounds and enriches the Spark JDBC options before the read begins.

The strategy is:

Candidate key Auto-partitioning strategy
Single numeric key column Partition directly on the numeric column
Composite key Build a deterministic ORA_HASH(...) expression
Single non-numeric key column Build a deterministic ORA_HASH(...) expression
Key involving supported LOB values Hash a stable derived representation

Auto-partitioning is currently Oracle-specific. Other databases can still use manual partitioning.

Manual Partitioning

Core parameters

The following four parameters must be supplied together:

UI Label Manifest Field Type Description
SourcePartitionColumn partitionColumn String Numeric column or supported Oracle expression used to divide the read
SourceLowerBound lowerBound String Lower end of the partition range
SourceUpperBound upperBound String Upper end of the partition range
SourceNumPartitions numPartitions Integer Number of Spark partitions and approximate number of concurrent JDBC reads

SourceFetchSize maps to fetchSize. It is independent and may be configured without the other partitioning parameters.

All-or-nothing validation rule

partitionColumn, lowerBound, upperBound, and numPartitions must either all be provided or all be omitted. Supplying only a subset causes Spark validation to fail with an error similar to:

All or none of the following options must be specified:
'partitionColumn', 'lowerBound', 'upperBound', 'numPartitions'

Manual numeric-column partitioning

For a numeric column, use the actual or expected range of values:

partitionColumn = ID
lowerBound      = 1
upperBound      = 5000000
numPartitions   = 40
fetchSize       = 10000

This is most effective when the values are reasonably well distributed. Large gaps are acceptable, but severe clustering can create skewed partitions.

Manual Oracle hash partitioning

For Oracle tables with a non-numeric identifier or an uneven numeric distribution, use:

MOD(ORA_HASH(<expression>), N)

Configure:

partitionColumn = MOD(ORA_HASH(<expression>), N)
lowerBound      = 0
upperBound      = N
numPartitions   = <required parallelism>

MOD(..., N) produces values from 0 to N - 1. Setting upperBound = N provides a convenient range for Spark to split.

Oracle Auto-Partitioning

Oracle auto-partitioning reduces the need for manual source configuration. It is designed to choose an appropriate key and provide Spark with a numeric partition column even when the table does not have a single numeric primary key.

Key selection order

For source reads, GatorSet inspects table metadata and:

  1. prefers primary-key columns
  2. falls back to unique-key columns when no primary key is available
  3. collects all columns in the selected key when the key is composite.

For target-side readbacks, GatorSet uses the target primary key as the basis for partitioning when available.

Direct versus hash-based partitioning

GatorSet uses a two-tier strategy:

  • Direct partitioning: a single numeric key column can be used directly.
  • Hash-based partitioning: composite keys and non-numeric keys are converted into a numeric Oracle ORA_HASH(...) expression.

Bound calculation

Before the main read, GatorSet runs a transient bounds query against the relevant table to retrieve:

PartitionLowerBound
PartitionUpperBound
PartitionColumnName

The first two values are the actual minimum and maximum values of the selected numeric column or generated hash expression. Using actual bounds keeps Spark's partition ranges as tight as possible.

Transient EXATE_INDEX_COLUMN

Spark requires partitionColumn to refer to a column in the result set. GatorSet therefore injects the selected direct or hash-based expression into a subquery using the alias EXATE_INDEX_COLUMN:

(
  SELECT
    t.*,
    <PARTITION_EXPRESSION> AS EXATE_INDEX_COLUMN
  FROM SCHEMA.TABLE_NAME t
) TMP

Spark partitions on EXATE_INDEX_COLUMN. The underlying table schema is not modified and no permanent index column is created.

Default parallelism

Oracle auto-partitioning uses:

numPartitions = 24

unless overridden by the relevant configuration. The default JDBC fetch size is:

fetchSize = 10000

Oracle ORA_HASH Partitioning

Why ORA_HASH is needed

Spark's JDBC reader needs a numeric partition column. Many real-world tables instead use:

  • composite keys
  • string identifiers or UUID-like values
  • DATE or TIMESTAMP keys
  • RAW values
  • LOB values
  • numeric identifiers with a highly uneven distribution.

Oracle's built-in ORA_HASH function turns a supported expression into a numeric value without changing the physical table.

Key properties

ORA_HASH is useful because it is:

  • deterministic for a given input expression
  • numeric, regardless of the original key representation
  • stateless, requiring no new physical column or index
  • typically well distributed when applied to a high-cardinality key.

A basic expression is:

ORA_HASH("COL1" || '|' || "COL2")

For manual bucket sizing, wrap the hash in MOD:

MOD(ORA_HASH("ENTITY_ID"), 2300)

Composite-key handling

For composite keys, GatorSet concatenates stable representations of each key component and separates them with a delimiter before hashing:

ORA_HASH(
  NVL(TO_CHAR("COL1"), 'NULL') || '|' ||
  NVL(TO_CHAR("COL2"), 'NULL')
)

Explicit NVL(..., 'NULL') markers ensure that missing key components have a stable representation in the hash input.

Data-type-specific formatting

To make hashes consistent across Oracle settings and supported data types, GatorSet formats values before hashing.

Dates

NVL(TO_CHAR("MY_DATE", 'YYYY-MM-DD HH24:MI:SS'), 'NULL')

Timestamps

NVL(TO_CHAR("MY_TIMESTAMP", 'YYYY-MM-DD HH24:MI:SS.FF6'), 'NULL')

Numeric values

For NUMBER, DECIMAL, and FLOAT, GatorSet uses the TM9 format with an explicit decimal separator:

NVL(
  TO_CHAR("MY_NUMBER", 'TM9', 'NLS_NUMERIC_CHARACTERS=''.,'''),
  'NULL'
)

CLOB values

Oracle cannot hash a CLOB directly, so GatorSet hashes the first 4000 characters:

NVL(DBMS_LOB.SUBSTR("MY_CLOB", 4000, 1), 'NULL')

BLOB values

For a BLOB, GatorSet hashes the LOB length:

NVL(TO_CHAR(DBMS_LOB.GETLENGTH("MY_BLOB")), 'NULL')

Manual ORA_HASH expressions

GatorSet also supports manually configured Oracle expressions such as:

MOD(ORA_HASH(ENTITY_ID), 2300)

When partitionColumn contains ORA_HASH, GatorSet injects the expression into the selected columns as EXATE_INDEX_COLUMN and configures Spark to partition on that alias rather than passing the raw expression as the JDBC partitionColumn.

Execution characteristics

A generated partitioned read is conceptually similar to:

SELECT *
FROM (
  SELECT
    t.*,
    ORA_HASH(<FORMATTED_KEY_EXPRESSION>) AS EXATE_INDEX_COLUMN
  FROM SCHEMA.TABLE_NAME t
)
WHERE EXATE_INDEX_COLUMN >= <LOWER_BOUND>
  AND EXATE_INDEX_COLUMN < <UPPER_BOUND>

Spark generates one range query per partition and Oracle executes these reads concurrently.

Hash calculation adds Oracle CPU overhead. Because the hash expression is transient and not indexed, Oracle may perform full table scans. The gains from parallel I/O can still outweigh that cost for large tables, but the database load should be monitored.

Source Auto-Partitioning Flow

Source auto-partitioning is managed within LibsSources, primarily by JdbcDataFrameLoader.scala.

Trigger criteria

The source flow is activated when:

  • the source database is Oracle
  • at least one primary key or unique key is detected
  • the user has not manually configured partitionColumn, lowerBound, upperBound, or numPartitions
  • the dataset is not explicitly limited to a small size, such as a limitRows value between 0 and 100.

Processing steps

  1. Discover a key using findPartitionColumn.
  2. Prefer the primary key, falling back to a unique key.
  3. Collect all key columns when the chosen key is composite.
  4. Choose a direct or hash-based expression according to the key shape and data types.
  5. Retrieve bounds using getPartitionBounds.
  6. Inject EXATE_INDEX_COLUMN into the JDBC dbtable subquery.
  7. Enrich the JDBC options with the detected bounds and partition settings.
  8. Load in parallel using Spark JDBC.

Target Auto-Partitioning Flow

Target auto-partitioning is implemented within LibsTargets. It is used when GatorSet needs to read data back from an Oracle target during post-processing.

Typical use cases

  • validating source and target results;
  • comparing counts or schemas;
  • applying rearrangement logic;
  • handling existing target data during append or overwrite workflows.

Processing steps

  1. JdbcWriteModeHandler triggers a target bounds query.
  2. The target-side OracleService identifies a primary-key-based partition expression.
  3. Non-numeric keys are converted into a numeric ORA_HASH(...) expression.
  4. Target column names are quoted where required for Oracle compatibility.
  5. The selected expression is injected into a subquery as EXATE_INDEX_COLUMN.
  6. Spark reads the target table in parallel using the calculated bounds.

The target implementation mirrors the source-side approach so that partitioned read behaviour remains consistent.

Configuration Reference

Manual source settings

UI Label Manifest Field Type Description
SourcePartitionColumn partitionColumn String Numeric column or supported Oracle expression
SourceLowerBound lowerBound String Lower partition bound
SourceUpperBound upperBound String Upper partition bound
SourceNumPartitions numPartitions Integer Number of Spark JDBC partitions
SourceFetchSize fetchSize Integer Number of rows fetched per JDBC round trip

Spark configuration

Spark Configuration Description Default
exate.default.autoNumPartitions Number of partitions created during Oracle auto-partitioning 24
exate.default.oracleUseAllIndexes When true, inspect ALL_INDEXES instead of USER_INDEXES while identifying relevant key metadata. This is useful when the database user can access tables in other schemas but does not own them. false unless configured

Worked Examples

Example 1: Manual Oracle hashing for BO_AUDIT_HIST

Assume BO_AUDIT_HIST contains approximately 2.3 billion rows and uses a string-based ENTITY_ID.

UI Field Value
SourcePartitionColumn MOD(ORA_HASH(ENTITY_ID), 2300)
SourceLowerBound 0
SourceUpperBound 2300
SourceNumPartitions 108

MOD(..., 2300) maps rows into buckets from 0 to 2299. Spark divides the range into 108 partitions, each covering roughly 21 hash buckets. At an even distribution, each Spark partition reads approximately 21.3 million rows.

Example 2: Manual Oracle hashing for BO_AUDIT

Assume BO_AUDIT contains approximately 1.57 billion rows.

UI Field Value
SourcePartitionColumn MOD(ORA_HASH(ENTITY_ID), 1500)
SourceLowerBound 0
SourceUpperBound 1500
SourceNumPartitions 108

Each Spark partition covers roughly 13–14 hash buckets and reads approximately 14.5 million rows when distribution is even.

Example 3: Oracle auto-partitioning on a numeric primary key

Assume ORDERS has a numeric ORDER_ID primary key and no manual partitioning options.

GatorSet can:

  1. detect ORDER_ID as the preferred partition key
  2. retrieve the actual minimum and maximum values
  3. partition directly on ORDER_ID
  4. apply the default numPartitions = 24 unless overridden.

For example:

SELECT MIN("ORDER_ID"), MAX("ORDER_ID")
FROM "SCHEMA"."ORDERS"

Example 4: Oracle auto-partitioning on a composite key

Assume a table uses SOU_CODE and FILE_DATE as a composite primary key.

GatorSet can construct a stable expression similar to:

ORA_HASH(
  NVL(TO_CHAR("SOU_CODE"), 'NULL') || '|' ||
  NVL(TO_CHAR("FILE_DATE", 'YYYY-MM-DD HH24:MI:SS'), 'NULL')
)

GatorSet retrieves the actual minimum and maximum hash values, injects the expression as EXATE_INDEX_COLUMN and performs the parallel read without altering the table.

Sizing Guidelines

Choosing numPartitions

numPartitions controls parallelism and approximate database connection count.

Table size Suggested starting range
Small or medium table: under 100 million rows 8–24
Large table: 100 million to 1 billion rows 24–64
Very large table: over 1 billion rows 64–128

Treat these as starting points rather than fixed limits. The appropriate value depends on:

  • available Spark executor cores
  • database session limits
  • database CPU and I/O capacity
  • row width
  • network throughput
  • concurrent workloads.

A useful starting point is to align numPartitions with the available Spark executor cores, then increase only when the database can safely support the added concurrency.

Choosing fetchSize

fetchSize controls the number of rows retrieved per JDBC round trip.

Scenario Suggested starting value
General use 10000
Wide rows or large LOB values 1000–5000
Narrow rows where throughput is the priority 20000–50000

Choosing N for manual MOD(ORA_HASH(...), N)

For manual Oracle hashing, choose a modulus larger than numPartitions so that each Spark partition covers multiple hash buckets. This smooths out minor variations in bucket size.

A practical starting point is:

N ≈ numPartitions × 15 to numPartitions × 25
numPartitions Suggested N
8 120–200
24 360–600
48 720–1200
108 1500–2700

Avoid excessively large modulus values unless testing demonstrates a benefit.

Requirements and Limitations

Database support

Feature Oracle PostgreSQL SQL Server MySQL / MariaDB
Manual numeric-column partitioning Yes Yes Yes Yes
Manual ORA_HASH expression Yes No No No
Auto-partitioning Yes Not currently activated No No

The infrastructure for PostgreSQL automatic bound detection exists, but auto-partitioning is not currently activated.

Distribution matters

For direct numeric partitioning, the selected values should be reasonably distributed across the range. A clustered distribution can leave some Spark tasks almost empty while one task performs most of the work.

For hash-based partitioning, choose a high-cardinality key. A low-cardinality value such as a status flag can populate only a small number of hash buckets, regardless of the configured modulus.

Connection pressure

Each partition can create a concurrent database connection. Ensure that numPartitions remains within the database's available session capacity and take other running workloads into account.

Oracle CPU and scan overhead

Transient hash expressions are calculated at query time. They can increase Oracle CPU usage and may lead to full table scans. Validate performance in an environment representative of production before applying aggressive partition counts.

Manual configuration validation

For manual configuration:

  1. provide all four core partition settings together
  2. use numeric bounds that Spark can parse
  3. configure numPartitions as a positive integer
  4. use ORA_HASH expressions only against Oracle databases.

Troubleshooting

One Spark task runs much longer than the others

Likely cause: The selected numeric column is unevenly distributed.

Recommended action: For Oracle, switch to a high-cardinality MOD(ORA_HASH(...), N) expression or rely on Oracle auto-partitioning when a suitable key is detected.

Most tasks finish immediately while a few tasks do the work

Likely cause: The configured numeric bounds are much wider than the actual values, or the selected values are clustered in a narrow range.

Recommended action: Use actual MIN and MAX values for manual numeric partitioning. Consider Oracle hashing when the underlying distribution remains uneven.

Spark reports that all or none of the partition settings must be specified

Likely cause: Only some of partitionColumn, lowerBound, upperBound and numPartitions were configured.

Recommended action: Provide all four settings or remove all four settings.

Oracle reports connection-limit errors such as ORA-12516

Likely cause: numPartitions exceeds the available Oracle sessions.

Recommended action: Reduce numPartitions or coordinate an increase in Oracle capacity with the DBA.

Oracle auto-partitioning does not activate

Check whether:

  1. the database type is configured as Oracle
  2. manual partitioning parameters have been supplied
  3. a primary key or unique key is visible in the retrieved metadata
  4. the query is intentionally limited to a small row count
  5. cross-schema metadata requires exate.default.oracleUseAllIndexes = true.

Hash-based reads increase database load

Likely cause: Oracle is calculating the transient hash expression repeatedly and performing full table scans for parallel ranges.

Recommended action: Reduce numPartitions, test alternative partition strategies and monitor Oracle CPU, I/O and session usage.

Implementation Reference

Shared Oracle behaviour

Both source and target implementations use Oracle-specific service logic to generate bounds queries and partition expressions. The core method is represented by OracleService.getPartitionBoundsQuery in the relevant modules.

Source-side classes

  • com.exate.source.relationaldb.service.JdbcDataFrameLoader
  • com.exate.source.relationaldb.service.jdbc.OracleService
  • com.exate.source.relationaldb.util.QueryBuilder

Target-side classes

  • com.exate.target.relationaldb.service.JdbcWriteModeHandler
  • com.exate.target.relationaldb.service.jdbc.OracleService
  • com.exate.target.relationaldb.service.jdbc.DatabaseService
  • com.exate.target.relationaldb.service.jdbc.DatabaseServiceProvider