Partition Iceberg Tables

Iceberg supports partitioning through partition transforms, which differ from Oracle AI Database partitioning. Instead of storing rows directly into predefined partitions, Iceberg computes partition values from one or more columns during data ingestion. Oracle supports these transforms when creating and writing to Iceberg tables. Partitioned Iceberg tables improve query performance by allowing query engines to read only the partitions required for a query.

Partition Transforms

The partitions transforms supported by Iceberg tables are listed in the following table.

Transform Name Description/Semantics Syntax Source Type Result Type
BUCKET (n) Hashes the column value into n buckets. Applies the Iceberg bucket hash function to the column value; the resulting hash value is mapped to one of the specified number of buckets; bucket partitioning distributes data evenly across partitions while avoiding excessive numbers of partitions. BUCKET(number_of_buckets, column) INT, LONG, Decimal, DATE, TIMESTAMP, TIMESTAMPTZ, STRING INT
YEAR Extract a date or timestamp year, example, as years from 1970. Extracts the calendar year from a DATE or TIMESTAMP value; rows belonging to the same year are written to the same Iceberg partition. YEAR(date_column) DATE, TIMESTAMP, TIMESTAMPTZ INT
MONTH Extract a date or timestamp month, example, as months from 1970-01-01 MONTH(date_column) DATE, TIMESTAMP, TIMESTAMPTZ INT
DAY Extract a date or timestamp day, example, as days from 1970-01-01. Extracts the day component from a DATE or TIMESTAMP value; rows sharing the same day are grouped into the same partition. DAY(date_column) DATE, TIMESTAMP, TIMESTAMPTZ INT
HOUR Extract a timestamp hour, as hours from 1970-01-01 00:00:00. Extracts the hour component from a TIMESTAMP value; this transform is commonly used for event streams and time-series data. HOUR(timestamp_column) TIMESTAMP, TIMESTAMPTZ INT

Multi-Column Partitioning

Iceberg supports multiple partition transforms within the same table. For example:

CREATE ICEBERG TABLE sales
(
    order_id      NUMBER,
    customer_id   NUMBER,
    order_date    DATE,
    amount        DOUBLE
)
WITHIN CATALOG GLUE_CAT
STORAGE LOCATION 's3://sales_bucket/'
PARTITION BY
(
    YEAR(order_date),
    BUCKET(32, customer_id)
);

In this example:

This combination balances partition pruning with even data distribution.

Create Iceberg Partitions with PARTITION BY Clause

The DDL syntax for creation of Iceberg tables with PARTITION BY clause specifies one or more partition transforms used to organize data within the Iceberg table. Unlike Oracle partitioning, Iceberg computes partition values using partition transforms rather than storing partition key values directly. If omitted, the Iceberg table is created without partitioning.

Syntax

CREATE ICEBERG TABLE [<schema>.]<table_name>
  [ ( <column_def> [, ...] ) ]                           -- optional; omit when using CTAS
  [ WITHIN CATALOG <catalog_name> ]                      -- omit to use session default catalog
  [ STORAGE LOCATION '<bucket_uri_or_prefix>']
  [ PARTITION BY ( <transform(col)> [, ...] ) ]          -- IDENTITY(col) | BUCKET(n,col) | YEAR/MONTH/DAY/HOUR(col)

Semantics

Example

PARTITION BY
(YEAR(order_date),BUCKET(32,customer_id)
);

Restrictions

The following restriction applies when creating, querying, and loading data into Partitioned Iceberg tables:

Write Partition Iceberg Tables

ITAS (Insert Table As Select) operation that bulk loads the result of a query into an Iceberg table is used to write into partitioned Iceberg tables. During the operation, Oracle generates the required Parquet data files and Iceberg metadata, uploads them to the configured object store, and commits a new snapshot to the Iceberg catalog.

Syntax

INSERT INTO schema.table@catalog_name
SELECT ...
FROM source_table;

Example

INSERT INTO sales.orders@GLUE_CAT
SELECT *
FROM local_orders;

COMMIT;

The ITAS operation performs the following high-level steps:

Update and Delete operations are also supported for partitioned Iceberg tables.
Use the UPDATE and DELETE statements to modify column values of rows or to remove all rows in a partitioned Iceberg table. Update and Delete operations can be made to specific columns of each row, or to rows satisfying a predicate.

Partition Visibility

Oracle provides a diagnostic per-table view into rows loaded in each partition. The $partitions view provides visibility into the partitions currently present in the Iceberg table and can be used for troubleshooting and validation. Use the $partitions view periodically to verify that data is evenly distributed across partitions.

Syntax

SELECT <columns> FROM <schema>.<table_name>$partitions@<catalog>;

Example

SELECT*
FROMsales.orders$partitions@GLUE_CAT;

Restrictions

The following restrictions apply specifically to partitioned Iceberg tables:

Performance Considerations

Partitioned Iceberg tables are optimized for analytical workloads. Performance depends on:

Oracle recommends using Parallel DML (PDML) when loading large partitioned tables. Additional recommendations include the following:

Drop Partition Iceberg Table with PURGE Clause

The DDL syntax that drops Iceberg tables in a catalog is used to drop metadata files and data files for partitioned Iceberg tables with the PURGE clause.

Prerequisites

Syntax

DROP ICEBERG TABLE [schema.]table_name
WITHIN CATALOG catalog_name
[PURGE];

Semantics

The PURGE option deletes the current metadata files as well as all manifest-list, manifest, and data files associated with the table.

Note - The DROP ICEBERG TABLE operation fails if concurrent DML operations are active on the target table.

Example

DROP ICEBERG TABLE sales
WITHIN CATALOG GLUE_CAT
PURGE;

Transaction Recovery

Oracle tracks in-progress Iceberg transactions using an internal catalog table named SYS.PENDING_LAKE_TRANS$. This table works as an internal recovery ledger for Iceberg transactions that records information about:

The SYS.PENDING_LAKE_TRANS$ table records pending Iceberg operations during critical transaction phases. Transactions insert rows in the table during critical operations (like commit to the catalog), and the rows are purged by the transaction on successful commit, or by a background recovery job in case of failures. The table is owned and accessible by SYS users, and located in the SYSAUX tablespace.

Note - To facilitate reads by non-SYS users, Dictionary views DBA_PENDING_LAKE_TRANS and CDB_PENDING_LAKE_TRANS are created with the same column names as stated above and granted select to role, select_catalog_role. These views provide a supported dictionary-facing visibility layer for the internal recovery state. For more information on these dictionary views, see Monitor and Diagnose.

If a transaction fails due to transaction rollback, instance failure, database crash, PDML worker failure, Oracle uses the pending transaction information to recover the transaction state.

Recovery is performed by a background recovery process rather than SMON, because Iceberg recovery may require REST calls to object storage and external catalogs that can block SMON (which is critical to DB recovery).

Orphan File Cleanup

During an interrupted ITAS operation, Parquet files and Iceberg metadata files that are already uploaded to object storage are left orphan. These orphan files consume storage space without cleanup. Oracle automatically removed orphan files in the following conditions:

This automatic cleanup keeps the object store consistent with the Iceberg catalog.