1.Exchange Partition of *-List Partition
Here I took example of Range-List partitioning method.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 |
SQL> CREATE TABLE LIST_COMP_PART 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> CREATE TABLE LIST_SIMPLE_PART (EMP_ID NUMBER, EMP_NAME VARCHAR2(30), AGE VARCHAR2(50), DEPT_ID CHAR(3)) PARTITION BY LIST (DEPT_ID) (PARTITION P1 VALUES('SLS','ACC'), PARTITION P2 VALUES('HRM','MAR')); 2 3 4 5 6 7 8 Table created. SQL> ALTER TABLE LIST_COMP_PART EXCHANGE PARTITION P_R1 WITH TABLE LIST_SIMPLE_PART; Table altered. SQL> |
2.Exchange Partition of *-Range Partitioned table :
Here I took example of Range-Range partitioning method.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 |
SQL> CREATE TABLE RANGE_RANGE_COMP (ID NUMBER, NAME VARCHAR2(50), REGISTER_DT DATE, JOINING_DT DATE) PARTITION BY RANGE (REGISTER_DT) SUBPARTITION BY RANGE(JOINING_DT) (PARTITION P_RR1 VALUES LESS THAN (TO_DATE('01-JAN-2019','dd-MON-yyyy')) (SUBPARTITION P_RR1_R1 VALUES LESS THAN (TO_DATE('31-JAN-2019','dd-MON-yyyy')), SUBPARTITION P_RR1_R2 VALUES LESS THAN (TO_DATE('28-FEB-2019','dd-MON-yyyy')), SUBPARTITION P_RR1_R3 VALUES LESS THAN (TO_DATE('31-MAR-2019','dd-MON-yyyy')) 2 3 4 5 6 7 8 9 10 11 ), PARTITION P_RR2 VALUES LESS THAN (TO_DATE('01-APR-2019','dd-MON-yyyy')) (SUBPARTITION P_RR2_R1 VALUES LESS THAN (TO_DATE('30-APR-2019','dd-MON-yyyy')), SUBPARTITION P_RR2_R2 VALUES LESS THAN (TO_DATE('31-MAY-2019','dd-MON-yyyy')), SUBPARTITION P_RR2_R3 VALUES LESS THAN (TO_DATE('30-JUN-2019','dd-MON-yyyy')) )); 12 13 14 15 16 17 Table created. SQL> CREATE TABLE RANGE_SIMPLE 2 (ID NUMBER, NAME VARCHAR2(50), REGISTER_DT DATE, JOINING_DT DATE) PARTITION BY RANGE(JOINING_DT) (PARTITION P1 VALUES LESS THAN (TO_DATE('31-JAN-2019','dd-MON-yyyy')), PARTITION P2 VALUES LESS THAN (TO_DATE('28-FEB-2019','dd-MON-yyyy')), PARTITION P3 VALUES LESS THAN (TO_DATE('31-MAR-2019','dd-MON-yyyy')) ); 3 4 5 6 7 8 9 10 Table created. SQL> ALTER TABLE RANGE_RANGE_COMP EXCHANGE PARTITION P_RR1 WITH TABLE RANGE_SIMPLE; Table altered. |
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
