Oracle split partition feature allows you to split partition which are already created. We can take benefit of this feature when in specific partition lots of records are inserted and its tough to maintain it individually.
In this post we will see how can we partition different types of partition.
1.Splitting a partition of range-partitioned table
SQL> SELECT TABLE_NAME,PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME ='TRANSACTIONS';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
TRANSACTIONS Y_2011
TRANSACTIONS Y_2012
TRANSACTIONS Y_2013
TRANSACTIONS Y_2014
TRANSACTIONS Y_2015
TRANSACTIONS Y_2016
TRANSACTIONS Y_2017
TRANSACTIONS Y_2018
TRANSACTIONS Y_2019
9 rows selected.
SQL> ALTER TABLE TRANSACTIONS SPLIT PARTITION Y_2012
INTO (PARTITION Y_2012_H1 VALUES LESS THAN (TO_DATE('01-JUL-2012','DD-MON-YYYY')),
PARTITION Y_2012_H2); 2 3
Table altered.
SQL> SELECT TABLE_NAME,PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME ='TRANSACTIONS';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
TRANSACTIONS Y_2011
TRANSACTIONS Y_2012_H1
TRANSACTIONS Y_2012_H2
TRANSACTIONS Y_2013
TRANSACTIONS Y_2014
TRANSACTIONS Y_2015
TRANSACTIONS Y_2016
TRANSACTIONS Y_2017
TRANSACTIONS Y_2018
TRANSACTIONS Y_2019
10 rows selected.
SQL> 2.Split partition of list-partitioned table
SQL> SELECT TABLE_NAME,PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME ='DEPT_LIST';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
DEPT_LIST P1
DEPT_LIST P2
SQL> ALTER TABLE DEPT_LIST SPLIT PARTITION P1 INTO (PARTITION P1_SLS VALUES ('SLS'),PARTITION P1_ACC);
Table altered.
SQL> SELECT TABLE_NAME,PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME ='DEPT_LIST';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
DEPT_LIST P1_ACC
DEPT_LIST P1_SLS
DEPT_LIST P2
SQL> 3.Splitting partition of interval partitioned table
SQL> SELECT TABLE_NAME,PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME ='TRAN_INTERVAL';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
TRAN_INTERVAL P1
TRAN_INTERVAL P2
TRAN_INTERVAL P3
TRAN_INTERVAL P4
TRAN_INTERVAL P5
TRAN_INTERVAL P6
6 rows selected.
SQL> ALTER TABLE TRAN_INTERVAL SPLIT PARTITION P2 INTO (PARTITION P2_H1 VALUES LESS THAN (TO_DATE('15-JAN-2019','DD-MON-YYYY')),PARTITION P2_H2);
Table altered.
SQL> SELECT TABLE_NAME,PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME ='TRAN_INTERVAL';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
TRAN_INTERVAL P1
TRAN_INTERVAL P2_H1
TRAN_INTERVAL P2_H2
TRAN_INTERVAL P3
TRAN_INTERVAL P4
TRAN_INTERVAL P5
TRAN_INTERVAL P6
7 rows selected.4.Splitting *-hash partitioned table
Here I took example of range-hash partition.
SQL> SELECT TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME FROM USER_TAB_SUBPARTITIONS WHERE TABLE_NAME='TRANSACTIONS_MAIN_COMP_HASH' AND PARTITION_NAME='Y_2012';
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------ --------------------------------------------------------------------------------------------------------------------------------
TRANSACTIONS_MAIN_COMP_HASH Y_2012 SYS_SUBP1125
TRANSACTIONS_MAIN_COMP_HASH Y_2012 SYS_SUBP1126
TRANSACTIONS_MAIN_COMP_HASH Y_2012 SYS_SUBP1127
TRANSACTIONS_MAIN_COMP_HASH Y_2012 SYS_SUBP1128
SQL> ALTER TABLE TRANSACTIONS_MAIN_COMP_HASH SPLIT PARTITION Y_2012 INTO (PARTITION Y_2012_H1 VALUES LESS THAN (TO_DATE('01-JUL-2012','DD-MON-YYYY')) SUBPARTITIONS 4 , PARTITION Y_2012_H2);
Table altered.
SQL>
SQL> SELECT TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME FROM USER_TAB_SUBPARTITIONS WHERE TABLE_NAME='TRANSACTIONS_MAIN_COMP_HASH' AND PARTITION_NAME IN ('Y_2012_H1','Y_2012_H2');
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------ --------------------------------------------------------------------------------------------------------------------------------
TRANSACTIONS_MAIN_COMP_HASH Y_2012_H1 SYS_SUBP1161
TRANSACTIONS_MAIN_COMP_HASH Y_2012_H1 SYS_SUBP1162
TRANSACTIONS_MAIN_COMP_HASH Y_2012_H1 SYS_SUBP1163
TRANSACTIONS_MAIN_COMP_HASH Y_2012_H1 SYS_SUBP1164
TRANSACTIONS_MAIN_COMP_HASH Y_2012_H2 SYS_SUBP1165
TRANSACTIONS_MAIN_COMP_HASH Y_2012_H2 SYS_SUBP1166
TRANSACTIONS_MAIN_COMP_HASH Y_2012_H2 SYS_SUBP1167
TRANSACTIONS_MAIN_COMP_HASH Y_2012_H2 SYS_SUBP1168
8 rows selected.
SQL> 5.Splitting partition of *-range partitioned table
SQL> SELECT TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME FROM USER_TAB_SUBPARTITIONS WHERE TABLE_NAME='SUBSCRIPTION_DTL';
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------ --------------------------------------------------------------------------------------------------------------------------------
SUBSCRIPTION_DTL P_RR1 P_RR1_R1
SUBSCRIPTION_DTL P_RR1 P_RR1_R2
SUBSCRIPTION_DTL P_RR1 P_RR1_R3
SUBSCRIPTION_DTL P_RR2 P_RR2_R1
SUBSCRIPTION_DTL P_RR2 P_RR2_R2
SUBSCRIPTION_DTL P_RR2 P_RR2_R3
SUBSCRIPTION_DTL P_RR3 P_RR3_R1
SUBSCRIPTION_DTL P_RR3 P_RR3_R2
SUBSCRIPTION_DTL P_RR3 P_RR3_R3
9 rows selected.
SQL> ALTER TABLE SUBSCRIPTION_DTL SPLIT PARTITION P_RR2 AT (TO_DATE('15-MAR-2019','DD-MON-YYYY')) INTO (PARTITION P_RR2_1,PARTITION P_RR2_2);
Table altered.
SQL> SELECT TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME FROM USER_TAB_SUBPARTITIONS WHERE TABLE_NAME='SUBSCRIPTION_DTL';
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------ --------------------------------------------------------------------------------------------------------------------------------
SUBSCRIPTION_DTL P_RR2_1 P_RR2_1_R2
SUBSCRIPTION_DTL P_RR2_1 P_RR2_1_R3
SUBSCRIPTION_DTL P_RR2_1 P_RR2_1_R1
SUBSCRIPTION_DTL P_RR1 P_RR1_R1
SUBSCRIPTION_DTL P_RR1 P_RR1_R2
SUBSCRIPTION_DTL P_RR1 P_RR1_R3
SUBSCRIPTION_DTL P_RR3 P_RR3_R1
SUBSCRIPTION_DTL P_RR3 P_RR3_R2
SUBSCRIPTION_DTL P_RR3 P_RR3_R3
SUBSCRIPTION_DTL P_RR2_2 P_RR2_2_R1
SUBSCRIPTION_DTL P_RR2_2 P_RR2_2_R2
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------ --------------------------------------------------------------------------------------------------------------------------------
SUBSCRIPTION_DTL P_RR2_2 P_RR2_2_R3
12 rows selected.
Split partition of *-list partitioned table :
SQL> SELECT TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME FROM USER_TAB_SUBPARTITIONS WHERE TABLE_NAME='DEPT_LIST_LIST';
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------ --------------------------------------------------------------------------------------------------------------------------------
DEPT_LIST_LIST P_L1 P_L1_L1
DEPT_LIST_LIST P_L1 P_L1_L2
DEPT_LIST_LIST P_L1 P_L1_L3
DEPT_LIST_LIST P_L2 P_L2_L1
DEPT_LIST_LIST P_L2 P_L2_L2
DEPT_LIST_LIST P_L3 P_L3_L1
6 rows selected.
SQL> ALTER TABLE DEPT_LIST_LIST SPLIT PARTITION P_L1 INTO (PARTITION P1_L1_1 VALUES ('GJ'),PARTITION P1_L1_2);
Table altered.
SQL> SELECT TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME FROM USER_TAB_SUBPARTITIONS WHERE TABLE_NAME='DEPT_LIST_LIST';
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------ --------------------------------------------------------------------------------------------------------------------------------
DEPT_LIST_LIST P_L2 P_L2_L1
DEPT_LIST_LIST P_L2 P_L2_L2
DEPT_LIST_LIST P_L3 P_L3_L1
DEPT_LIST_LIST P1_L1_1 P1_L1_1_L1
DEPT_LIST_LIST P1_L1_1 P1_L1_1_L2
DEPT_LIST_LIST P1_L1_1 P1_L1_1_L3
DEPT_LIST_LIST P1_L1_2 P1_L1_2_L1
DEPT_LIST_LIST P1_L1_2 P1_L1_2_L2
DEPT_LIST_LIST P1_L1_2 P1_L1_2_L3
9 rows selected.
SQL> 7.Split Index partition
Just like table partition we can split global index partition.
Note : We can not split local index partition , to split local index partition , associated table partition must be split.
SQL> SELECT INDEX_NAME,PARTITION_NAME FROM USER_IND_PARTITIONS WHERE INDEX_NAME='TRANSACTION_GLOBAL_IND';
INDEX_NAME PARTITION_NAME
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------
TRANSACTION_GLOBAL_IND P1
TRANSACTION_GLOBAL_IND P10
TRANSACTION_GLOBAL_IND P2
TRANSACTION_GLOBAL_IND P3
TRANSACTION_GLOBAL_IND P4
TRANSACTION_GLOBAL_IND P5
TRANSACTION_GLOBAL_IND P6
TRANSACTION_GLOBAL_IND P7
TRANSACTION_GLOBAL_IND P8
TRANSACTION_GLOBAL_IND P9
10 rows selected.
SQL> ALTER INDEX TRANSACTION_GLOBAL_IND SPLIT PARTITION P2 AT (TO_DATE('01-JUL-2012','DD-MON-YYYY')) INTO (PARTITION P2_H1,PARTITION P2_H2);
Index altered.
SQL> SELECT INDEX_NAME,PARTITION_NAME FROM USER_IND_PARTITIONS WHERE INDEX_NAME='TRANSACTION_GLOBAL_IND';
INDEX_NAME PARTITION_NAME
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------
TRANSACTION_GLOBAL_IND P1
TRANSACTION_GLOBAL_IND P10
TRANSACTION_GLOBAL_IND P2_H1
TRANSACTION_GLOBAL_IND P2_H2
TRANSACTION_GLOBAL_IND P3
TRANSACTION_GLOBAL_IND P4
TRANSACTION_GLOBAL_IND P5
TRANSACTION_GLOBAL_IND P6
TRANSACTION_GLOBAL_IND P7
TRANSACTION_GLOBAL_IND P8
TRANSACTION_GLOBAL_IND P9
11 rows selected.8.Split into multiple partitions
With Split partition operation we can even split single partition into multiple partitions.
SQL> SELECT TABLE_NAME,PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='DEPT_LIST_EX';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
DEPT_LIST_EX P1
DEPT_LIST_EX P2
SQL> ALTER TABLE DEPT_LIST_EX SPLIT PARTITION P1 INTO (PARTITION P1_SLS VALUES ('SLS'),PARTITION P1_ACC VALUES('ACC'),PARTITION P1_DBA);
Table altered.
SQL> SELECT TABLE_NAME,PARTITION_NAME FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME='DEPT_LIST_EX';
TABLE_NAME PARTITION_NAME
------------------------------ ------------------------------
DEPT_LIST_EX P1_ACC
DEPT_LIST_EX P1_DBA
DEPT_LIST_EX P1_SLS
DEPT_LIST_EX P2
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
