Hybrid Partition Table in Oracle 19c

By -

 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

2. 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

3. 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

4. To find tables using Hybrid Partitioning, you can run this query:

select TABLE_NAME,HYBRID from user_tables  where HYBRID=’YES’;
Vahid Yousefzadeh

Written by Vahid Yousefzadeh

I have been a DBA since 2011 and I work with Oracle technology. Linkdin: linkedin.com/in/vahidusefzadeh telegram channel ID:@oracledb vahidusefzadeh@gmail.com

Comments

Post a Comment

Popular posts from this blog

Oracle 23ai — Speed up IMPDP Using NOVALIDATE Constraints

By -
  When executing the   IMPDP   command, some operations do not support parallel execution. Creating constraints is one such operation. Oracle must validate the current data in the table before creating a constraint, which requires a full table scan for each validation. This process can be very time-consuming. Consider the following constraints: ALTER TABLE VAHID.MYTB ADD CONSTRAINT CHECK1 CHECK (col3 > 50 ); ALTER TABLE VAHID.MYTB ADD CONSTRAINT CHECK2 CHECK (col9 IN ( 5 , 15 )); We drop the  MYTB  table and recreate it using a dump file: SQL > drop table mytb; Table dropped. W -1 Processing object type TABLE_EXPORT / TABLE / TABLE_DATA W -1 . . imported "VAHID"."MYTB" 19 GB 337830912 rows in 83 seconds using external_table W -1 Processing object type TABLE_EXPORT / TABLE / CONSTRAINT / CONSTRAINT W -1 Completed 2 CONSTRAINT objects in 208 seconds As shown, creating the two constraints ta...
Read more

Staging Tables in Oracle 23ai

By -
  As you may know, using   staging tables   is a common practice in   Data Warehouse   environments, primarily during the data loading process into the   Warehouse Database . Typically, raw data received from   source databases   is first loaded into a temporary area, referred to as   staging tables . After cleansing inconsistent and invalid data and transforming it into a standard format, the cleaned data is stored in the final operational tables. In addition to initial data loading,   staging tables   are also used for updates and are often truncated before reuse. In   Oracle 23ai , a new minor feature has been introduced for this purpose, allowing users to create tables specifically designed as   staging tables . This is achieved by adding the   FOR STAGING   clause to the   CREATE TABLE   statement. Creating Staging Tables Using the   FOR STAGING   clause, staging tables can be created as ...
Read more

Boolean Data Type in Oracle ِDatabase 23ai

By -
Image
  Oracle introduced the Boolean data type in SQL with version 23ai (although this data type has existed in PL/SQL previously). With this feature, you can define table columns as Boolean during table creation. SQL > CREATE TABLE jadval1 (id NUMBER, is_accept BOOLEAN ); Table created You can also use the keyword  BOOL  instead of  BOOLEAN : SQL > CREATE TABLE jadval1 (id NUMBER, is_accept BOOL); Table created You can set default values for Boolean columns: SQL > CREATE TABLE table1 (id NUMBER, is_accept BOOL DEFAULT FALSE ); Table created To populate the  is_accept  column, you can use the three keywords:  TRUE ,  FALSE , and  NULL : SQL > insert into table1 values ( 1 , true ); 1 row inserted SQL > insert into table1 values ( 2 , null ); 1 row inserted SQL > insert into table1 values ( 3 , false ); 1 row inserted SQL > select * from table1; ID IS_ACCEPT ---------- ---------- ...
Read more