News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

Oracle 19c New Feature Hybrid Partitioned Tables

  • Posted by Gavin Soorma
  • On July 30, 2019
  • 0 Comments
  • 18c, 19c, 19c new feature, external tables, hybrid partition, partial indexes, partitioning

In Oracle 12c Release 2, one of the new features was the ability to create partitions on External tables.

New in Oracle 19c is the feature where we can create a Hybrid Partitioned table – so some partitions exist in the database and some partitions are hosted external to the database – maybe on a normal file system or ACFS or even Hadoop File System.

Using this feature we may like to move older non-active partitions of a table to possibly cheaper storage hosted outside the database – while the active data is hosted in partitions stored inside the Oracle database.

So now we have a concept of External and Internal partitions and the Hybrid Partition Table feature enables us to have this integration of internal and external partitions – all in the same table!

For such Hybrid Partitioned tables, the partitions can reside on both tablespaces in the Oracle database as well as external sources such as Linux files with comma-separated values (CSV) records or files on Hadoop Distributed File System (HDFS) with Java server.

Let us have a look at this feature with an example.

Let us assume we have some sales data and for the year 2017 and 2018 the data is located in flat files (sales_2017.dat and sales_2018.dat).
 

[oracle@host03 dpdump]$ cat sales_2017.dat
CUST_A 1001 100 20-JAN-2017
CUST_B 1001 110 20-JAN-2017
CUST_C 1001 200 21-JAN-2017
CUST_D 1000 108 22-JAN-2017
CUST_E 1002 10  24-JAN-2017

[oracle@host03 dpdump]$ cat sales_2018.dat 
CUST_A 1001 1   20-JAN-2018
CUST_B 1001 110 20-JAN-2018
CUST_C 1001 300 21-JAN-2018
CUST_D 1000 108 22-JAN-2018
CUST_E 1002 90  24-JAN-2018

We now create a HYBRID Partitioned Table which is range partitioned on the column DATE_OF_SALES.

Partitions for the year 2017 and 2018 are external and partition for the year 2019 is internal.

Note the EXTERNAL LOCATION clause which indicates that the partition is an external partition and the data is contained in the file mentioned. The external files are located in the directory location defined for the directory DATA_PUMP_DIR.

 

SQL> CREATE TABLE demo.sales 
(cust_name varchar2(10), 
 prod_id number,
 amount_sold number ,
 date_of_sale date)
EXTERNAL PARTITION ATTRIBUTES (
TYPE oracle_loader
DEFAULT DIRECTORY data_pump_dir
ACCESS PARAMETERS 
(RECORDS DELIMITED BY NEWLINE FIELDS TERMINATED BY WHITESPACE)
)
PARTITION BY RANGE (date_of_sale) 
(PARTITION sales_2017 VALUES LESS THAN (TO_DATE('01-JAN-2018','DD-MON-YYYY')) EXTERNAL LOCATION ('sales_2017.dat'),
 PARTITION sales_2018 VALUES LESS THAN (TO_DATE('01-JAN-2019','DD-MON-YYYY')) EXTERNAL LOCATION ('sales_2018.dat'),
 PARTITION sales_2019 VALUES LESS THAN (TO_DATE('01-JAN-2020','DD-MON-YYYY'))
)
;    

Table created.

SQL> select hybrid from dba_tables where table_name='SALES';

HYB
---
YES

 
Let us issue some queries against the external partitions and also insert some rows into the internal partition.
 

SQL> select * from demo.sales 
    partition (sales_2017);

CUST_NAME     PROD_ID AMOUNT_SOLD DATE_OF_S
---------- ---------- ----------- ---------
CUST_A		 1001	      100 20-JAN-17
CUST_B		 1001	      110 20-JAN-17
CUST_C		 1001	      200 21-JAN-17
CUST_D		 1000	      108 22-JAN-17
CUST_E		 1002	       10 24-JAN-17

SQL> select * from demo.sales
    partition (sales_2018);

CUST_NAME     PROD_ID AMOUNT_SOLD DATE_OF_S
---------- ---------- ----------- ---------
CUST_A		 1001		1 20-JAN-18
CUST_B		 1001	      110 20-JAN-18
CUST_C		 1001	      300 21-JAN-18
CUST_D		 1000	      108 22-JAN-18
CUST_E		 1002	       90 24-JAN-18

SQL> insert into demo.sales
    values 
    ('CUST_A',1003,50,'02-JAN-2019');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from demo.sales
    partition (sales_2019);

CUST_NAME     PROD_ID AMOUNT_SOLD DATE_OF_S
---------- ---------- ----------- ---------
CUST_A		 1003	       50 02-JAN-19
 

0 Comments

Leave Reply

Your email address will not be published. Required fields are marked *