GatorSet Partitioning Guide

Purpose

Partitioning allows GatorSet to read large database tables in parallel. Instead of one Spark task reading the whole table through one JDBC connection, Spark can split the source read into multiple ranges and run those ranges concurrently.

This guide explains how Spark JDBC partitioning and Oracle auto-partitioning work in GatorSet. It offers practical advice about when and how to use manual table configs and different Spark options, and how to prepare tables before running a job.

The auto-partitioning planner described here is Oracle-specific. Non-Oracle JDBC sources can still use manual partitioning where the database has a suitable direct range column.

Quick Start

For most Oracle source tables:

Do not add a table config unless you already know the best partitioning strategy.

Make sure Oracle table and column statistics are current. For large Oracle tables, gather stats before running a job:

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname    => 'SOURCE_SCHEMA',
    tabname    => 'TABLE_NAME',
    cascade    => TRUE,
    method_opt => 'FOR ALL COLUMNS SIZE AUTO'
  );
END;
/

Run the GatorSet job.
If performance is worse than expected, check the job logs (stderr) for [OraclePartitionPlanner] to see which path was selected.
Update configs if the selected path causes skew, excessive database load or insufficient throughput.

For non-Oracle databases, use manual partitioning if you have a suitable direct range column and know the bounds.

How Spark JDBC Partitioning Works

Spark JDBC partitioning splits a read into multiple ranges over a configured partition column. Each range becomes a Spark task and usually opens its own JDBC connection.

For example:

partitionColumn = ID
lowerBound      = 1
upperBound      = 1001
numPartitions   = 4

Spark creates ranges similar to:

WHERE ID >= 1   AND ID < 251
WHERE ID >= 251 AND ID < 501
WHERE ID >= 501 AND ID < 751
WHERE ID >= 751 AND ID <= 1001

The goal is to have each task read a similar amount of data.

Important points:

numPartitions controls Spark task parallelism and approximate database connection concurrency.
fetchSize controls how many rows the JDBC driver fetches per round trip.
lowerBound and upperBound are used by Spark to calculate range strides. They are not a strict filter guaranteeing that values outside the bounds are excluded.
Partitioning helps only when the database can execute the concurrent reads efficiently.

Manual Partitioning

Add a table config when:

you already know the best partition column
you know safe lower and upper bounds
you need exact control over database concurrency
a non-Oracle database needs parallel JDBC reads.

Required Manual Settings

The following four settings must be supplied together:

UI Key	Manifest Field	Description	Example
`SourcePartitionColumn`	`partitionColumn`	Column (or column-derived expression) used to split the read	ORDER_ID
`SourceLowerBound`	`lowerBound`	Lower end of the range	1 (MIN_VALUE)
`SourceUpperBound`	`upperBound`	Upper end of the range	802647 (MAX_VALUE)
`SourceNumPartitions`	`numPartitions`	Number of Spark JDBC partitions	24

The following settings are optional but useful for fine-tuning:

UI Key	Manifest Field	Description	Default
`SourceFetchSize`	`fetchSize`	Number of rows retrieved from the source database per JDBC round trip	100000
`TargetBatchSize`	`batchSize`	Number of rows written to the target database per JDBC batch	100000

Choosing a Source Partition Column

The SourcePartitionColumn should ideally be a direct, range-friendly numeric, date or timestamp column with few null values and reasonably even distribution.

Hash expressions like ORA_HASH(TO_CHAR("COLUMN_NAME")) are useful fallbacks, but they are usually better left to Oracle auto-partitioning. Oracle has to compute a hash expression for every row at read time, and expression predicates are usually less index-friendly than predicates on a real column.

Avoid manually configuring hash expressions unless there is a specific tested reason to override the planner.

Choosing Manual Bounds

For a direct range-friendly column, use the actual minimum value as SourceLowerBound and the actual maximum value as SourceUpperBound unless there is a specific reason to narrow the read. This gives Spark realistic bounds for calculating partition ranges and is the simplest starting point for most table configs.

Run the bounds query against the same source rows the job will read. If the job applies a source filter, include the same WHERE clause here.

Numeric column:

SELECT MIN(ID) AS SourceLowerBound,
       MAX(ID) AS SourceUpperBound,
       COUNT(*) AS row_count,
       COUNT(ID) AS non_null_count,
       COUNT(DISTINCT ID) AS distinct_values
FROM SCHEMA_NAME.TABLE_NAME;

Date or timestamp column:

SELECT MIN(CREATED_AT) AS SourceLowerBound,
       MAX(CREATED_AT) AS SourceUpperBound,
       COUNT(*) AS row_count,
       COUNT(CREATED_AT) AS non_null_count,
       COUNT(DISTINCT CREATED_AT) AS distinct_values
FROM SCHEMA_NAME.TABLE_NAME;

If non_null_count is much lower than row_count, the column may be a poor manual partition choice. If MIN and MAX are far apart but most rows sit in a small part of that range, many Spark tasks may be empty or uneven; check the distribution queries below before finalizing the config.

Choosing the Number of Partitions

As a rule of thumb, 24 is a good starting value for SourceNumPartitions. Increase only when the database, Spark executors, and network can use the extra concurrency. Decrease it when Oracle session pressure, CPU pressure, or many empty Spark tasks appear.

Manual Numeric Example

SourcePartitionColumn = ID
SourceLowerBound      = 1
SourceUpperBound      = 5000000
SourceNumPartitions   = 40
SourceFetchSize       = 10000

This is suitable when ID is numeric and reasonably evenly distributed.

Manual Date or Timestamp Example

SourcePartitionColumn = CREATED_AT
SourceLowerBound      = 2026-01-01 00:00:00
SourceUpperBound      = 2026-08-01 00:00:00
SourceNumPartitions   = 24
SourceFetchSize       = 10000

This is suitable when rows are spread reasonably evenly across CREATED_AT. It is a poor choice if most rows are concentrated in a small time window.

Manual Candidate Checks

