Optimizing Data Storage With Hybrid Partitioned Tables in Oracle 19c

Effective management of large datasets is paramount for both performance and cost optimization. Oracle 19c introduces Hybrid Partitioned Tables (HPT), a feature that allows you to distribute table partitions across multiple storage tiers — from high-performance on-premises systems to cost-effective cloud-based object storage. This approach enables organizations to store frequently accessed “hot” data locally for faster processing while archiving less-active “cold” data in the cloud.

Learn how to implement Hybrid Partitioned Tables (HPT) to streamline data management, boost query performance, and cut storage costs.

What Are Hybrid Partitioned Tables?

Hybrid Partitioned Tables enable you to:

• Store hot data (frequently accessed) in local database storage.
• Store cold data (infrequently accessed) in external object storage, such as Oracle Cloud Infrastructure (OCI) Object Storage.
• Query both internal and external partitions seamlessly without application changes.

This approach is ideal for organizations looking to optimize storage costs while maintaining high performance for critical data.

Step-by-Step Guide to Implementing Hybrid Partitioned Tables

Step 1: Prerequisites

• Oracle Database 19c installed.
• Access to Oracle Cloud Infrastructure (OCI) Object Storage (or any compatible external storage).
• A bucket was created in OCI Object Storage to store external partitions.

Step 2: Create a Hybrid Partitioned Table

Let’s create a hybrid partitioned table to store sales data. Recent sales data will be stored locally, while older data will be stored in OCI Object Storage.

-- Create a Hybrid Partitioned Table
CREATE TABLE sales (
    sale_id   NUMBER,
    sale_date DATE,
    amount    NUMBER
)
PARTITION BY RANGE (sale_date) (
    PARTITION p2023_q1 VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD')),
    PARTITION p2023_q2 VALUES LESS THAN (TO_DATE('2023-07-01', 'YYYY-MM-DD')),
    PARTITION p2023_q3 VALUES LESS THAN (TO_DATE('2023-10-01', 'YYYY-MM-DD')),
    PARTITION p2023_q4 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
)
HYBRID (
    PARTITION p2023_q1 EXTERNAL LOCATION (
        'https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace/b/bucket/o/p2023_q1.csv'
    ),
    PARTITION p2023_q2 EXTERNAL LOCATION (
        'https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace/b/bucket/o/p2023_q2.csv'
    )
);

Step 3: Load Data into Partitions

• Load recent data into internal partitions:

  SQL

   

  INSERT INTO sales VALUES (1, TO_DATE('2023-01-15', 'YYYY-MM-DD'), 1000);
  INSERT INTO sales VALUES (2, TO_DATE('2023-06-20', 'YYYY-MM-DD'), 1500);

• Load older data into external partitions (upload CSV files to OCI Object Storage):

  SQL

   

  -- Example CSV content for p2023_q1.csv:
  -- 3,2023-02-10,2000
  -- 4,2023-03-25,2500

Step 4: Query the Hybrid Partitioned Table

Query the table to retrieve data from both internal and external partitions:

SELECT * 
FROM sales
WHERE sale_date BETWEEN TO_DATE('2023-01-01', 'YYYY-MM-DD') 
                    AND TO_DATE('2023-12-31', 'YYYY-MM-DD');

Adding and Dropping Partitions

Adding a Partition

Use the statement to add a new partition to the hybrid partitioned table. For example, to add a partition for Q1 2024:

ALTER TABLE sales 
ADD PARTITION p2024_q1 
VALUES LESS THAN (TO_DATE('2024-04-01', 'YYYY-MM-DD'));

Dropping a Partition

To drop an existing partition, use the ALTER TABLE ... DROP PARTITION statement. For example, to drop the Q1 2023 partition:

ALTER TABLE sales 
DROP PARTITION p2023_q1;

Adding an External Partition

To add an external partition, specify the external location:

ALTER TABLE sales 
ADD PARTITION p2023_q1 
VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD')) 
EXTERNAL 
DEFAULT DIRECTORY tmp_dir 
LOCATION ('p2023_q1.csv');

Dropping an External Partition

Dropping an external partition is similar to dropping an internal partition:

ALTER TABLE sales 
DROP PARTITION p2023_q1;

Simulation Scenario: Performance and Cost Benefits

Scenario Setup

• Table Size: 1 million rows (500,000 in internal storage, 500,000 in external storage).
• Query Pattern:
  • 80% of queries access recent data (internal partitions).
  • 20% of queries access historical data (external partitions).

Performance Metrics

Cost Savings

• Storing 500,000 rows in external storage reduces monthly storage costs by 80% compared to on-premises storage.

Restrictions on Hybrid Partitioned Tables

While Hybrid Partitioned Tables offer significant flexibility, there are some restrictions to be aware of:

1. Partitioning Types:
   1. Only RANGE and LIST partitioning are supported.
   2. REFERENCE and SYSTEM partitioning are not supported.
2. DML Operations:
   1. DML operations (INSERT, UPDATE, DELETE) are allowed only on internal partitions.
   2. Attempting to modify external partitions results in an error.

INSERT INTO sales 
VALUES ('GBR', 9999, 'X', 'X');

-- ERROR: ORA-14466: Data in a read-only partition or subpartition cannot be modified.

1. External Partition Limitations:
   1. External partitions are read-only.
   2. Data in external partitions must be stored in flat files (e.g., CSV) in object storage.
2. Other Restrictions:
   1. Certain operations, such as splitting or merging partitions, may have limitations depending on the partitioning type and storage tier.

Best Practices for Hybrid Partitioned Tables

1. Data Lifecycle Management:
   1. Use Oracle’s Automatic Data Optimization (ADO) to automate the movement of data between storage tiers based on access patterns.
2. Partition Design:
   1. Choose a partitioning strategy (e.g., range, list) that aligns with your data access patterns.
3. Monitoring:
   1. Regularly monitor query performance and storage costs using Oracle’s performance views (e.g., DBA_HYBRID_PART_TABLES).

Conclusion

Hybrid Partitioned Tables in Oracle 19c provide a flexible, cost-effective solution for managing large datasets. Organizations can achieve significant cost savings without compromising performance by storing hot data locally and cold data in external storage. Follow the steps in this article to implement HPT and optimize your data storage strategy.