Managing Hybrid Partitioned Tables

Creating Hybrid Partitioned Tables

You can use the EXTERNAL PARTITION ATTRIBUTES clause of the CREATE TABLE statement to determine hybrid partitioning for a table. The partitions of the table can be external and or internal.

A hybrid partitioned table enables partitions to reside both in database data files (internal partitions) and in external files and sources (external partitions). You can create and query a hybrid partitioned table to utilize the benefits of partitioning with classic partitioned tables, such as pruning, on data that is contained in both internal and external partitions.

The EXTERNAL PARTITION ATTRIBUTES clause of the CREATE TABLE statement is defined at the table level for specifying table level external parameters in the hybrid partitioned table, such as:

The access driver type, such as ORACLE_LOADER, ORACLE_DATAPUMP, ORACLE_HDFS, ORACLE_HIVE
The default directory for all external partitions files
The access parameters

The EXTERNAL clause of the PARTITION clause defines the partition as an external partition. When there is no EXTERNAL clause, the partition is an internal partition. You can specify for each external partition different attributes than the default attributes defined at the table level, such the directory. For example, in Example 4-42 the DEFAULT DIRECTORY value for partitions sales_data2, sales_data3, and sales_data_acfs is different than the DEFAULT DIRECTORY value defined in the EXTERNAL PARTITION ATTRIBUTES clause.

When there is no external file defined for an external partition, the external partition is empty. It can be populated with an external file by using an ALTER TABLE MODIFY PARTITION statement. Note that at least one partition must be an internal partition.

In Example 4-42, a hybrid range-partitioned table is a created with four external partitions and two internal partitions. The external comma-separated (CSV) data files are stored in the sales_data , sales_data2, sales_data3, and sales_data_acfs directories defined by the DEFAULT DIRECTORY clauses. sales_data is defined as the overall DEFAULT DIRECTORY in the EXTERNAL PARTITION ATTRIBUTES clause. The other directories are defined at the partition level. sales_2014 and sales_2015 are internal partitions. Data directory sales_data_acfs is stored on an Oracle ACFS file system to illustrate the use of that storage option.

In Example 4-43, an additional external partition is added to the hybrid range-partitioned table.

Example 4-42 Creating a Hybrid Range-Partitioned Table

REM CONNECT AS SYSDBA user, run the following to set up data directories that contain the data files
CREATE DIRECTORY sales_data AS '/u01/my_data/sales_data1';
GRANT READ,WRITE ON DIRECTORY sales_data TO hr;

CREATE DIRECTORY sales_data2 AS '/u01/my_data/sales_data2';
GRANT READ,WRITE ON DIRECTORY sales_data2 TO hr;

CREATE DIRECTORY sales_data3 AS '/u01/my_data/sales_data3';
GRANT READ,WRITE ON DIRECTORY sales_data3 TO hr;

REM set up a data directory on an Oracle ACFS mount point (file system)
CREATE DIRECTORY sales_data_acfs AS '/u01/acfsmounts/acfs1';
GRANT READ,WRITE ON DIRECTORY sales_data_acfs TO hr;

CONNECT AS hr, run the following
CREATE TABLE hybrid_partition_table
  ( prod_id       NUMBER        NOT NULL,
    cust_id       NUMBER        NOT NULL,
    time_id       DATE          NOT NULL,
    channel_id    NUMBER        NOT NULL,
    promo_id      NUMBER        NOT NULL,
    quantity_sold NUMBER(10,2)  NOT NULL,
    amount_sold   NUMBER(10,2)  NOT NULL
  )
    EXTERNAL PARTITION ATTRIBUTES (
      TYPE ORACLE_LOADER 
      DEFAULT DIRECTORY sales_data
       ACCESS PARAMETERS(
         FIELDS TERMINATED BY ','
         (prod_id,cust_id,time_id DATE 'dd-mm-yyyy',channel_id,promo_id,quantity_sold,amount_sold)
       ) 
      REJECT LIMIT UNLIMITED
     ) 
    PARTITION BY RANGE (time_id)
    (PARTITION sales_2014 VALUES LESS THAN (TO_DATE('01-01-2015','dd-mm-yyyy')),
     PARTITION sales_2015 VALUES LESS THAN (TO_DATE('01-01-2016','dd-mm-yyyy')),
     PARTITION sales_2016 VALUES LESS THAN (TO_DATE('01-01-2017','dd-mm-yyyy')) EXTERNAL 
          LOCATION ('sales2016_data.txt'),
     PARTITION sales_2017 VALUES LESS THAN (TO_DATE('01-01-2018','dd-mm-yyyy')) EXTERNAL 
          DEFAULT DIRECTORY sales_data2 LOCATION ('sales2017_data.txt'),
     PARTITION sales_2018 VALUES LESS THAN (TO_DATE('01-01-2019','dd-mm-yyyy')) EXTERNAL 
          DEFAULT DIRECTORY sales_data3 LOCATION ('sales2018_data.txt'),
     PARTITION sales_2019 VALUES LESS THAN (TO_DATE('01-01-2020','dd-mm-yyyy')) EXTERNAL
          DEFAULT DIRECTORY sales_data_acfs LOCATION ('sales2019_data.txt')
 );