Before choosing a manual column, check basic range and distribution signals. This generic form is useful when comparing candidate columns:

SELECT COUNT(*) row_count,
       COUNT(SourcePartitionColumn) non_null_count,
       MIN(SourcePartitionColumn) min_value,
       MAX(SourcePartitionColumn) max_value,
       COUNT(DISTINCT SourcePartitionColumn) distinct_values
FROM SCHEMA_NAME.TABLE_NAME;

Then check whether the values are clustered into a small part of the range. For date or timestamp columns, a simple daily or monthly count often gives a better operational view than a single aggregate:

SELECT TRUNC(SourcePartitionColumn, 'MM') month_start,
       COUNT(*) row_count
FROM SCHEMA_NAME.TABLE_NAME
GROUP BY TRUNC(SourcePartitionColumn, 'MM')
ORDER BY month_start;

For numeric columns, compare counts across coarse buckets:

SELECT bucket_id,
       COUNT(*) row_count
FROM (
  SELECT WIDTH_BUCKET(SourcePartitionColumn, :lower_bound, :upper_bound, 24) bucket_id
  FROM SCHEMA_NAME.TABLE_NAME
  WHERE SourcePartitionColumn IS NOT NULL
)
GROUP BY bucket_id
ORDER BY bucket_id;

A good manual range column has few nulls, enough distinct values, sensible bounds and no single bucket that dominates the read.

Oracle Auto-Partitioning

When manual partitioning is not configured, GatorSet can plan Oracle source partitioning automatically.

Auto-partitioning is considered when:

the source database type is Oracle
the source object is a table
the job has not supplied all manual Spark JDBC partitioning settings
table metadata is available
the read is not intentionally limited to a very small number of rows.

Manual partitioning always takes precedence. If a complete manual configuration is present, GatorSet uses it and does not override it.

Choosing Between Manual and Auto

Use auto-partitioning first for most Oracle tables, especially when you are not sure which column is best. Auto-partitioning can use Oracle metadata that is hard to reproduce reliably in a manual table config, including physical partition names, row-count estimates, indexes, primary keys, unique keys, column statistics and histogram data.

A table may be a good auto-partitioning candidate when:

it is physically partitioned in Oracle
it has a numeric primary key or numeric unique key
it has a valid numeric index with high cardinality and low skew
current Oracle statistics are available
there is no obvious direct manual column with known good bounds.

A manual table config may be a better option when:

the source is not Oracle
the tenant already knows the right range column and bounds
the tenant wants to cap database concurrency explicitly

Planner Order

GatorSet chooses the first suitable path in this order:

Manual partitioning, if configured
Oracle physical partitions
Indexed numeric range column
Unindexed numeric range column, only below a row-count guardrail
Single Oracle PARALLEL hinted query for large tables
ORA_HASH fallback
Normal single JDBC read if no safe plan is available

The planner is conservative. It tries to avoid expensive expression scans on very large tables when Oracle metadata suggests a better option.

Oracle Physical Partitions

Oracle can physically partition one logical table into named chunks. A common example is range partitioning by date:

CREATE TABLE SALES (
  ID NUMBER NOT NULL,
  CREATED_DATE DATE NOT NULL,
  PAYLOAD VARCHAR2(100)
)
PARTITION BY RANGE (CREATED_DATE) (
  PARTITION P202601 VALUES LESS THAN (DATE '2026-02-01'),
  PARTITION P202602 VALUES LESS THAN (DATE '2026-03-01'),
  PARTITION P202603 VALUES LESS THAN (DATE '2026-04-01'),
  PARTITION P_FUTURE VALUES LESS THAN (MAXVALUE)
);

Oracle still exposes this as one table, but it knows which rows belong to each physical partition.

How GatorSet Uses Physical Partitions

If the source table has suitable physical Oracle partitions, GatorSet generates a query shaped like this:

(
  SELECT t.*, 0 AS EXATE_INDEX_COLUMN
  FROM "SOURCE_SCHEMA"."SALES" PARTITION ("P202601") t

  UNION ALL

  SELECT t.*, 1 AS EXATE_INDEX_COLUMN
  FROM "SOURCE_SCHEMA"."SALES" PARTITION ("P202602") t

  UNION ALL

  SELECT t.*, 2 AS EXATE_INDEX_COLUMN
  FROM "SOURCE_SCHEMA"."SALES" PARTITION ("P202603") t
) TMP

Spark partitions on EXATE_INDEX_COLUMN.

Conceptually:

Spark task 1 reads EXATE_INDEX_COLUMN = 0 -> Oracle partition P202601
Spark task 2 reads EXATE_INDEX_COLUMN = 1 -> Oracle partition P202602
Spark task 3 reads EXATE_INDEX_COLUMN = 2 -> Oracle partition P202603

This is useful because Oracle can read each named physical partition directly. GatorSet does not need to compute ORA_HASH across the table to create parallel work.

When This Path Is Selected

The physical partition path is selected only when:

Oracle reports the table as physically partitioned
at least two physical partitions are available
the partition count is not above exate.default.oracleAutoPartitionMaxPhysicalPartitions
partition row-count skew is acceptable
partition names can be read from Oracle metadata.

Before running the job, tenants can check whether a table is likely to be a good candidate for this path:

SELECT COUNT(*) partition_count,
       COUNT(num_rows) partitions_with_stats,
       MAX(num_rows) max_rows,
       AVG(num_rows) avg_rows,
       MAX(num_rows) / NULLIF(AVG(num_rows), 0) skew_ratio,
       MIN(last_analyzed) oldest_analyzed,
       MAX(last_analyzed) newest_analyzed
FROM all_tab_partitions
WHERE table_owner = 'SCHEMA_NAME'
  AND table_name = 'TABLE_NAME';

Useful signals:

partition_count is at least 2 and not above the configured maximum.
partitions_with_stats is close to partition_count.
skew_ratio is below the configured maximum, 4.0 by default.
oldest_analyzed and newest_analyzed show that stats are current enough to trust.

