Hybrid Partitioning is a new feature introduced in Oracle 19c. Using this feature, you can create a combination of external and internal partitions for a single table.
Reminder: For a table or partition of type external, only the metadata and its definition are stored in the database; the actual data resides outside the database at the operating system level.
Example:
Suppose we want to store information such as national ID, first name, last name, and organization ID in a table named mytbl. Using the following statement, we will partition the table based on org_id:
CREATE TABLE mytbl(national_id NUMBER, name VARCHAR2(20),last_name VARCHAR2(20),org_id number)
PARTITION BY LIST (org_id)
(
PARTITION p1 VALUES (1),
PARTITION p2 VALUES (2)
);
We want to store the information of people with org_id 1 and 2 inside the database (internal):
insert into mytbl values(100,’ali’,’rezai’,1);
insert into mytbl values(101,’hadi’,’alavi’,1);
insert into mytbl values(102,’reza’,’karimi’,2);
insert into mytbl values(103,’hossein’,’akbari’,2);
commit;
For certain reasons, we want to store the data of people with org_id 3 and 4 outside the database as external data. This information is stored in two files: part3.txt and part4.txt:
[oracle@ol7 ~]$ cat /part3/part3.txt
104,javad,akbarian,3
105,mina,karimi,3
106,sima,kabiri,3
107,nima,kasiri,3
[oracle@ol7 ~]$ vi /part4/part4.txt
108,kimya,hasani,4
109,kobra,armani,4
110,kazem,kalvandi,4
111,usef,kalvani,4
To access this data within the database, we create two directories named part3dir and part4dir:
SQL> create directory part3dir as ‘/part3’;
Directory created.
SQL> create directory part4dir as ‘/part4’;
Directory created.
Using the Hybrid Partitioning feature, we now add two external partitions to the mytbl table:
– Enable EXTERNAL PARTITION attribute for the mytbl table:
ALTER TABLE mytbl
ADD EXTERNAL PARTITION ATTRIBUTES
(TYPE ORACLE_LOADER
DEFAULT DIRECTORY part3dir
ACCESS PARAMETERS (
FIELDS TERMINATED BY ‘,’ (national_id,name,last_name,org_id)
)
);
– Add the required partitions:
ALTER TABLE mytbl ADD PARTITION p3 VALUES (3) EXTERNAL LOCATION (part3dir:’part3.txt’);
ALTER TABLE mytbl ADD PARTITION p4 VALUES (4) EXTERNAL LOCATION (part4dir:’part4.txt’);
After adding these two partitions, the table structure will look like this:
create table MYTBL
(
national_id NUMBER,
name VARCHAR2(20),
last_name VARCHAR2(20),
org_id NUMBER
)
organization external
(
type ORACLE_LOADER
default directory PART3DIR
access parameters
(
FIELDS TERMINATED BY ‘,’ (national_id,name,last_name,org_id)
)
)
reject limit 0
partition by list (ORG_ID)
(
partition P1 values (1),
partition P2 values (2),
partition P3 values (3),
partition P4 values (4)
);
Now you can access the externally stored data as well. For example, to see only the data of people with org_id=3, run:
select * from mytbl where org_id=3;
You can check the execution plan for org_id=2 and org_id=3 as follows:
select * from mytbl where org_id=2;
select * from mytbl where org_id=3;
Key Features and Limitations of Hybrid Partitioning
1.Only partial indexes are supported for such tables:
SQL> create index ind1 on mytbl(name);
ORA-14354: operation not supported for a hybrid-partitioned table
SQL> create index ind1 on mytbl(name) local;
ORA-14354: operation not supported for a hybrid-partitioned table
SQL> create index ind1 on mytbl(name) indexing partial;
Index created
SQL> create index ind1 on mytbl(name) local indexing partial;
Index created
- DML operations can only be performed on internal partitions:
SQL> insert into mytbl values(130,'hossein','zaker',4);
ORA-14466: Data in a read-only partition or subpartition cannot be modified.
SQL> insert into mytbl values(130,'hossein','zaker',1);
1 row inserted
- LOB and LONG data types cannot be added to these tables:
SQL> alter table MYTBL add pic blob;
ORA-03001: unimplemented feature
SQL> alter table MYTBL add id number;
Table altered
- To find tables using Hybrid Partitioning, you can run this query:
select TABLE_NAME,HYBRID from user_tables where HYBRID=’YES’;