Example 4-43 Adding an External Partition to a Hybrid Range-artitioned Table

ALTER TABLE hybrid_partition_table 
      ADD PARTITION sales_2020 VALUES LESS THAN (TO_DATE('01-01-2021','dd-mm-yyyy'))
          EXTERNAL DEFAULT DIRECTORY sales_data_acfs  LOCATION ('sales2020_data.txt');

See Also:

Hybrid Partitioned Tables

Converting to Hybrid Partitioned Tables

You can convert a table with only internal partitions to a hybrid partitioned table.

In Example 4-44, an internal range partitioned table is converted to a hybrid partitioned table. You must add external partition attributes to an existing table first, then add external partitions. Note that at least one partition must be an internal partition.

Example 4-44 Converting to a Hybrid Range-Partitioned Table

CREATE TABLE internal_to_hypt_table (
    prod_id       NUMBER        NOT NULL,
    cust_id       NUMBER        NOT NULL,
    time_id       DATE          NOT NULL,
    channel_id    NUMBER        NOT NULL,
    promo_id      NUMBER        NOT NULL,
    quantity_sold NUMBER(10,2)  NOT NULL,
    amount_sold   NUMBER(10,2)  NOT NULL
  )
   PARTITION by range (time_id) 
   (PARTITION sales_2014 VALUES LESS THAN (TO_DATE('01-01-2015','dd-mm-yyyy'))
  );

SELECT HYBRID FROM USER_TABLES WHERE TABLE_NAME = 'INTERNAL_TO_HYPT_TABLE';
HYB
---
NO

ALTER TABLE internal_to_hypt_table 
  ADD EXTERNAL PARTITION ATTRIBUTES
   (TYPE ORACLE_LOADER 
     DEFAULT DIRECTORY sales_data
     ACCESS PARAMETERS (
       FIELDS TERMINATED BY ','
       (prod_id,cust_id,time_id DATE 'dd-mm-yyyy',channel_id,promo_id,quantity_sold,amount_sold)
     )
   )
;

ALTER TABLE internal_to_hypt_table 
      ADD PARTITION sales_2015 VALUES LESS THAN (TO_DATE('01-01-2016','dd-mm-yyyy'))
          EXTERNAL LOCATION ('sales2015_data.txt');

ALTER TABLE internal_to_hypt_table 
      ADD PARTITION sales_2016 VALUES LESS THAN (TO_DATE('01-01-2017','dd-mm-yyyy'))
          EXTERNAL LOCATION ('sales2016_data.txt');

SELECT HYBRID FROM USER_TABLES WHERE TABLE_NAME = 'INTERNAL_TO_HYPT_TABLE';
HYB
---
YES

SELECT DEFAULT_DIRECTORY_NAME FROM USER_EXTERNAL_TABLES WHERE TABLE_NAME = 'INTERNAL_TO_HYPT_TABLE';
DEFAULT_DIRECTORY_NAME
---------------------------------------------------------------------------------------------
SALES_DATA

See Also:

Hybrid Partitioned Tables

Converting Hybrid Partitioned Tables to Internal Partitioned Tables

