Understanding ONLINE Split Partition In Oracle Database 12c.

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