top of page

Hybrid Partitioned Tables in the Oracle Database

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


-Killian

Comments


Disclaimer: The blogs and content I produce are entirely my own and do not represent an official stance of my employer, Oracle Corp. I do not speak on behalf of Oracle.

© 2023 Killiansbytes. All rights reserved.

bottom of page