Physical partition skew is calculated as:

largest_partition_num_rows / average_partition_num_rows

Example:

P202601: 10,000,000 rows
P202602: 12,000,000 rows
P202603: 8,000,000 rows

average = 10,000,000
largest = 12,000,000
skew    = 1.2

That is a good distribution.

If the table looks like this:

P202601: 1,000,000 rows
P202602: 1,000,000 rows
P202603: 98,000,000 rows

the skew is high and one Spark task would do almost all the work. GatorSet can reject this path and use a different strategy.

Target Table Creation

When GatorSet creates an Oracle target table from Oracle source DDL metadata, it preserves Oracle partition definitions where available. This means a partitioned Oracle source table can produce a partitioned Oracle target table during table creation.

This affects target DDL creation only. It is separate from the source read partitioning plan.

Numeric Range Partitioning

If physical partitions are not used, GatorSet looks for a good numeric range column.

Good candidates include:

a single numeric primary key
a numeric unique key
a numeric indexed column with high cardinality and low null count.

Indexed numeric range partitioning is usually efficient because Oracle can use a real column and, where appropriate, a real index.

Before relying on this path, check whether the candidate column has usable stats and enough spread:

SELECT num_distinct,
       num_nulls,
       low_value,
       high_value,
       histogram,
       last_analyzed
FROM all_tab_col_statistics
WHERE owner = 'SCHEMA_NAME'
  AND table_name = 'TABLE_NAME'
  AND column_name = 'COLUMN_NAME';

Then check whether Oracle can see a valid index on that column:

SELECT i.index_name,
       i.status,
       i.index_type,
       ic.column_position
FROM all_indexes i
JOIN all_ind_columns ic
  ON i.owner = ic.index_owner
 AND i.index_name = ic.index_name
WHERE i.table_owner = 'SCHEMA_NAME'
  AND i.table_name = 'TABLE_NAME'
  AND ic.column_name = 'COLUMN_NAME'
ORDER BY ic.column_position;

Good signals are high num_distinct, low num_nulls, current last_analyzed, and a VALID non-bitmap, non-reverse index where the candidate column is useful for range predicates.

Indexed Numeric Columns

GatorSet prefers indexed numeric candidates when:

the index is present
the index status is VALID
the index is not bitmap
the index is not reverse-key
column statistics suggest enough distinct values
null count is acceptable
histogram skew is acceptable.

Example generated shape:

partitionColumn = ID
lowerBound      = <MIN(ID)>
upperBound      = <MAX(ID)>
numPartitions   = 24

Histogram Skew Calculation

A numeric column can be indexed and high-cardinality but still make a poor range partition column if many rows share one value. GatorSet uses Oracle histogram statistics, when available, to estimate that risk without scanning the whole table.

For FREQUENCY and TOP-FREQUENCY histograms, Oracle stores ENDPOINT_NUMBER as a cumulative count. GatorSet calculates the largest bucket by subtracting the previous endpoint from the current endpoint:

bucket_rows = endpoint_number - previous_endpoint_number
max_bucket_rows = largest bucket_rows value

It then compares that largest bucket with the expected row count per distinct value:

expected_rows_per_value = (table_rows - num_nulls) / num_distinct
histogram_skew = max_bucket_rows / expected_rows_per_value

Worked example:

table_rows       = 100,000
num_nulls        = 0
num_distinct     = 1,000
max_bucket_rows  = 20,000

expected_rows_per_value = 100,000 / 1,000 = 100
histogram_skew          = 20,000 / 100 = 200

That means the most common value appears about 200 times more often than it would in a uniform distribution. With the default maximum histogram skew of 20.0, GatorSet rejects that column as a range partition candidate.

If Oracle has no frequency-style histogram, GatorSet does not guess value-frequency skew from unsupported metadata. It relies on the other checks instead, such as index validity, null count, cardinality and table size.

Unindexed Numeric Columns

An unindexed numeric column may be used only below a configured row-count threshold.

Default:

exate.default.oracleAutoPartitionUnindexedMaxRows = 50000000

This guardrail exists because range predicates on an unindexed large table can still force repeated full scans.

Oracle PARALLEL Fallback

For large Oracle tables where no good physical or numeric range plan exists, GatorSet can prefer a single Oracle PARALLEL hinted query before using repeated ORA_HASH scans.

Example:

SELECT /*+ PARALLEL(t 8) */ t."COL1", t."COL2"
FROM "SOURCE_SCHEMA"."LARGE_TABLE" t

This shifts parallelism to Oracle instead of asking multiple Spark tasks to evaluate a hash expression across the table.

Defaults:

exate.default.oracleAutoPartitionParallelFallbackRows = 100000000
exate.default.oracleAutoPartitionParallelDegree       = 8

Use this with DBA input. Oracle parallel execution can be very effective, but it also consumes Oracle server resources.

ORA_HASH Fallback

ORA_HASH is the universal Oracle fallback when GatorSet needs a numeric expression but no better physical or range strategy is available.

It is commonly used for:

composite keys
string keys
RAW keys
date or timestamp keys
nullable unique-key cases
tables where no reliable numeric range column exists.

How GatorSet Builds Hash Expressions

Spark requires the partition column to exist in the query result. GatorSet injects a transient column:

(
  SELECT
    t.*,
    ORA_HASH(<formatted_key_expression>) AS EXATE_INDEX_COLUMN
  FROM "SOURCE_SCHEMA"."TABLE_NAME" t
) TMP

Spark then partitions on EXATE_INDEX_COLUMN.

No physical column is added to the Oracle table.

Hash Bounds

For automatic ORA_HASH fallback, GatorSet uses Oracle's 32-bit hash range:

lowerBound = 0
upperBound = 4294967295

This avoids a separate full-table MIN(ORA_HASH(...)) and MAX(ORA_HASH(...)) scan before the main read.

Null Handling

Hash operands are normalised with NVL(..., 'NULL').