You can convert a hybrid partitioned table to a table with only internal partitions.

In Example 4-45, a hybrid partitioned table is converted to an internal range partitioned table. First, you must drop the external partitions and then you can drop the external partition attributes.

Example 4-45 Converting from a Hybrid Partitioned Table to an Internal Table

CREATE TABLE hypt_to_int_table
  ( prod_id       NUMBER        NOT NULL,
    cust_id       NUMBER        NOT NULL,
    time_id       DATE          NOT NULL,
    channel_id    NUMBER        NOT NULL,
    promo_id      NUMBER        NOT NULL,
    quantity_sold NUMBER(10,2)  NOT NULL,
    amount_sold   NUMBER(10,2)  NOT NULL
  )
    EXTERNAL PARTITION ATTRIBUTES (
     TYPE ORACLE_LOADER 
     DEFAULT DIRECTORY sales_data
       ACCESS PARAMETERS(
        FIELDS TERMINATED BY ','
        (prod_id,cust_id,time_id DATE 'dd-mm-yyyy',channel_id,promo_id,quantity_sold,amount_sold)
       ) 
      REJECT LIMIT UNLIMITED
     ) 
    PARTITION BY RANGE (time_id)
    (PARTITION sales_2014 VALUES LESS THAN (TO_DATE('01-01-2015','dd-mm-yyyy')),
     PARTITION sales_2015 VALUES LESS THAN (TO_DATE('01-01-2016','dd-mm-yyyy')),
     PARTITION sales_2016 VALUES LESS THAN (TO_DATE('01-01-2017','dd-mm-yyyy')) 
          EXTERNAL LOCATION ('sales2016_data.txt'),
     PARTITION sales_2017 VALUES LESS THAN (TO_DATE('01-01-2018','dd-mm-yyyy')) 
          EXTERNAL DEFAULT DIRECTORY sales_data2 LOCATION ('sales2017_data.txt'),
     PARTITION sales_2018 VALUES LESS THAN (TO_DATE('01-01-2019','dd-mm-yyyy')) 
          EXTERNAL DEFAULT DIRECTORY sales_data3 LOCATION ('sales2018_data.txt'),
     PARTITION sales_2019 VALUES LESS THAN (TO_DATE('01-01-2020','dd-mm-yyyy')) 
          EXTERNAL DEFAULT DIRECTORY sales_data_acfs LOCATION ('sales2019_data.txt')
 );

SELECT HYBRID FROM USER_TABLES WHERE TABLE_NAME = 'HYPT_TO_INT_TABLE';
HYB
---
YES

ALTER TABLE hypt_to_int_table DROP PARTITION sales_2016;
ALTER TABLE hypt_to_int_table DROP PARTITION sales_2017;
ALTER TABLE hypt_to_int_table DROP PARTITION sales_2018;
ALTER TABLE hypt_to_int_table DROP PARTITION sales_2019;

ALTER TABLE hypt_to_int_table DROP EXTERNAL PARTITION ATTRIBUTES();

SELECT HYBRID FROM USER_TABLES WHERE TABLE_NAME = 'HYPT_TO_INT_TABLE';
HYB
---
NO

See Also:

Hybrid Partitioned Tables

Using ADO With Hybrid Partitioned Tables

You can use Automatic Data Optimization (ADO) policies with hybrid partitioned tables under some conditions.

In Example 4-46, note that ADO policies are only defined on the internal partitions of the table.

Example 4-46 Using ADO with a Hybrid Partitioned Table

