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’; 
Top comments (0)