Example:

ORA_HASH(
  NVL(TO_CHAR("COUNTRY_CODE"), 'NULL') || '|' ||
  NVL(TO_CHAR("CUSTOMER_REF"), 'NULL')
)

This gives nullable values a deterministic representation before hashing.

For a true primary key, Oracle columns are normally non-null. Null handling still matters for unique-key and fallback candidates.

Data-Type Formatting

GatorSet formats values before hashing so the input is stable.

Dates:

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

Timestamps:

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

Numbers:

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

RAW values:

NVL(RAWTOHEX("RAW_ID"), 'NULL')

CLOB values:

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

BLOB values:

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

Performance Consideration

ORA_HASH is useful, but it is not free. Oracle must evaluate the expression at read time. On very large tables, physical partitioning, indexed numeric range partitioning, or Oracle PARALLEL may be better.

Target-Side Partitioning

Most partitioning decisions are source-read decisions. GatorSet also uses Oracle partitioning logic in target-side readbacks when a workflow needs to inspect data already written to an Oracle target, for example during append, update, validation, or post-processing paths.

Target-side partitioning follows the same broad principle:

use a suitable numeric primary key directly where possible
use a deterministic ORA_HASH expression for composite or non-numeric keys
inject the expression as transient EXATE_INDEX_COLUMN
read the target table in parallel through Spark JDBC.

This does not alter the target table schema.

When GatorSet creates an Oracle target table from Oracle source DDL metadata, it can also preserve source Oracle physical partition definitions in the target DDL. This is separate from Spark read partitioning: it controls the physical shape of the created Oracle target table.

Preparing Oracle Tables

Oracle auto-partitioning relies heavily on Oracle metadata and optimizer statistics. Before running large jobs, make sure stats are current.

Gather Table Statistics

Run this after inserting, updating, or loading substantial data:

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname    => 'SOURCE_SCHEMA',
    tabname    => 'TABLE_NAME',
    cascade    => TRUE,
    method_opt => 'FOR ALL COLUMNS SIZE AUTO'
  );
END;
/

cascade => TRUE gathers index statistics as well.

Gather Histogram Statistics for Skewed Columns

If a candidate numeric column may be skewed, ask Oracle to collect a histogram:

CREATE TABLE SOURCE_SCHEMA.ACCOUNT_EVENTS (
  ID NUMBER NOT NULL,
  ACCOUNT_ID NUMBER NOT NULL,
  PAYLOAD VARCHAR2(100),
  CONSTRAINT ACCOUNT_EVENTS_PK PRIMARY KEY (ID)
);

CREATE INDEX SOURCE_SCHEMA.ACCOUNT_EVENTS_ACCOUNT_IDX
  ON SOURCE_SCHEMA.ACCOUNT_EVENTS (ACCOUNT_ID);

In this example, ACCOUNT_ID is a numeric indexed column that GatorSet might consider for range partitioning. If most rows belong to a small number of accounts, it can be badly skewed, so a histogram helps GatorSet reject it when appropriate.

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(
    ownname    => 'SOURCE_SCHEMA',
    tabname    => 'TABLE_NAME',
    cascade    => TRUE,
    method_opt => 'FOR COLUMNS SIZE 254 ACCOUNT_ID'
  );
END;
/

The number after SIZE is the maximum number of histogram buckets Oracle may use for that column.

Use:

SIZE AUTO for general stats collection where Oracle decides which columns need histograms.
SIZE 254 when you specifically want Oracle to collect a detailed histogram for a known skew-sensitive column.
a smaller value, such as SIZE 64, if you want a lighter histogram and do not need maximum detail.

Oracle may still choose the final histogram type based on the data and database version. GatorSet uses frequency-style histograms where they are reliable for skew estimation.

What Happens If Stats Are Missing?

The job can still run, but GatorSet becomes more conservative.

Possible effects:

physical partition skew may be unknown or inaccurate
indexed numeric candidates may be rejected
histogram skew may not be available
row-count thresholds may be based on stale estimates
the planner may fall back to ORA_HASH, Oracle PARALLEL, or a normal JDBC read.

Missing stats should not make the job unsafe, but they can prevent the best partitioning path from being selected.

Recommended Operational Practice

For production:

gather stats after major data loads
keep scheduled Oracle stats jobs enabled where possible
gather histograms for known skew-sensitive columns
coordinate with DBAs for very large tables and Oracle parallel execution settings.

Configuration Reference

Table Configuration

UI label	Manifest field	Default	Notes
`SourcePartitionColumn`	`partitionColumn`	none	Required for manual partitioning; prefer a direct range-friendly numeric, date, or timestamp column
`SourceLowerBound`	`lowerBound`	none	Required for manual partitioning
`SourceUpperBound`	`upperBound`	none	Required for manual partitioning
`SourceNumPartitions`	`numPartitions`	none	Required for manual partitioning; `24` is a good starting point for table configs
`SourceFetchSize`	`fetchSize`	`10000` in Oracle auto paths	Can be configured independently

Spark Configuration

Below is a complete list of exate.default.* keys that can be applied to Spark Resource Configs.