SQL> CREATE TABLE hypt_ado_table
  ( prod_id       NUMBER        NOT NULL,
    cust_id       NUMBER        NOT NULL,
    time_id       DATE          NOT NULL,
    channel_id    NUMBER        NOT NULL,
    promo_id      NUMBER        NOT NULL,
    quantity_sold NUMBER(10,2)  NOT NULL,
    amount_sold   NUMBER(10,2)  NOT NULL
  )
    EXTERNAL PARTITION ATTRIBUTES (
     TYPE ORACLE_LOADER 
     DEFAULT DIRECTORY sales_data
       ACCESS PARAMETERS(
        FIELDS TERMINATED BY ','
        (prod_id,cust_id,time_id DATE 'dd-mm-yyyy',channel_id,promo_id,quantity_sold,amount_sold)
       ) 
      REJECT LIMIT UNLIMITED
     ) 
    PARTITION BY RANGE (time_id)
    (PARTITION sales_2014 VALUES LESS THAN (TO_DATE('01-01-2015','dd-mm-yyyy')),
     PARTITION sales_2015 VALUES LESS THAN (TO_DATE('01-01-2016','dd-mm-yyyy')),
     PARTITION sales_2016 VALUES LESS THAN (TO_DATE('01-01-2017','dd-mm-yyyy')) 
          EXTERNAL LOCATION ('sales2016_data.txt'),
     PARTITION sales_2017 VALUES LESS THAN (TO_DATE('01-01-2018','dd-mm-yyyy')) 
          EXTERNAL DEFAULT DIRECTORY sales_data2 LOCATION ('sales2017_data.txt'),
     PARTITION sales_2018 VALUES LESS THAN (TO_DATE('01-01-2019','dd-mm-yyyy')) 
          EXTERNAL DEFAULT DIRECTORY sales_data3 LOCATION ('sales2018_data.txt'),
     PARTITION sales_2019 VALUES LESS THAN (TO_DATE('01-01-2020','dd-mm-yyyy')) 
          EXTERNAL DEFAULT DIRECTORY sales_data4 LOCATION ('sales2019_data.txt')
 );
Table created.

SQL> SELECT HYBRID FROM USER_TABLES WHERE TABLE_NAME = 'HYPT_ADO_TABLE';
HYB
---
YES

SQL> ALTER TABLE hypt_ado_table MODIFY PARTITION sales_2014 ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW AFTER 6 MONTHS OF NO MODIFICATION;
Table altered.

SQL> ALTER TABLE hypt_ado_table MODIFY PARTITION sales_2015 ILM ADD POLICY ROW STORE COMPRESS ADVANCED ROW AFTER 6 MONTHS OF NO MODIFICATION;
Table altered.

SQL> SELECT POLICY_NAME, POLICY_TYPE, ENABLED FROM USER_ILMPOLICIES;
POLICY_NAME    POLICY_TYPE     ENA
-------------  --------------- -----
P1             DATA MOVEMENT   YES
P2             DATA MOVEMENT   YES

See Also:

Using Automatic Data Optimization for information about ADO policies

Splitting Partitions in a Hybrid Partitioned Table

In Example 4-47, the default (MAXVALUE) partition is split into a two partitions: a new partition and the existing default position. You can split a default partition similar to splitting any other partition.

Example 4-47 Splitting the Default Partition in a Hybrid Partitioned Table

CREATE TABLE hybrid_split_table
  ( prod_id       NUMBER        NOT NULL,
    cust_id       NUMBER        NOT NULL,
    time_id       DATE          NOT NULL,
    channel_id    NUMBER        NOT NULL,
    promo_id      NUMBER        NOT NULL,
    quantity_sold NUMBER(10,2)  NOT NULL,
    amount_sold   NUMBER(10,2)  NOT NULL
  )
    EXTERNAL PARTITION ATTRIBUTES (
     TYPE ORACLE_LOADER 
     DEFAULT DIRECTORY sales_data
       ACCESS PARAMETERS(
        FIELDS TERMINATED BY ','
        (prod_id,cust_id,time_id DATE 'dd-mm-yyyy',channel_id,promo_id,quantity_sold,amount_sold)
       ) 
      REJECT LIMIT UNLIMITED
     ) 
    PARTITION BY RANGE (time_id)
     (PARTITION sales_2016 VALUES LESS THAN (TO_DATE('01-01-2017','dd-mm-yyyy')) 
          EXTERNAL LOCATION ('sales2016_data.txt'),
      PARTITION sales_2017 VALUES LESS THAN (TO_DATE('01-01-2018','dd-mm-yyyy')) 
          EXTERNAL LOCATION ('sales2017_data.txt'),
     PARTITION sales_2018 VALUES LESS THAN (TO_DATE('01-01-2019','dd-mm-yyyy')),
     PARTITION sales_2019 VALUES LESS THAN (TO_DATE('01-01-2020','dd-mm-yyyy')),
     PARTITION sales_future VALUES LESS THAN (MAXVALUE)
 );

