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