Config	Default	Meaning
`exate.default.autoNumPartitions`	`24`	Number of Spark JDBC partitions if no manifest value is provided
`exate.default.oracleUseAllIndexes`	`false`	Uses broader Oracle index metadata where configured and available
`exate.default.oracleAutoPartitionMaxPhysicalPartitions`	`256`	Maximum physical Oracle partitions eligible for the physical partition path
`exate.default.oracleAutoPartitionMaxPhysicalSkew`	`4.0`	Maximum accepted physical partition skew ratio
`exate.default.oracleAutoPartitionMaxHistogramSkew`	`20.0`	Maximum accepted histogram skew for numeric range candidates
`exate.default.oracleAutoPartitionUnindexedMaxRows`	`50000000`	Largest table row count where an unindexed numeric range column can be selected
`exate.default.oracleAutoPartitionParallelFallbackRows`	`100000000`	Row count at which Oracle `PARALLEL` fallback is preferred before `ORA_HASH`
`exate.default.oracleAutoPartitionParallelDegree`	`8`	Oracle parallel hint degree
`exate.default.oracleTargetRepartition`	`24`	Number of partitions before a JDBC target write when an Oracle source DataFrame has exactly one partition. The target manifest's `numPartitions` takes precedence; an effective value of `1` or less disables this repartitioning. DataFrames with multiple partitions are unchanged
`exate.default.oracleValidateConstraints`	`true`	Uses `ENABLE VALIDATE` when generating enabled Oracle target constraints or re-enabling them. Set to `false` to use `ENABLE NOVALIDATE`, skipping validation of existing rows
`exate.default.deduplicateAppend`	`false`	Enables a target read and left anti-join in the default JDBC append flow to skip incoming rows matching existing target rows on the combined primary-key, unique-key and foreign-key columns available in metadata. With no such columns, rows are written directly. When disabled, target constraints are enforced by the database
`exate.default.targetDDLNumPartitions`	`1`	Number of Spark JDBC partitions used to read Oracle target DDL metadata. Controls metadata-read concurrency, independently of source data reads and target data writes
`exate.default.postProcessBatchSize`	`10000`	Number of values processed between `ExateService.postProcess()` calls in pseudo-encryption processing within each executor task. A remaining partial batch is flushed when the task completes. Use a positive integer

Sizing Guidance

Choosing `numPartitions`

numPartitions controls concurrency. More partitions can improve throughput, but they also increase database sessions and load.

Table size	Suggested starting range
Under 100 million rows	`8` to `24`
100 million to 1 billion rows	`24` to `64`
More than 1 billion rows	`64` to `128`

Tune based on:

Spark executor cores
Oracle session limits
Oracle CPU and I/O capacity
row width
network throughput
other database workloads.

Choosing `fetchSize`

Scenario	Suggested starting value
General use	`10000`
Wide rows or large LOBs	`1000` to `5000`
Narrow rows with high throughput requirement	`20000` to `50000`

Very high fetch sizes can increase executor memory pressure.

Worked Examples

Example 1: Oracle Physical Partition Path

Source table:

CREATE TABLE SOURCE_SCHEMA.SALES_BY_MONTH (
  ID NUMBER NOT NULL,
  CREATED_DATE DATE NOT NULL,
  PAYLOAD VARCHAR2(100),
  CONSTRAINT SALES_BY_MONTH_PK PRIMARY KEY (ID)
)
PARTITION BY RANGE (CREATED_DATE) (
  PARTITION P202601 VALUES LESS THAN (DATE '2026-02-01'),
  PARTITION P202602 VALUES LESS THAN (DATE '2026-03-01'),
  PARTITION P202603 VALUES LESS THAN (DATE '2026-04-01'),
  PARTITION P_FUTURE VALUES LESS THAN (MAXVALUE)
);

If stats show the physical partitions are reasonably balanced, GatorSet can generate a UNION ALL query over the named partitions and Spark can read those branches in parallel.

Expected log excerpt (counts and skew depend on table statistics; see What to Look For in Logs for full examples):

[OraclePartitionPlanner] table=SOURCE_SCHEMA.SALES_BY_MONTH Selecting Path 2: Table has 4 physical partitions and this does not exceed exate.default.oracleAutoPartitionMaxPhysicalPartitions=256; ...

Example 2: Indexed Numeric Primary Key

Source table:

CREATE TABLE SOURCE_SCHEMA.ORDERS (
  ORDER_ID NUMBER NOT NULL,
  CREATED_DATE DATE,
  PAYLOAD VARCHAR2(100),
  CONSTRAINT ORDERS_PK PRIMARY KEY (ORDER_ID)
);

With no manual partitioning, GatorSet can use ORDER_ID directly:

partitionColumn = ORDER_ID
lowerBound      = MIN(ORDER_ID)
upperBound      = MAX(ORDER_ID)
numPartitions   = 24 by default

Expected log excerpt (the remaining message reports thresholds, skew and score calculations):

[OraclePartitionPlanner] table=SOURCE_SCHEMA.ORDERS Selecting Path 3: Using indexed numeric range column "ORDER_ID". ...

Example 3: Composite Key ORA_HASH Fallback

Source table:

CREATE TABLE SOURCE_SCHEMA.CUSTOMER_EVENTS (
  COUNTRY_CODE VARCHAR2(2) NOT NULL,
  CUSTOMER_REF VARCHAR2(50) NOT NULL,
  EVENT_DATE DATE NOT NULL,
  PAYLOAD VARCHAR2(100),
  CONSTRAINT CUSTOMER_EVENTS_PK PRIMARY KEY (COUNTRY_CODE, CUSTOMER_REF, EVENT_DATE)
);

GatorSet can build a stable hash expression:

ORA_HASH(
  NVL(TO_CHAR("COUNTRY_CODE"), 'NULL') || '|' ||
  NVL(TO_CHAR("CUSTOMER_REF"), 'NULL') || '|' ||
  NVL(TO_CHAR("EVENT_DATE", 'YYYY-MM-DD HH24:MI:SS'), 'NULL')
)

Example selection log (key-column order may vary):

[OraclePartitionPlanner] table=SOURCE_SCHEMA.CUSTOMER_EVENTS Selecting Path 6: Using ORA_HASH fallback over primary key columns "COUNTRY_CODE", "CUSTOMER_REF", "EVENT_DATE" because Paths 1-5 were not selected.

Example 4: Automatic ORA_HASH Fallback

Suppose BO_AUDIT_HIST has a string identifier and no suitable physical partition or numeric range path. With no manual table config, GatorSet may build a transient hash expression automatically:

(
  SELECT
    t.*,
    ORA_HASH(NVL(TO_CHAR("ENTITY_ID"), 'NULL')) AS EXATE_INDEX_COLUMN
  FROM "SOURCE_SCHEMA"."BO_AUDIT_HIST" t
) TMP

