Exchange Subpartition in Oracle

We can exchange subpartition in oracle the same way we exchange partition.Let’s see few examples.

1.Exchange subpartition of *-Hash partitioned table :

Here I took example of List-Hash Partitioning method.

SQL> CREATE TABLE LIST_HASH
 (CITY_CD CHAR(2),
 CITY_NAME VARCHAR2(50),
 STATE_CD CHAR(2),
 ZONE_CD CHAR(2))
 PARTITION BY LIST(STATE_CD) SUBPARTITION BY HASH(ZONE_CD)
 (PARTITION P1 VALUES('GJ')
 (SUBPARTITION P1_H1,
  SUBPARTITION P1_H2) ,
  PARTITION P2 VALUES('RJ')
  (SUBPARTITION P2_H1,
   SUBPARTITION P2_H2))  2    3    4    5    6    7    8    9   10   11   12  
 13  ;

Table created.

SQL> CREATE TABLE LIST_HASH_T1 AS SELECT * FROM LIST_HASH WHERE 2=1;

Table created.

SQL> ALTER TABLE LIST_HASH EXCHANGE SUBPARTITION P1_H1 WITH TABLE LIST_HASH_T1;

Table altered.

SQL> 

2.Exchange subpartition of *-Range Partitioned table :

Here I took example of List-Range partitioning method.

SQL> CREATE TABLE DEPT_LIST_RANGE
 (EMP_ID NUMBER,
EMP_NAME VARCHAR2(30),
AGE VARCHAR2(50),
DEPT_ID CHAR(3))
PARTITION BY LIST(DEPT_ID) SUBPARTITION BY RANGE(AGE) 
(PARTITION P1 VALUES('SLS','ACC')
(SUBPARTITION A1 VALUES LESS THAN (25),
SUBPARTITION A2 VALUES LESS THAN (35),
SUBPARTITION A3 VALUES LESS THAN (50),
SUBPARTITION A_MAX VALUES LESS THAN (MAXVALUE)),
PARTITION P2 VALUES('HRM','MAR')
(SUBPARTITION B1 VALUES LESS THAN (25),
SUBPARTITION B2 VALUES LESS THAN (35),
SUBPARTITION B3 VALUES LESS THAN (50),
SUBPARTITION B_MAX VALUES LESS THAN (MAXVALUE)
));  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17  

Table created.


SQL> CREATE TABLE DEPT_LIST_RANGE_TEMP AS SELECT * FROM DEPT_LIST_RANGE WHERE 2=1;

Table created.

SQL>  ALTER TABLE DEPT_LIST_RANGE EXCHANGE SUBPARTITION A1 WITH TABLE DEPT_LIST_RANGE_TEMP;

Table altered.

SQL> 

3.Exchange subpartition of *-List partitioned table :

Here I took example of Range-List partitioning method.

SQL> CREATE TABLE DEPT_RANGE_LIST
  2  (EMP_ID NUMBER,
EMP_NAME VARCHAR2(30),
AGE VARCHAR2(50),
DEPT_ID CHAR(3))
PARTITION BY RANGE(AGE) SUBPARTITION BY LIST(DEPT_ID) 
(PARTITION P_R1 VALUES LESS THAN(25)
(SUBPARTITION P_R1_L1 VALUES('SLS','ACC'),
 SUBPARTITION P_R1_L2 VALUES('HRM','MAR')
),
PARTITION P_R2 VALUES LESS THAN(35)
(SUBPARTITION P_R2_L1 VALUES('SLS','ACC'),
 SUBPARTITION P_R2_L2 VALUES('HRM','MAR')
),
PARTITION P_R3 VALUES LESS THAN(MAXVALUE)
(SUBPARTITION P_R3_L1 VALUES('SLS','ACC'),
 SUBPARTITION P_R3_L2 VALUES('HRM','MAR')
));  3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18  

Table created.

SQL> 

SQL> CREATE TABLE DEPT_RANGE_LIST_TEMP AS SELECT * FROM DEPT_RANGE_LIST WHERE 2=1;

Table created.

SQL> ALTER TABLE DEPT_RANGE_LIST EXCHANGE SUBPARTITION P_R2_L2 WITH TABLE DEPT_RANGE_LIST_TEMP;

Table altered.

SQL> 

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp