Understanding ONLINE Split Partition In Oracle Database 12c.
- Performance Tuning
- December 5, 2022
- 0 Comment
In Oracle Database 12c Release 2 (12.2) the SPLIT PARTITION operations on heap tables can be performed online so they don’t block DML.
This is done by the addition of the ONLINE keyword, which also causes local and global indexes to be updated without having to specify the UPDATE INDEXES clause.
The idea here is to split a SINGLE partition into MULTIPLE partitions based on partition key column.
/* Create Table - test_tab */
SQL> CREATE TABLE test_tab
2 (
3 id NUMBER,
4 name VARCHAR2(100 BYTE),
5 eff_date DATE,
6 CONSTRAINT test_tab_pk PRIMARY KEY (id)
7 )
8 PARTITION BY RANGE (eff_date)
9 (
10 PARTITION test_tab_2017 VALUES LESS THAN (MAXVALUE)
11 );
Table created.
/* Create Index - indx_test_tab_eff_date */
SQL> CREATE INDEX indx_test_tab_eff_date ON test_tab (eff_date) LOCAL;
Index created.
/* Perform DML On test_tab */
SQL> INSERT INTO test_tab VALUES (10,'TEST_NAME_1',TO_DATE('01-JUL-2015','DD-MON-YYYY'));
1 row created.
SQL> INSERT INTO test_tab VALUES (20,'TEST_NAME_2',TO_DATE('01-JUL-2016','DD-MON-YYYY'));
1 row created.
SQL> INSERT INTO test_tab VALUES (30,'TEST_NAME_3',TO_DATE('01-JUL-2017','DD-MON-YYYY'));
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM test_tab;
ID NAME EFF_DATE
---------- ----------------- ---------
10 TEST_NAME_1 01-JUL-15
20 TEST_NAME_2 01-JUL-16
30 TEST_NAME_3 01-JUL-17
/* Run Stats On test_tab */
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'test_tab', cascade => TRUE);
PL/SQL procedure successfully completed.
SQL> COLUMN table_name FORMAT A30
SQL> COLUMN partition_name FORMAT A30
SQL> SELECT table_name,partition_name,num_rows FROM user_tab_partitions WHERE table_name = 'TEST_TAB' ORDER BY 1,2;
TABLE_NAME PARTITION_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
TEST_TAB TEST_TAB_2017 3
SQL> SELECT index_name,partition_name,status FROM user_ind_partitions WHERE index_name = 'INDX_TEST_TAB_EFF_DATE' ORDER BY 1,2;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
INDX_TEST_TAB_EFF_DATE TEST_TAB_2017 USABLE
SQL> COLUMN index_name FORMAT A30
SQL> COLUMN partitioned FORMAT A11
SQL> SELECT index_name,partitioned,status FROM user_indexes WHERE index_name IN ('TEST_TAB_PK','INDX_TEST_TAB_EFF_DATE');
INDEX_NAME PARTITIONED STATUS
------------------------------ ----------- --------
INDX_TEST_TAB_EFF_DATE YES N/A
TEST_TAB_PK NO VALID
/*Perform ONLINE SPLIT PARTITION To Split the Single Partition TEST_TAB_2017 Into Other Partitions - TEST_TAB_2015 & TEST_TAB_2016 Based On Column - EFF_DATE */
SQL> ALTER TABLE test_tab SPLIT PARTITION test_tab_2017 AT (TO_DATE('31-DEC-2015 23:59:59', 'DD-MON-YYYY HH24:MI:SS')) INTO (PARTITION test_tab_2015,PARTITION test_tab_2017) ONLINE;
Table altered.
SQL> ALTER TABLE test_tab SPLIT PARTITION test_tab_2017 AT (TO_DATE('31-DEC-2016 23:59:59', 'DD-MON-YYYY HH24:MI:SS')) INTO (PARTITION test_tab_2016,PARTITION test_tab_2017);
Table altered.
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'test_tab', cascade => TRUE);
PL/SQL procedure successfully completed.
SQL> COLUMN table_name FORMAT A30
SQL> COLUMN partition_name FORMAT A30
SQL> SELECT table_name,partition_name,num_rows FROM user_tab_partitions WHERE table_name = 'TEST_TAB' ORDER BY 1,2;
TABLE_NAME PARTITION_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
TEST_TAB TEST_TAB_2015 1
TEST_TAB TEST_TAB_2016 1
TEST_TAB TEST_TAB_2017 1
SQL> COLUMN index_name FORMAT A30
SQL> COLUMN partitioned FORMAT A11
SQL> SELECT index_name,partitioned,status FROM user_indexes WHERE index_name IN ('TEST_TAB_PK','INDX_TEST_TAB_EFF_DATE');
INDEX_NAME PARTITIONED STATUS
------------------------------ ----------- --------
INDX_TEST_TAB_EFF_DATE YES N/A
TEST_TAB_PK NO UNUSABLE
SQL> SELECT index_name,partition_name,status FROM user_ind_partitions WHERE index_name = 'INDX_TEST_TAB_EFF_DATE' ORDER BY 1,2;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
INDX_TEST_TAB_EFF_DATE TEST_TAB_2015 USABLE
INDX_TEST_TAB_EFF_DATE TEST_TAB_2016 UNUSABLE
INDX_TEST_TAB_EFF_DATE TEST_TAB_2017 UNUSABLE
/* Rebuild Partioned Index. */
SQL> ALTER INDEX indx_test_tab_eff_date REBUILD PARTITION test_tab_2016;
Index altered.
SQL> ALTER INDEX indx_test_tab_eff_date REBUILD PARTITION test_tab_2017;
Index altered.
/* Rebuild Normal B-Tree Index. */
SQL> ALTER INDEX test_tab_pk REBUILD;
Index altered.
SQL> SELECT index_name,partitioned,status FROM user_indexes WHERE index_name IN ('TEST_TAB_PK','INDX_TEST_TAB_EFF_DATE');
INDEX_NAME PARTITIONED STATUS
------------------------------ ----------- --------
INDX_TEST_TAB_EFF_DATE YES N/A
TEST_TAB_PK NO VALID
SQL> SELECT index_name,partition_name,status FROM user_ind_partitions WHERE index_name = 'INDX_TEST_TAB_EFF_DATE' ORDER BY 1,2;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
INDX_TEST_TAB_EFF_DATE TEST_TAB_2015 USABLE
INDX_TEST_TAB_EFF_DATE TEST_TAB_2016 USABLE
INDX_TEST_TAB_EFF_DATE TEST_TAB_2017 USABLE
SQL> SELECT table_name,partition_name,num_rows FROM user_tab_partitions WHERE table_name = 'TEST_TAB' ORDER BY 1,2;
TABLE_NAME PARTITION_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
TEST_TAB TEST_TAB_2015 1
TEST_TAB TEST_TAB_2016 1
TEST_TAB TEST_TAB_2017 1