SELECT HYBRID FROM USER_TABLES WHERE TABLE_NAME = 'HYBRID_SPLIT_TABLE';
HYB
---
YES

SELECT DEFAULT_DIRECTORY_NAME FROM USER_EXTERNAL_TABLES WHERE TABLE_NAME = 'HYBRID_SPLIT_TABLE';
DEFAULT_DIRECTORY_NAME
--------------------------------------------------------------------------------
SALES_DATA

INSERT INTO hybrid_split_table VALUES (1001,100,TO_DATE('10-02-2018','dd-mm-yyyy'),10,15,500,7500);
INSERT INTO hybrid_split_table VALUES (1002,110,TO_DATE('15-06-2018','dd-mm-yyyy'),12,18,100,3200);
...
INSERT INTO hybrid_split_table VALUES (1002,110,TO_DATE('12-01-2019','dd-mm-yyyy'),12,18,150,4800);
INSERT INTO hybrid_split_table VALUES (1001,100,TO_DATE('16-02-2019','dd-mm-yyyy'),10,15,400,6500);
...
INSERT INTO hybrid_split_table VALUES (1002,110,TO_DATE('19-02-2020','dd-mm-yyyy'),12,18,150,4800);
INSERT INTO hybrid_split_table VALUES (1001,100,TO_DATE('12-03-2020','dd-mm-yyyy'),10,15,400,6500);
...

SELECT * FROM hybrid_split_table PARTITION(sales_2016);
   PROD_ID    CUST_ID TIME_ID   CHANNEL_ID   PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
---------- ---------- --------- ---------- ---------- ------------- -----------
      1001        100 10-JAN-16         10         15           500        7500
      1002        110 25-JAN-16         12         18           100        3200
...

SELECT * FROM hybrid_split_table PARTITION(sales_2017);
   PROD_ID    CUST_ID TIME_ID   CHANNEL_ID   PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
---------- ---------- --------- ---------- ---------- ------------- -----------
      1002        110 15-JAN-17         12         18           100        3200
      1001        100 10-FEB-17         10         15           500        7500
...

SELECT * FROM hybrid_split_table PARTITION(sales_2018);
   PROD_ID    CUST_ID TIME_ID   CHANNEL_ID   PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
---------- ---------- --------- ---------- ---------- ------------- -----------
      1001        100 10-FEB-18         10         15           500        7500
      1002        110 15-JUN-18         12         18           100        3200
...

SELECT * FROM hybrid_split_table PARTITION(sales_2019);
   PROD_ID    CUST_ID TIME_ID   CHANNEL_ID   PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
---------- ---------- --------- ---------- ---------- ------------- -----------
      1002        110 12-JAN-19         12         18           150        4800
      1001        100 16-FEB-19         10         15           400        6500
...

SELECT * FROM hybrid_split_table PARTITION(sales_future);
   PROD_ID    CUST_ID TIME_ID   CHANNEL_ID   PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
---------- ---------- --------- ---------- ---------- ------------- -----------
      1002        110 19-FEB-20         12         18           150        4800
      1001        100 12-MAR-20         10         15           400        6500
      1001        100 31-MAR-20         10         15           600        8000
      2105        101 25-APR-20         12         19           100        3000

ALTER TABLE hybrid_split_table 
  SPLIT PARTITION sales_future INTO
  (PARTITION sales_2020 VALUES LESS THAN (TO_DATE('01-01-2021','dd-mm-yyyy')),
   PARTITION sales_future
   );

SELECT * FROM hybrid_split_table PARTITION(sales_2020);
   PROD_ID    CUST_ID TIME_ID   CHANNEL_ID   PROMO_ID QUANTITY_SOLD AMOUNT_SOLD
---------- ---------- --------- ---------- ---------- ------------- -----------
      1002        110 19-FEB-20         12         18           150        4800
      1001        100 12-MAR-20         10         15           400        6500
      1001        100 31-MAR-20         10         15           600        8000
      2105        101 25-APR-20         12         19           100        3000

SELECT * FROM hybrid_split_table PARTITION(sales_future);
no rows selected