Oracle Database — Hybrid Partition Tables on Autonomous Database

Hybrid partition table is way to create internal partitioned tables with external tables and thus are called Hybrid Partition Tables. This process enables users to combine power of partitioning internal tables with data on the external storage devices to as a partition. External partitions enables you to move data in form of comma delimited files from an expensive storage device to much cheaper storage devices like HDFS and more often to Oracle Object Store which is considerably cheaper. There are many external storage type drivers for data loading off of external tables.

  1. ORACLE_DATAPUMP
  2. ORACLE_LOADER
  3. ORACLE_HDFS
  4. ORACLE_HIVE

There are some per-requisites and restrictions that needs to be followed to setup and utilize HPT tables that can be found here. In this blog we’ll explore how we can create Hybrid Partition Tables on autonomous database.

  1. Create autonomous database credentials to authenticate against OCI Object Store

2. Place the files (can be downloaded from here) in the object store and generate a link to the store

3. Create Hybrid partition table using the external location of object store as 1 file and rest of the data on the database itself. In my case, I have create 2 partitions p2 in which I am loading data into the database from external tables that I created using the file in object store (not mandatory step), so essentially local storage and p1 partition that will pull data from object store or external storage

4. Query both the partitions and explain plans

This blog will be useful to leverage capabilities on Oracle Database (for now Autonomous Database) on OCI. I’ll be posting a blog on how capabilities of dbms_cloud can be extended to Oracle databases in future… Stay tuned !!

About Author:

Phani Turlapati is a Cloud Engineer at Oracle Inc. He’s been with Oracle for over 3 years. He started out at Solution engineering hub at Oracle focusing on various design solutions to help customer to value-add to their businesses. He’s a part of Database Team focusing on all things database. Prior to Oracle he had worked as Software Engineer developing application on Java Platform.

The blog is my view and is no way related to Oracle

Feel free to comment on the above post. Your feedback is valuable!