With Oracle Database version 19c a new concept called Hybrid Partitioned Tables is being introduced. As the name implies the partitions of hybrid partitioned tables can exist in both – inside the Oracle database in tablespaces and outside in external sources, such as Linux files, files on Hadoop Distributed File System (HDFS),and files in Cloud Object Storage. Hybrid Partitioned Tables enhance the functionality of partitioning for mixed Big Data scenarios where large portions of a table can reside in external partitions.

The Hybrid Partitioned Tables feature extends Oracle partitioning by enabling partitions to reside in both – in Oracle database segments and in external files and sources.To accomplish this, the two concepts – external table function and partitioning – have been integrated. Hybrid partitioned tables support all existing external table driver types for external partitions including ORACLE_ DATAPUMP, ORACLE_LOADER, ORACLE_HDFS and ORACLE_ HIVE. ORACLE_LOADER is the default external tables access driver to read data from flat files, ORACLE_DATAPUMP access driver however can write data to a dump file in a binary format that can only be read by the ORACLE_DATAPUMP access driver. With ORACLE_HDFS and ORACLE_HIVE data stored in HDFS and Hive tables on Hadoop and Hive clients can be accessed as if that data was stored in tables in an Oracle database. As a prerequisite you need to have access to a directory object in the database to access the external sources.

To create hybrid partitioned tables, an extended external table syntax is used. Hybrid partitioned tables can be created with CREATE TABLE or ALTER TABLE. With the ALTER TABLE command, for example, “normally” partitioned tables can be subsequently enriched with external partitions. A good introduction can be found in the manual “VLDB and Partitioning Guide” in the chapter Hybrid Partitioned Tables. Among other things, the restrictions are also described. For example operations like SPLIT, MERGE and MOVE are not allowed and only single level partitioning with LIST and RANGE is possible. Furthermore, there is currently no support for LOB, LONG and ADT data types.

Creating Hybrid Partitioned Tables

In scenario 1, a hybrid partitioned table is created with CREATE TABLE. The partition DEPTNO_10 is an internal partition, DEPTNO_20 an external partition. The clause EXTERNAL PARTITION ATTRIBUTES specifies the driver (here: ORACLE_LOADER) and the directory (here: HOME) for the external partition(s). Let’s assume we have generated the data in the flat file “emp_dept_20.csv”. The CSV file emp_dept_20.csv located in the  directory HOME stores the information on the DEPTNO_20 partition.
Tip: With the SQL*Plus command SET MARKUP CSV {ON|OFF} [DELIMI[TER] character] [QUOTE {ON|OFF}], data from tables can be easily listed in CSV format. 

Let’s first check if the directory HOME exists.

SQL> SELECT directory_name, directory_path 
     FROM all_directories;

DIRECTORY_NAME  DIRECTORY_PATH
--------------- ------------------------------------------------------------
LOBHOME         /home/oracle/LOB
TEST            /home/oracle/test
HOME            /home/oracle
...

In general, you can use single level RANGE and LIST partitioning methods when creating a hybrid partitioned table. In the example, a hybrid partitioned table with LIST partitions on the column DEPTNO is created.

DROP TABLE scott.ext_emp_dept_hybrid_1;

CREATE TABLE scott.ext_emp_dept_hybrid_1
   (    "ENAME" varchar2(10),
        "DNAME" varchar2(14), 
         DEPTNO number)
   EXTERNAL PARTITION ATTRIBUTES
    ( 
      TYPE oracle_loader
      DEFAULT DIRECTORY home
      REJECT LIMIT UNLIMITED 
    ) 
    PARTITION BY LIST (deptno)
    ( PARTITION deptno_10 VALUES (10) , -- intern
      PARTITION deptno_20 VALUES (20) EXTERNAL LOCATION ('emp_dept_20.csv')) -- extern
/

-- INSERT data into partition 
INSERT INTO "SCOTT".EXT_EMP_DEPT_HYBRID_1
SELECT e.ename, d.dname, d.deptno FROM scott.emp e JOIN scott.dept d ON d.deptno=e.deptno WHERE d.deptno=10;

-- query
SQL> SELECT * FROM scott.ext_emp_dept_hybrid_1;

