Oracle Hybrid Partitioned Tables

 🚀 Oracle Hybrid Partitioned Tables — The Power of Internal + External Data in One Table

Hybrid Partitioned Tables (HPT) in Oracle Database bring a game-changing capability:

Seamlessly combine internal table partitions with external partitions stored in Hadoop, Object Storage, Kafka, NoSQL, and more, all inside a single logical table.

This feature helps DBAs and architects build cost-optimized, high-scale, and flexible data architectures without sacrificing Oracle’s powerful partitioning features.

🔹 What Are Hybrid Partitioned Tables?

Hybrid partitioned tables merge:

• Internal partitions → stored traditionally inside Oracle tablespaces

• External partitions → stored outside the DB (HDFS, Object Storage, Parquet, ORC, CSV, HBase, Kafka, Oracle NoSQL, AWS S3, Azure, OCI, etc.)

This hybrid model is ideal when you want:

•Hot data inside Oracle (fast access, indexed, DML-capable)

•Cold/archived data in cheaper external storage

•A unified SQL interface over both

🔹 Access Drivers Supported

Hybrid partitions work with all external table types using drivers like:

• ORACLE_LOADER

• ORACLE_DATAPUMP

• ORACLE_HDFS

• ORACLE_HIVE

• ORACLE_BIGDATA

Users must have:

READ on data directories

WRITE on log/bad directories

EXECUTE on pre-processor directories.

🔹 Constraints & Query Rewrite

Because Oracle cannot enforce integrity on external data:

Primary/foreign/unique key constraints cannot be enforced

Only RELY DISABLE constraints are allowed

Enable optimizations by setting:

ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED;

or

STALE_TOLERATED;

🔹 Performance Optimizations Available

Oracle still applies partition-based optimizations across internal + external partitions:

• ✔ Static Partition Pruning

• ✔ Dynamic Partition Pruning

• ✔ Bloom Pruning

This ensures queries only scan relevant partitions—even if some reside in object storage.

🔹 What You Can Do with HPT

Hybrid tables support many powerful operations:

Create RANGE and LIST hybrid partitions

ALTER TABLE operations: ADD / DROP / RENAME partitions

Change external data source locations

Convert internal tables → hybrid tables

Exchange partitions between:

• internal ↔ internal

• external ↔ external

• internal ↔ external

Create:

• Global partial non-unique indexes

• Materialized views (with restrictions)

• Full partition-wise refresh on external partitions

🔹 Restrictions You Must Know

Some limitations apply:

• No unique or global unique indexes

• No clustering clause

• DML allowed only on internal partitions (external = read-only)

• No LOB, LONG, ADT types

• No column defaults, no invisible columns

• No in-memory for external partitions

• No SPLIT, MERGE, MOVE on external partitions

• Only RELY constraints allowed

🎯 Why Hybrid Partitioned Tables Matter

HPT enables Oracle customers to build:

• Tiered storage architectures

• Cost-effective archiving

• Unified access to data lakes and databases

• Elastic, cloud-integrated data platforms

All while maintaining Oracle SQL performance features where they matter.