Oracle 19c introduces a powerful new feature called Hybrid Partitioning, which allows database administrators to create tables that combine both internal (data stored within the database) and external (data residing outside the database, typically in operating system files) partitions. This flexibility enables optimized data management, allowing frequently accessed or highly transactional data to remain internal while less frequently accessed or archival data can be stored externally, often reducing storage costs and improving certain query performances.

Understanding the Core Concept

In a traditional database setup, all table data is stored internally. With external tables, only the metadata and definition are stored in the database, while the actual data files are managed at the operating system level. Hybrid Partitioning bridges these two approaches, offering a unified view of data that is physically distributed.

Practical Implementation Example

Let’s illustrate Hybrid Partitioning with a mytbl table designed to store national ID, first name, last name, and organization ID (org_id).

1. Initial Table Creation with Internal Partitions:
First, we create mytbl partitioned by org_id, with p1 and p2 as internal partitions for org_id values 1 and 2, respectively.

CREATE TABLE mytbl(
    national_id NUMBER,
    name VARCHAR2(20),
    last_name VARCHAR2(20),
    org_id NUMBER
)
PARTITION BY LIST (org_id)
(
    PARTITION p1 VALUES (1),
    PARTITION p2 VALUES (2)
);

We then insert data for these internal partitions:

INSERT INTO mytbl VALUES(100, 'ali', 'rezai', 1);
INSERT INTO mytbl VALUES(101, 'hadi', 'alavi', 1);
INSERT INTO mytbl VALUES(102, 'reza', 'karimi', 2);
INSERT INTO mytbl VALUES(103, 'hossein', 'akbari', 2);
COMMIT;

2. Setting Up External Data:
For org_id values 3 and 4, we decide to store data externally. This data is kept in operating system files, part3.txt and part4.txt.

/part3/part3.txt:

104,javad,akbarian,3
105,mina,karimi,3
106,sima,kabiri,3
107,nima,kasiri,3

/part4/part4.txt:

108,kimya,hasani,4
109,kobra,armani,4
110,kazem,kalvandi,4
111,usef,kalvani,4

To enable the database to access these external files, we create corresponding directory objects within Oracle:

CREATE DIRECTORY part3dir AS '/part3';
CREATE DIRECTORY part4dir AS '/part4';

3. Enabling and Adding External Partitions:
Now, we enable the EXTERNAL PARTITION ATTRIBUTES for mytbl and define how Oracle should read the external data (e.g., fields terminated by commas). We specify part3dir as the DEFAULT DIRECTORY.

ALTER TABLE mytbl
ADD EXTERNAL PARTITION ATTRIBUTES
(
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY part3dir
    ACCESS PARAMETERS
    (
        FIELDS TERMINATED BY ',' (national_id, name, last_name, org_id)
    )
);

Finally, we add the external partitions p3 and p4, linking them to their respective files and directories:

ALTER TABLE mytbl ADD PARTITION p3 VALUES (3) EXTERNAL LOCATION (part3dir:'part3.txt');
ALTER TABLE mytbl ADD PARTITION p4 VALUES (4) EXTERNAL LOCATION (part4dir:'part4.txt');

After these steps, mytbl conceptually includes all data, regardless of its physical storage location.

Accessing Hybrid-Partitioned Data

You can query the mytbl table as usual, and Oracle will seamlessly retrieve data from both internal and external partitions. For example:

SELECT * FROM mytbl WHERE org_id = 3;

The execution plan for queries targeting internal partitions will show a TABLE ACCESS PARTITION RANGE (or similar for internal access), while queries targeting external partitions will indicate an EXTERNAL TABLE ACCESS. This distinction highlights how Oracle intelligently processes queries across different storage types.

Key Features and Limitations of Hybrid Partitioning

While highly beneficial, Hybrid Partitioning comes with specific characteristics and restrictions:

1. Index Support: Only partial indexes are supported for hybrid-partitioned tables. Global or local indexes without the INDEXING PARTIAL clause are not permitted.
   sql
CREATE INDEX ind1 ON mytbl(name) INDEXING PARTIAL;
CREATE INDEX ind1 ON mytbl(name) LOCAL INDEXING PARTIAL;
2. DML Operations: Data Manipulation Language (DML) operations (INSERT, UPDATE, DELETE) are only allowed on internal partitions. Attempting DML on an external partition will result in an error, as external data is typically considered read-only from the database’s perspective.
3. Unsupported Data Types: Data types like LOB (Large Object) and LONG cannot be added to hybrid-partitioned tables.
4. Identification Query: You can identify hybrid-partitioned tables in your schema by querying the USER_TABLES view:
   sql
SELECT TABLE_NAME, HYBRID FROM user_tables WHERE HYBRID = 'YES';

Conclusion

Hybrid Partitioning in Oracle 19c offers a robust solution for managing diverse data storage needs within a single logical table. By enabling a mix of internal and external partitions, organizations can optimize data placement for performance, cost-efficiency, and compliance, making it an invaluable tool for modern database architectures.