Example selection log, assuming ENTITY_ID is the primary key:

[OraclePartitionPlanner] table=SOURCE_SCHEMA.BO_AUDIT_HIST Selecting Path 6: Using ORA_HASH fallback over primary key columns "ENTITY_ID" because Paths 1-5 were not selected.

This is a fallback, not the preferred first choice for very large tables. Oracle must compute the expression while reading, so check for physical partitions, numeric range candidates, and Oracle PARALLEL suitability first.

Example 5: Large-Table Oracle PARALLEL Fallback

For a very large table with no good range or physical partition path, GatorSet may choose:

SELECT /*+ PARALLEL(t 8) */ t.*
FROM "SOURCE_SCHEMA"."LARGE_TABLE" t

Example selection log for a table with an estimated 500 million rows:

[OraclePartitionPlanner] table=SOURCE_SCHEMA.LARGE_TABLE Selecting Path 5: Using a single Oracle PARALLEL hinted query because tableRows=500000000 >= exate.default.oracleAutoPartitionParallelFallbackRows=100000000, exate.default.oracleAutoPartitionParallelDegree=8 > 1, and Paths 1-4 were not selected.

This is useful when repeated ORA_HASH range scans would be more expensive than one Oracle-managed parallel scan.

Troubleshooting

Auto-partitioning did not activate

Check:

the source database type is Oracle
the source object is a table
the job did not supply manual partition settings
metadata for primary keys, unique keys, indexes and partitions is visible to the source user
the read is not deliberately limited to a small number of rows
Oracle stats are current.

Physical partitions were not used

Possible causes:

the table is not physically partitioned
there is only one physical partition
there are more partitions than exate.default.oracleAutoPartitionMaxPhysicalPartitions
partition stats show too much skew
partition row counts are stale or missing
partition names are not visible through Oracle metadata.

Action:

gather table stats with cascade => TRUE
check ALL_TAB_PARTITIONS
review the [OraclePartitionPlanner] log message for the chosen path.

One Spark task runs much longer than the others

Likely cause:

range column values are skewed
physical partitions are uneven
manual bounds create uneven ranges.

Action:

gather histogram stats for the candidate column
reduce oracleAutoPartitionMaxPhysicalSkew
use a different direct manual range column if one exists
prefer Oracle auto-partitioning when no good manual range column exists
reduce numPartitions if the database is overloaded.

Many Spark tasks finish immediately

Likely cause:

bounds are much wider than actual values
values are clustered in a small part of the range
too many partitions were configured for the data volume.

Action:

use actual MIN and MAX values for manual partitioning
lower numPartitions
prefer Oracle auto-partitioning when available.

Oracle connection errors such as `ORA-12516`

Likely cause:

numPartitions creates more concurrent JDBC sessions than Oracle can accept.

Action:

reduce numPartitions
coordinate with the DBA on session limits
avoid running many high-partition jobs at the same time.

Oracle CPU is high during ORA_HASH reads

Likely cause:

Oracle is evaluating a transient hash expression across a large table.

Action:

prefer physical partitions where available
prefer indexed numeric range columns where suitable
consider Oracle PARALLEL fallback with DBA approval
reduce numPartitions
test during a lower-load window.

Target Oracle table was not created with partitions

If an Oracle target table already exists, GatorSet does not recreate its physical DDL in append mode. Drop or overwrite the target table if you want GatorSet to recreate it from source DDL.

If the table is being created for the first time, make sure the source user can see Oracle partition metadata. GatorSet uses source DDL metadata to preserve source Oracle partition definitions during target table creation.

What to Look For in Logs

Search stderr for [OraclePartitionPlanner] and table=SCHEMA.TABLE to follow the decisions for a specific table. Auto-planned reads log the starting context, then each skipped path until a path is selected. Later paths are not evaluated after a selection. Complete manual partitioning (Path 1) and early single-read fallbacks (Path 7) are logged by the loader without a planner-start message.

The table below shows representative messages from OraclePartitionPlanner.scala and JdbcDataFrameLoader.scala. These are independent scenarios, not one execution trace; identifiers, statistics, configuration values and candidate order vary by table. Log timestamps and logger names are omitted.

