With Oracle Database 19c there is now also the possibility of hybrid storage: With the help of External Table technology, data can be stored in partitions of a table inside and also outside the database. How this works in general, we have explained in one of our posting Hybrid Partitioned Tables. We explained how to create Hybrid Partitioned Tables with CREATE TABLE, how to convert a Partitioned Table into a Hybrid Partitioned Table and how to do this inside Autonomous Database and Object Storage. 

But what does this mean? How can you move data to external partitions? Or the other way round: How can data be stored in internal partitions? This is a typical requirement in the Lifecycle management environments. 

The solution is quite simple: You outsource the data with the help of an auxiliary table and use the EXCHANGE PARTITION command to exchange and thus relocate the data. Another idea is to use the SPILT command.

Let’s demonstrate the procedure with the EXCHANGE command using a very simple example. Let’s take the EMPLOYEES table from the HR schema as a basis and convert it into a partitioned table. The requirement is: The partition with the lowest salaries should be moved to an external partition. The steps to do this are as follows:

Note: If the HR schema and the EMPLOYEES table are not available, you can get them from Github.     

In preparation we create the partitioned table EMPLOYEES_HYBRID and fill it with data from the EMPLOYEES table. At the beginning the table EMPLOYEES_HYBRID consists of the 4 internal partitions (RANGE): salary_4000, salary_10000, salary_30000 and of course a partition for MAXVALUE.

DROP TABLE hr.employees_hybrid;

CREATE TABLE hr.employees_hybrid
   (  EMPLOYEE_ID varchar2(10),
      SALARY      number,
      JOB_TITLE   varchar2 (35))
    PARTITION BY RANGE (salary)
    ( PARTITION salary_4000 VALUES less than (4000),   -- intern
      PARTITION salary_10000 VALUES less than (10000), -- intern
      PARTITION salary_30000 VALUES less than (30000), -- intern
      PARTITION salary_max values less than (MAXVALUE))
/

INSERT INTO hr.employees_hybrid
SELECT e.employee_id, e.salary, j.job_title 
FROM hr.employees e JOIN hr.jobs j ON e.job_id=j.job_id;

COMMIT;     

Now we want to swap the data from the salary_4000 partition. The swapping of data works as always with  EXTERNAL TABLE technology, the driver ORACLE_DATAPUMP and the corresponding AS SELECT statement.
Therefore we now create the table EXT_HELP as an auxiliary table. 
 

/*
DROP TABLE ext_help;
! rm salaryless3000.dmp 
*/
CREATE TABLE ext_help
ORGANIZATION EXTERNAL
 (
    TYPE oracle_datapump
     DEFAULT DIRECTORY home
     LOCATION ('salaryless3000.dmp')
    )
    REJECT LIMIT UNLIMITED
AS SELECT employee_id, salary, job_title FROM hr.employees_hybrid partition(SALARY_4000);

SQL> select max(salary) from ext_help;

MAX(SALARY)
-----------
       3900

In the next step we convert the partitioned table EMPLOYEES_HYBRID into a hybrid partitioned table. For this we have to add the syntax EXTERNAL PARTITION ATTRIBUTES to specify the location (LOCATION) and the type of EXTERNAL TABLE driver (TYPE). In our example we use the HOME directory and the ORACLE_DATAPUMP driver.

SQL> select directory_name from all_directories;

DIRECTORY_NAME
--------------------------------------------------
MY_EXP
HOME

ALTER TABLE hr.employees_hybrid 
ADD EXTERNAL PARTITION ATTRIBUTES
   ( TYPE oracle_datapump 
     DEFAULT DIRECTORY home 
     REJECT LIMIT UNLIMITED );

Now we have a Hybrid Partitioned Table, but we do not yet have external partitions.

SQL> select table_name, type_name from user_XTERNAL_PART_TABLES
  2  /

TABLE_NAME           TYPE_NAME
-------------------- --------------------
EMPLOYEES_HYBRID     ORACLE_DATAPUMP

SQL> select partition_name from USER_XTERNAL_TAB_PARTITIONS;
no rows selected

SQL> select partition_name, high_value from user_tab_partitions where table_name like 'EMP%';

PARTITION_NAME                 HIGH_VALUE
------------------------------ --------------------
SALARY_10000                   10000
SALARY_30000                   30000
SALARY_4000                    4000
SALARY_MAX                     MAXVALUE

And here is a simple trick: to swap the data of the partition, you can simply use the EXCHANGE PARTITION command to swap the data segments. Remember: you can use it to change a partition or subpartition into a non-partitioned table and a non-partitioned table into a partition or subpartition of a partitioned table.

ALTER table hr.employees_hybrid
EXCHANGE PARTITION(salary_4000) WITH TABLE ext_help;

And we are done and can delete or use the auxiliary table otherwise. Let’s check the partitions.

SQL> select partition_name from USER_XTERNAL_TAB_PARTITIONS 
     where table_name like 'EMP%';

PARTITION_NAME
------------------------------
SALARY_4000

SQL> select partition_name, high_value 
     from user_tab_partitions where table_name like 'EMP%';

PARTITION_NAME                 HIGH_VALUE
------------------------------ --------------------
SALARY_10000                   10000
SALARY_30000                   30000
SALARY_4000                    4000
SALARY_MAX                     MAXVALUE

SQL> select max(salary) from employees_hybrid partition (salary_4000);

MAX(SALARY)
-----------
3900

The reverse method also works: You can also swap external data to an internal partition in this way: You could also store data as an internal partition in this way. This would make the lifecycle management perfect!   

But please keep in mind there are also some limitations on Hybrid Partitioned Tables. The following list gives a few examples:

  • Only single level LIST and RANGE partitioning are supported.
  • No unique indexes or global unique indexes. (Partial indexes are allowed.)
  • SPLIT, MERGE, and MOVE operations are not allowed on external partitions.
  • LOB, LONG, and ADT types are not allowed.

The complete list of supported operations and current limitations can be found in VLDB and Partitioning Guide chapter 2.1.10 Hybrid Partitioned Tables.

To workaround the LOB limitation a simple two table trick – one internal table for the hot data and one external table with DATAPUMP driver for the cold data  –  can be easily implemented and provide a solution.