This blog is part of the overall "10 Oracle Database Features That Fly Under the Radar" and explains what Hybrid Partitioned Tables are and how to use them.
Have you ever had a bunch of data that you wish you could offload from your database but still access if needed? That's where hybrid partitioned tables come in.
Before we look at hybrid partitioned tables, let's be sure to understand what partitioning is. Partitioning is a technique that breaks up a large table into smaller, more manageable parts called partitions. Each partition has its own name and can be stored separately from the others. This helps improve query performance and manageability of large tables.
What are Hybrid Partitioned Tables?
Hybrid Partitioned Tables let you keep some of your data inside the database as internal partitions, and some of your data in external files as external partitions.
Let's look at a real-world example. Imagine you're running an e-commerce system:
You need quick access to recent sales data (keep it internal)
Historical sales data from years ago is rarely accessed (move it external)
You still want to query all your data together
Setup
Connect as the system user and go to your pdb.
CONN sys/yourPassword@//localhost:1521/testpdb1 AS SYSDBA
Create the demo user
-- drop user hybrid_demo cascade;
CREATE USER hybrid_demo IDENTIFIED BY hybrid_demo QUOTA UNLIMITED ON users;
GRANT CREATE SESSION, CREATE TABLE TO hybrid_demo;
CREATE OR REPLACE DIRECTORY sales_files AS '/tmp';
GRANT READ, WRITE, EXECUTE ON DIRECTORY sales_files TO hybrid_demo;
Now connect as the user
CONN hybrid_demo/hybrid_demo@//localhost:1521/testpdb1
We can create some basic CSV files
-- Create the 2021 data file
DECLARE
v_file UTL_FILE.FILE_TYPE;
BEGIN
v_file := UTL_FILE.FOPEN('SALES_FILES', 'sales_2021.csv', 'W');
UTL_FILE.PUT_LINE(v_file, '1,2021-06-15,1500.00,1001');
UTL_FILE.PUT_LINE(v_file, '2,2021-08-22,2300.50,1002');
UTL_FILE.PUT_LINE(v_file, '3,2021-12-01,1800.75,1003');
UTL_FILE.FCLOSE(v_file);
END;
/
-- Create the 2022 data file
DECLARE
v_file UTL_FILE.FILE_TYPE;
BEGI
v_file := UTL_FILE.FOPEN('SALES_FILES', 'sales_2022.csv', 'W');
UTL_FILE.PUT_LINE(v_file, '4,2022-03-10,2100.25,1004');
UTL_FILE.PUT_LINE(v_file, '5,2022-07-15,1950.75,1005');
UTL_FILE.PUT_LINE(v_file, '6,2022-11-30,2800.00,1006');
UTL_FILE.FCLOSE(v_file);
END;
/
A Simple Example
Let's create a hybrid partitioned table for our sales data:
CREATE TABLE sales_data (
sale_id NUMBER,
sale_date DATE,
amount NUMBER(10,2),
customer_id NUMBER
)
EXTERNAL PARTITION ATTRIBUTES (
TYPE ORACLE_LOADER
DEFAULT DIRECTORY sales_files
ACCESS PARAMETERS (
FIELDS TERMINATED BY ','
(sale_id, sale_date DATE 'YYYY-MM-DD', amount, customer_id)
)
REJECT LIMIT UNLIMITED
)
PARTITION BY RANGE (sale_date) (
PARTITION sales_2021 VALUES LESS THAN (TO_DATE('2022-01-01', 'YYYY-MM-DD'))
EXTERNAL LOCATION ('sales_2021.csv'),
PARTITION sales_2022 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD'))
EXTERNAL LOCATION ('sales_2022.csv'),
PARTITION sales_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
PARTITION sales_2024 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD'))
);
What's happening here?
Recent data (2023-2024) stays in the database for quick access
Older data (2021-2022) lives in CSV files
We can still query all years together easily
We can check the users table to see this table is a hybrid partitioned table
COLUMN table_name FORMAT A11
COLUMN hybrid FORMAT A6
SELECT table_name, hybrid FROM user_tables;

Lets add some data to our internal partitions
INSERT INTO sales_data VALUES (7, TO_DATE('2023-02-15', 'YYYY-MM-DD'), 3200.50, 1007);
INSERT INTO sales_data VALUES (8, TO_DATE('2023-05-20', 'YYYY-MM-DD'), 2700.75, 1008);
INSERT INTO sales_data VALUES (9, TO_DATE('2024-01-10', 'YYYY-MM-DD'), 3500.00, 1009);
COMMIT;
Now we can query from both our external partitions and internal partitions from the same table either together or individually.
Let's query our most recent data (stored internally):
SELECT * FROM sales_data PARTITION (sales_2023)
UNION ALL
SELECT * FROM sales_data PARTITION (sales_2024);

We can also query only that data from the external partitions:
SELECT * FROM sales_data PARTITION (sales_2021)
UNION ALL
SELECT * FROM sales_data PARTITION (sales_2022);

And we can select both the internal and external partitions:
select * from sales_data;

Limitations to Keep in Mind
External partitions are read-only (no updates or deletes)
You need at least one internal partition
No unique indexes across all partitions
Some constraints (like PRIMARY KEY) can't be enforced across all partitions
Remember, hybrid partitioned tables are available in Oracle Database 19c (19.6) and later versions. They're a great way to balance performance and cost, especially when dealing with large amounts of historical data.
Additional Resources
Oracle Documentation: Hybrid Partitioned Tables
Oracle LiveLabs: Hybrid Partitioned Tables Tutorial (free hands on demo)
-Killian
Comments