Path	Description	Example Log
Planner start	Baseline context for every auto-planned table	[OraclePartitionPlanner] table=RISK_OWNER.TRADEDATA Starting Oracle auto-partitioning planner: objectType=table, visibleColumns=142, exate.default.autoNumPartitions=24, exate.default.oracleAutoPartitionMaxPhysicalPartitions=256, exate.default.oracleAutoPartitionMaxPhysicalSkew=4.00, exate.default.oracleAutoPartitionMaxHistogramSkew=20.00, exate.default.oracleAutoPartitionUnindexedMaxRows=50000000, exate.default.oracleAutoPartitionParallelFallbackRows=100000000, exate.default.oracleAutoPartitionParallelDegree=8, tableRows=500000000.
Path 1: Manual partitioning	Selected	`[OraclePartitionPlanner] table=RISK_OWNER.TRADEDATA Selecting Path 1: Manual Spark JDBC partitioning config is complete; partitionColumn=DEAL_ID, lowerBound=1, upperBound=90000000, numPartitions=48, fetchSize=10000.`
Path 1: Manual partitioning	Skipped: no partitioning keys supplied to the planner	`[OraclePartitionPlanner] table=RISK_OWNER.TRADEDATA Skipping Path 1: No manual Spark JDBC partitioning config was provided; required keys are partitionColumn, lowerBound, upperBound, numPartitions.`
Path 1: Manual partitioning	Skipped: incomplete configuration	`[OraclePartitionPlanner] table=RISK_OWNER.TRADEDATA Skipping Path 1: Manual Spark JDBC partitioning config is incomplete; present keys are lowerBound, numPartitions, partitionColumn and required keys are partitionColumn, lowerBound, upperBound, numPartitions.`
Path 2: Oracle physical partitions	Selected	`[OraclePartitionPlanner] table=RISK_OWNER.TRADEDATA Selecting Path 2: Table has 12 physical partitions and this does not exceed exate.default.oracleAutoPartitionMaxPhysicalPartitions=256; skew check 11000000 / 10000000.00 = 1.10 <= exate.default.oracleAutoPartitionMaxPhysicalSkew=4.00.`
Path 2: Oracle physical partitions	Skipped: table is not partitioned	`[OraclePartitionPlanner] table=RISK_OWNER.TRADEDATA Skipping Path 2: Oracle metadata reports TablePartitioned=NO; physicalPartitionCount=0, but at least 2 are required.`
Path 2: Oracle physical partitions	Skipped: too many partitions; names were not loaded	`[OraclePartitionPlanner] table=RISK_OWNER.TRADEDATA Skipping Path 2: physicalPartitionCount=800 exceeds exate.default.oracleAutoPartitionMaxPhysicalPartitions=256; loaded physical partition names=0; expected exactly metadata physicalPartitionCount=800.`
Path 2: Oracle physical partitions	Skipped: excessive skew	`[OraclePartitionPlanner] table=RISK_OWNER.TRADEDATA Skipping Path 2: physical partition skew 98000000 / 10000000.00 = 9.80 exceeds exate.default.oracleAutoPartitionMaxPhysicalSkew=4.00.`
Path 2: Oracle physical partitions	Skipped: planner statistics unavailable	`[OraclePartitionPlanner] table=RISK_OWNER.TRADEDATA Skipping Path 2: Oracle planner stats were not available, so table partition metadata could not be evaluated.`
Path 3: Indexed numeric range column	Selected, with threshold and score calculations	`[OraclePartitionPlanner] table=RISK_OWNER.TRADEDATA Selecting Path 3: Using indexed numeric range column "DEAL_ID". numDistinct minimum=max(exate.default.autoNumPartitions * 4, 100)=max(24 * 4, 100)=100; histogram skew 1.00 <= exate.default.oracleAutoPartitionMaxHistogramSkew=20.00; score working: keyScore=100.00 + indexScore=60.00 + nullScore=max(0, 20 * (1 - 0.00))=20.00 + distinctScore=min(40, log10(max(10, 500000000)) * 8)=40.00 - skewPenalty=max(0, 1.00 - 1) * 2=0.00 = 220.00.`
Path 3: Indexed numeric range column	Skipped: no usable indexed numeric candidate	`[OraclePartitionPlanner] table=RISK_OWNER.TRADEDATA Skipping Path 3: No eligible indexed numeric range column was found. numDistinct minimum=max(exate.default.autoNumPartitions * 4, 100)=max(24 * 4, 100)=100; exate.default.oracleAutoPartitionMaxHistogramSkew=20.00. Candidate checks: "ACCOUNT_ID": no indexName is present; "BOOK_ID": indexName=BOOK_IDX has indexStatus=UNUSABLE, but VALID is required.`
Path 3: Indexed numeric range column	Skipped: histogram skew exceeds the threshold	`[OraclePartitionPlanner] table=RISK_OWNER.TRADEDATA Skipping Path 3: No eligible indexed numeric range column was found. numDistinct minimum=max(exate.default.autoNumPartitions * 4, 100)=max(24 * 4, 100)=100; exate.default.oracleAutoPartitionMaxHistogramSkew=20.00. Candidate checks: "ACCOUNT_ID": histogram skew maxHistogramBucketRows / ((tableRows - numNulls) / numDistinct) = 500000 / (500000000 / 100000) = 100.00 exceeds exate.default.oracleAutoPartitionMaxHistogramSkew=20.00.`
Path 4: Unindexed numeric range column	Selected, with row-count guardrail and score calculations	[OraclePartitionPlanner] table=RISK_OWNER.SMALL_EVENTS Selecting Path 4: Using unindexed numeric range column "EVENT_ID". tableRows=20000000 <= exate.default.oracleAutoPartitionUnindexedMaxRows=50000000;numDistinct minimum=max(exate.default.autoNumPartitions * 4, 100)=max(24 * 4, 100)=100; histogram skew 1.00 <= exate.default.oracleAutoPartitionMaxHistogramSkew=20.00; score working: keyScore=0.00 + indexScore=10.00 + nullScore=max(0, 20 * (1 - 0.00))=20.00 + distinctScore=min(40, log10(max(10, 20000000)) * 8)=40.00 - skewPenalty=max(0, 1.00 - 1) * 2=0.00 = 70.00.
Path 4: Unindexed numeric range column	Skipped: table exceeds the row-count guardrail	`[OraclePartitionPlanner] table=RISK_OWNER.TRADEDATA Skipping Path 4: Table row count 500000000 exceeds exate.default.oracleAutoPartitionUnindexedMaxRows=50000000, so unindexed numeric range predicates are not allowed.`
Path 4: Unindexed numeric range column	Skipped: candidate has too few distinct values	`[OraclePartitionPlanner] table=RISK_OWNER.SMALL_EVENTS Skipping Path 4: No eligible unindexed numeric range column was found. numDistinct minimum=max(exate.default.autoNumPartitions * 4, 100)=max(24 * 4, 100)=100; exate.default.oracleAutoPartitionMaxHistogramSkew=20.00. Row-count guardrail check: tableRows=20000000 <= exate.default.oracleAutoPartitionUnindexedMaxRows=50000000. Candidate checks: "STATUS_CODE": numDistinct=5 is below required minimum 100.`
Path 5: Oracle PARALLEL hinted query	Selected	`[OraclePartitionPlanner] table=RISK_OWNER.TRADEDATA Selecting Path 5: Using a single Oracle PARALLEL hinted query because tableRows=500000000 >= exate.default.oracleAutoPartitionParallelFallbackRows=100000000, exate.default.oracleAutoPartitionParallelDegree=8 > 1, and Paths 1-4 were not selected.`
Path 5: Oracle PARALLEL hinted query	Skipped: table is below the fallback threshold	`[OraclePartitionPlanner] table=RISK_OWNER.SMALL_EVENTS Skipping Path 5: tableRows=20000000 is below exate.default.oracleAutoPartitionParallelFallbackRows=100000000.`
Path 5: Oracle PARALLEL hinted query	Skipped: parallel degree is disabled	`[OraclePartitionPlanner] table=RISK_OWNER.TRADEDATA Skipping Path 5: exate.default.oracleAutoPartitionParallelDegree=1, but the degree must be greater than 1.`
Path 6: ORA_HASH	Selected using key columns	`[OraclePartitionPlanner] table=RISK_OWNER.CUSTOMER_EVENTS Selecting Path 6: Using ORA_HASH fallback over primary key columns "COUNTRY_CODE", "CUSTOMER_REF", "EVENT_DATE" because Paths 1-5 were not selected.`
Path 6: ORA_HASH	Selected using a high-cardinality column	`[OraclePartitionPlanner] table=RISK_OWNER.EVENT_LOG Selecting Path 6: Using ORA_HASH fallback over high-cardinality column "EVENT_REF" with numDistinct=2500000 because Paths 1-5 were not selected.`
Path 6: ORA_HASH	Skipped: no suitable key or high-cardinality column	`[OraclePartitionPlanner] table=RISK_OWNER.AUDIT_PAYLOAD Skipping Path 6: no primary/unique key columns were found and no hashable column had numDistinct >= 100.`
Path 7: Single JDBC read	Selected after all earlier paths were rejected	`[OraclePartitionPlanner] table=RISK_OWNER.AUDIT_PAYLOAD Selecting Path 7: Normal single JDBC read because Paths 1-6 did not produce a safe plan.`
Path 7: Single JDBC read	Selected before planner startup for a row limit between 0 and 100 inclusive	`[OraclePartitionPlanner] table=RISK_OWNER.SMALL_EVENTS Selecting Path 7: Normal single JDBC read because limitRows=100 is <= 100.`
Path 7: Single JDBC read	Selected before planner startup because column metadata is unavailable	`[OraclePartitionPlanner] table=RISK_OWNER.TRADEDATA Selecting Path 7: Normal single JDBC read because table metadata columns were unavailable for RISK_OWNER.TRADEDATA.`