ENAME      DNAME              DEPTNO
---------- -------------- ----------
CLARK      ACCOUNTING             10
KING       ACCOUNTING             10
MILLER     ACCOUNTING             10
"SMITH"    "RESEARCH"             20
"JONES"    "RESEARCH"             20
"SCOTT"    "RESEARCH"             20
"ADAMS"    "RESEARCH"             20
"FORD"     "RESEARCH"             20

8 rows selected.

With an ALTER TABLE command, the partitioned table can be subsequently enriched with external partitions. In the example, the external partition DEPTNO_30 is added.

SQL> ALTER TABLE scott.ext_emp_dept_hybrid_1
     ADD PARTITION deptno_30 VALUES (30)
          EXTERNAL DEFAULT DIRECTORY home LOCATION ('emp_dept_30.csv');

You can read data from the external table partition DEPTNO_10 and insert (also update or delete) data on the internal partition. Remember, you can only use DML operations on internal partitions of a hybrid partitioned table because external partitions are treated as read-only partitions. Partition operations such as adding and dropping partitions is possible in a normal manner.

To move data to an external source you need to use an auxiliary external table with external table driver ORACLE_ DATAPUMP instead. In our example, these files have the names emp_dept_20.exp or emp_dept_30.exp. The following example creates the file emp_dept_20.exp. Then the file emp_dept_30.exp is created using the same procedure.

DROP TABLE ext_emp_dept_help; 

CREATE TABLE ext_emp_dept_help 
ORGANIZATION EXTERNAL 
(
  TYPE oracle_datapump 
  DEFAULT DIRECTORY home
  LOCATION ('emp_dept_20.exp')
 ) 
  REJECT LIMIT UNLIMITED 
  AS SELECT e.ename, d.dname, deptno FROM scott.dept d JOIN scott.emp e USING (deptno) WHERE deptno=20
/
--DROP TABLE ext_emp_dept_help;

Now we are able to create the hybrid partitioned table. The external partition DEPTNO_20_30 accesses the external files emp_dept_20.exp and emp_dept_30.exp, which we previously created with the auxiliary table and stored in the HOME directory.

DROP TABLE scott.ext_emp_dept_hybrid_2;

CREATE TABLE scott.ext_emp_dept_hybrid_2
   (    "ENAME" varchar2(10) ,
        "DNAME" varchar2(14), 
         DEPTNO number)
   EXTERNAL PARTITION ATTRIBUTES
    ( 
      TYPE oracle_datapump
      DEFAULT DIRECTORY home
    ) 
    PARTITION BY LIST (deptno)
    ( PARTITION deptno_10 VALUES (10), -- intern
      PARTITION deptno_20_30 VALUES (20,30) EXTERNAL LOCATION ('emp_dept_20.exp','emp_dept_30.exp') -- extern
    )
/

Altering Partitioned Tables to Hybrid Partitioned Tables

In scenario 2, a list partitioned table is converted into a hybrid partitioned table. We use a “normal” list partitioned table with the internal partition DEPTNO_10.

DROP TABLE scott.ext_emp_dept_hybrid_3;

CREATE TABLE scott.ext_emp_dept_hybrid_3
   (    "ENAME" varchar2(10),
        "DNAME" varchar2(14), 
         DEPTNO number)
    PARTITION BY LIST (deptno)
    ( PARTITION deptno_10 values (10));

-- Insert into partition
INSERT INTO scott.ext_emp_dept_hybrid_3
SELECT e.ename, d.dname, d.deptno FROM scott.emp e JOIN scott.dept d ON d.deptno=e.deptno WHERE d.deptno=10;

To be able to include external partitions, the clause EXTERNAL PARTITION ATTRIBUTES must first be added. 

ALTER TABLE scott.ext_emp_dept_hybrid_3 
ADD EXTERNAL PARTITION ATTRIBUTES
   ( TYPE oracle_loader 
     DEFAULT DIRECTORY home 
     REJECT LIMIT UNLIMITED );

In the next step, external partitions can then be added. The external partition DEPTNO_20_30 is added with data from the files emp_dept_20.csv and emp_dept_30.csv.

ALTER TABLE scott.ext_emp_dept_hybrid_3  
      ADD PARTITION deptno_20_30 VALUES (20,30) EXTERNAL LOCATION ('emp_dept_20.csv', 'emp_dept_30.csv');

Hybrid Partitioned Tables and Oracle Object Storage

