Hybrid Partitioned Tables - 19c New Feature

Introduction

In 12c, Oracle introduced external partitioned tables as a new feature to the traditional partitioned tables. In 19c, Oracle have now allowed you to combine both these features into a single partitioned table structure called hybrid partitioned tables. This is very useful if you wish to move older partitions onto cheaper storage.

For example, you may have one partition in a tablespace within ASM on fast storage and another partition sitting in a CSV file on a networked storage device.

Supported External Tables

Hybrid partitioned tables support the following external tables

  • ORACLE_DATAPUMP
  • ORACLE_LOADER
  • ORACLE_HDFS
  • ORACLE_HIVE

Privileges required

When using ORACLE_LOADER and ORACLE_DATAPUMP you need to grant the correct privileges, Read privileges on directories containing the data files, write privileges on directories containing the log files or bad data files and execute privileges on any directories containing the pre-processor executable.

Constraints

Enforcement of constraints is not supported on data stored in external partitions because the constraints apply to the entire table. For example, primary or foreign key constraints cannot be enforced on a hybrid partitioned table. Only constraints in the RELY DISABLE mode, such as NOT NULL, primary key, unique, and foreign-primary key are supported on hybrid partitioned tables. To activate optimizations based on these constraints, set the session parameter QUERY_REWRITE_INTEGRITY to TRUSTED or STALE_TOLERATED.

Optimizer Features

The following query optimizations are valid on Hybrid tables

Static partition pruning
Dynamic partition pruning
Bloom pruning

Support Features

Range and List partitioning methods
Alter table
The ability to modify the external data sources
Altering a traditional partition to and external partition
Creating global partial non-unique indexes on internal partitions only
Creating materialized views on internal partitions only
Creating materialized views that include external partitions when QUERY_REWRITE_INTEGRITY stale tolerated mode only is set
DML triggers on internal partitions only
ANALYZE TABLE … VALIDATE STRUCTURE on internal partitions only.
An external partition can be exchanged with an external non-partitioned table.

Limitations

Any restrictions to external tables are valid for hybrid partitioned tables
Reference and system partitioning methods are not supported
Only a single list or range partition is supported
No unique indexes or global unique indexes.
Clustering is not allowed.
DML operations on traditional partitions only as external partitions are read-only
In-memory only affects tarditional partitions.
Column defaults are not supported
Invisible columns are not supported.
The following partiton commands split, merge and move are not supported external partitions.
Lob, Long and ADT data types are not supported
Constraints are not supported


Published 2nd May 2023

Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License