The Path 1 diagnostic examines the JDBC options passed to the planner. The loader normally supplies numPartitions even without a manual table config, so that case can appear as an incomplete configuration with present keys are numPartitions. The no-config message applies when none of the four required keys reaches the planner.

Skip messages can combine several reasons separated by semicolons. Missing statistics are reported as unavailable. If physical partition metadata is usable but partition row-count skew inputs are missing, Path 2 can still be selected with the note Partition row-count skew inputs were unavailable, so the planner used its default skew ratio of 1.00. For Path 6, the key-based message says unique key columns when no primary key is available.

After a planner selection, also check [LoadWithAutoPartitioning] for the resulting read configuration. A selected range plan can still fall back if its bounds are unusable.

Read outcome	Example Log
Partitioned read configured (SQL abbreviated as `<generated SQL>`)	`[LoadWithAutoPartitioning] table=RISK_OWNER.TRADEDATA, dbtable: <generated SQL>, partitionColumn: EXATE_INDEX_COLUMN, lowerBound: 1, upperBound: 90000000, numPartitions: 24, fetchsize: 10000`
Oracle PARALLEL query configured	`[LoadWithAutoPartitioning] table=RISK_OWNER.TRADEDATA Using Oracle PARALLEL(8) query without Spark JDBC partition predicates.`
Range or hash expression partitioning could not be applied	`[LoadWithAutoPartitioning] table=RISK_OWNER.SMALL_EVENTS Skipping partitioning as upperBound (1) is not greater than lowerBound (1) or partitionColumn is empty. Falling back to default load.`
No safe plan was found	`[LoadWithAutoPartitioning] table=RISK_OWNER.AUDIT_PAYLOAD No suitable Oracle auto-partitioning candidate was found. Falling back to default load.`

GatorSet Partitioning Guide

Purpose

Table of Contents

Quick Start

How Spark JDBC Partitioning Works

Manual Partitioning

Required Manual Settings

Choosing a Source Partition Column

Choosing Manual Bounds

Choosing the Number of Partitions

Manual Numeric Example

Manual Date or Timestamp Example

Manual Candidate Checks

Oracle Auto-Partitioning

Choosing Between Manual and Auto

Planner Order

Oracle Physical Partitions

How GatorSet Uses Physical Partitions

When This Path Is Selected

Target Table Creation

Numeric Range Partitioning

Indexed Numeric Columns

Histogram Skew Calculation

Unindexed Numeric Columns

Oracle PARALLEL Fallback

ORA_HASH Fallback

How GatorSet Builds Hash Expressions

Hash Bounds

Null Handling

Data-Type Formatting

Performance Consideration

Target-Side Partitioning

Preparing Oracle Tables

Gather Table Statistics

Gather Histogram Statistics for Skewed Columns

What Happens If Stats Are Missing?

Recommended Operational Practice

Configuration Reference

Table Configuration

Spark Configuration

Sizing Guidance

Choosing numPartitions

Choosing fetchSize

Worked Examples

Example 1: Oracle Physical Partition Path

Example 2: Indexed Numeric Primary Key

Example 3: Composite Key ORA_HASH Fallback

Example 4: Automatic ORA_HASH Fallback

Example 5: Large-Table Oracle PARALLEL Fallback

Troubleshooting

Auto-partitioning did not activate

Physical partitions were not used

One Spark task runs much longer than the others

Many Spark tasks finish immediately

Oracle connection errors such as ORA-12516

Oracle CPU is high during ORA_HASH reads

Target Oracle table was not created with partitions

What to Look For in Logs

Choosing `numPartitions`

Choosing `fetchSize`

Oracle connection errors such as `ORA-12516`