With the PL/SQL package DBMS_CLOUD and the procedure CREATE_HYBRID_PART_TABLE you can create an external partitioned table with files residing in the cloud. The procedure supports external partitioned files in the supported cloud object storage services, including Oracle Cloud Infrastructure (OCI) Object Storage, Azure Blob Storage, Amazon S3 and Amazon S3-Compatible Object Storage. This allows to run queries on external data from Oracle Autonomous Databases and also from On-Premises installations (from 19c on). However, this package is not pre-installed with 19c on-premises databases. You need to manually install DBMS_CLOUD and also configure users or roles to use it. For information on how to install and configure the package refer to the MOS-NOTE with Doc ID 2748362.1. In scenario 3, the external information is stored in the Oracle Object Storage – in our case the CSV files emp_dept_20.csv and emp_dept_30.csv. As with loading data with Data Pump, it must also be ensured in this case that communication with the Autonomous Database is secured and only authorised data is loaded. For this purpose, the database credentials have been created beforehand with the package DBMS_CLOUD and set as default. We check the database credentials. If they are not yet available, they can be set with ALTER DATABASE PROPERTY.

SQL> SELECT credential_name, username, enabled, comments 
     FROM dba_credentials;

CREDENTIAL_NAME USERNAME                                 ENABL COMMENTS                                
--------------- ---------------------------------------- ----- ----------------------------------------
CREDENTIAL_US1  oracleidentitycloudservice/ulrike.schwin TRUE  {"comments":"Created via DBMS_CLOUD.crea
                n@oracle.com                                   te_credential"}     

-- if not enabled use
-- alter database property set default_credential = 'ADMIN.CREDENTIAL_US1';

The existence of the files in the object storage can also be checked in advance with DBMS_CLOUD.

SQL> SELECT object_name, bytes 
     FROM dbms_cloud.list_objects('CREDENTIAL_US1','https://objectstorage.eu-frankfurt 1.oraclecloud.com/
n/oraseemeadesandbox/b/USBUCKET/')
     WHERE object_name LIKE 'emp%';

OBJECT_NAME                                             BYTES
-------------------------------------------------- ----------
emp_dept_20.csv                                           114
emp_dept_20.exp                                         12288
emp_dept_30.csv                                           121
emp_dept_30.exp                                         12288

We now create a hybrid partitioned table with the procedure DBMS_CLOUD.CREATE_HYBRID_PART_TABLE.  In the argument FORMAT we can use type CSV.

DROP TABLE ext_emp_dept_hybrid_auto;

BEGIN 
DBMS_CLOUD.CREATE_HYBRID_PART_TABLE(
    table_name      => 'EXT_EMP_DEPT_HYBRID_AUTO', 
    credential_name => 'CREDENTIAL_US1', 
    format          => json_object('type' VALUE 'CSV'), 
    column_list     => 'ename varchar2(10), dname varchar2(14), deptno number',
    partitioning_clause => 'partition by list (deptno)
      ( partition deptno_30 values (30) external location -- extern
         (''https://objectstorage.eu-frankfurt 1.oraclecloud.com/n/oraseemeadesandbox/b/ USBUCKET/o/emp_dept_30.csv''),
        partition deptno_20 values (20) external location -- extern
         (''https://objectstorage.eu-frankfurt 1.oraclecloud.com/n/oraseemeadesandbox/b/ USBUCKET/o/emp_dept_20.csv''),
        partition deptno_10 values (10) )'   -- intern
     );
END;
/

To be sure that the access with External Table will be successful, check the status in advance with VALIDATE_EXTERNAL_TABLE. If there is an error message, you should review the setup again.
If you want to use the driver ORACLE_DATAPUMP, the driver with the constant DBMS_CLOUD.FORMAT_TYPE_DATAPUMP must be used in the argument FORMAT. The two external files emp_dept_30.exp and emp_dept_20.exp from the first example have been loaded into the object storage in advance.

DROP TABLE ext_emp_dept_hybrid_dp;

BEGIN  
   DBMS_CLOUD.CREATE_HYBRID_PART_TABLE (
      table_name      => 'EXT_EMP_DEPT_HYBRID_DP',  
      credential_name => 'CREDENTIAL_US1',  
      format          => json_object('type' VALUE DBMS_CLOUD.FORMAT_TYPE_DATAPUMP),  
      column_list     => 'ename varchar2(10), dname varchar2(14), deptno number',
      partitioning_clause => 'partition by list (deptno)
                               ( partition deptno_30 values (30) external location
                                    ( ''https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/oraseemeadesandbox/b/USBUCKET/o/emp_dept_30.exp''),
                                 partition deptno_20 values (20) external location
                                    ( ''https://objectstorage.eu-frankfurt-1.oraclecloud.com/n/oraseemeadesandbox/b/USBUCKET/o/emp_dept_20.exp''),
                                 partition deptno_10 values (10) )'
     );
   END;
/

After successful validation, the information can be accessed as usual with SQL commands.

SQL> execute DBMS_CLOUD.VALIDATE_EXTERNAL_TABLE (table_name => 'EXT_EMP_DEPT_HYBRID_AUTO_DP');

PL/SQL procedure successfully completed.

Monitoring

As usual you can display information about hybrid partitioned tables in Oracle database views.
External tables – whether hybrid or not – can generally be listed with the view USER/ALL/DBA_EXTERNAL_TABLES.

SQL> SELECT table_name, owner, type_name 
     FROM all_external_tables;

TABLE_NAME                     OWNER      TYPE_NAME
------------------------------ ---------- --------------------
OPATCH_XML_INV                 SYS        ORACLE_LOADER
EXT_EMP_DEPT_HYBRID_LIST       SCOTT      ORACLE_DATAPUMP
EXT_EMP_DEPT_HYBRID_3          SCOTT      ORACLE_LOADER
EXT_EMP_DEPT_HYBRID_2          SCOTT      ORACLE_LOADER
EXT_EMP_DEPT_HYBRID_RANGE      SCOTT      ORACLE_DATAPUMP
EXT_EMP_DEPT_HYBRID_2          SCOTT      ORACLE_DATAPUMP
EXT_EMP_DEPT_HYBRID_1          SCOTT      ORACLE_LOADER
ORDERS_H                       OT         ORACLE_LOADER
HYPT_TO_INT_TABLE              US         ORACLE_LOADER
EXT_EMP_DEPT_HELP              US         ORACLE_DATAPUMP

9 rows selected.

If you only want to find out whether a table is hybrid partitioned, you can look it up in the data dictionary view ALL_TABLES and filter on the new column HYBRID. 

SQL> SELECT table_name, owner, hybrid 
     FROM all_tables where hybrid='YES';

TABLE_NAME                     OWNER      HYB
------------------------------ ---------- ---
EXT_EMP_DEPT_HYBRID_RANGE      SCOTT      YES
EXT_EMP_DEPT_HYBRID_2          SCOTT      YES
EXT_EMP_DEPT_HYBRID_LIST       SCOTT      YES
EXT_EMP_DEPT_HYBRID_1          SCOTT      YES
EXT_EMP_DEPT_HYBRID_2          SCOTT      YES
EXT_EMP_DEPT_HYBRID_3          SCOTT      YES
ORDERS_H                       OT         YES
HYPT_TO_INT_TABLE              US         YES

8 rows selected.

Hybrid partitioned tables can be also found in the data dictionary views such as ALL_XTERNAL_PART_TABLES, ALL_ XTERNAL_TAB_PARTITIONS etc. introduced with external partitioned tables.
The following query with USER_XTERNAL_PART_TABLES lists all partitioned external tables.
 

SQL> SELECT table_name, type_name, default_directory_name DIRECTORY,
            reject_limit, access_parameters
     FROM user_xternal_part_tables;

TABLE_NAME                     TYPE_NAME            DIRECTORY
------------------------------ -------------------- --------------------
REJECT_LIMIT
----------------------------------------
ACCESS_PARAMETERS
--------------------------------------------------------------------------------
HYPT_TO_INT_TABLE              ORACLE_LOADER        HOME
UNLIMITED
FIELDS TERMINATED BY ','
        (prod_id,cust_id,time_id DATE 'dd-mm-yyyy',chan
...

Conclusion

Oracle Hybrid Partitioned Tables integrate internal partitions and external partitions into a single partitioned table. Classical internal partitioned tables in combination with Oracle external partitioned tables form a new table partitioning concept called hybrid partitioned tables. Appealing scenarios especially with lifecycle management and the usage of cheaper storage solutions such as cloud object storages can be achieved. As an example, CERN used this technology to leverage 1 PB of data with Oracle Autonomous Database.

Further Reading