8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Online SPLIT PARTITION and SPLIT SUBPARTITION in Oracle Database 12c Release 2 (12.2)
In Oracle Database 12c Release 2 (12.2) the SPLIT PARTITION and SPLIT SUBPARTITION 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.
Related articles.
- All Partitioning Articles
- Partitioning Enhancements in Oracle Database 12c Release 2 (12.2)
- Online Operation Enhancements in Oracle Database 12c Release 1 (12.1)
Online SPLIT PARTITION
Create a partitioned table with a single partition, along with a global and partitioned index.
DROP TABLE t1 PURGE;
CREATE TABLE t1 (
id NUMBER,
code VARCHAR2(10),
description VARCHAR2(50),
created_date DATE,
CONSTRAINT t1_pk PRIMARY KEY (id)
)
PARTITION BY RANGE (created_date)
(
PARTITION t1_2017 VALUES LESS THAN (MAXVALUE)
);
CREATE INDEX t1_created_date_idx ON t1 (created_date) LOCAL;
CREATE INDEX t1_code_idx ON t1 (code);
INSERT INTO t1 VALUES ( 1, 'ONE', 'Description for ONE', TO_DATE('01-JUL-2015','DD-MON-YYYY'));
INSERT INTO t1 VALUES ( 2, 'TWO', 'Description for ONE', TO_DATE('01-JUL-2015','DD-MON-YYYY'));
INSERT INTO t1 VALUES ( 3, 'THREE', 'Description for ONE', TO_DATE('01-JUL-2015','DD-MON-YYYY'));
INSERT INTO t1 VALUES ( 4, 'FOUR', 'Description for ONE', TO_DATE('01-JUL-2015','DD-MON-YYYY'));
INSERT INTO t1 VALUES ( 5, 'ONE', 'Description for ONE', TO_DATE('01-JUL-2016','DD-MON-YYYY'));
INSERT INTO t1 VALUES ( 6, 'TWO', 'Description for ONE', TO_DATE('01-JUL-2016','DD-MON-YYYY'));
INSERT INTO t1 VALUES ( 7, 'THREE', 'Description for ONE', TO_DATE('01-JUL-2016','DD-MON-YYYY'));
INSERT INTO t1 VALUES ( 8, 'FOUR', 'Description for ONE', TO_DATE('01-JUL-2016','DD-MON-YYYY'));
INSERT INTO t1 VALUES ( 9, 'ONE', 'Description for ONE', TO_DATE('01-JUL-2017','DD-MON-YYYY'));
INSERT INTO t1 VALUES (10, 'TWO', 'Description for ONE', TO_DATE('01-JUL-2017','DD-MON-YYYY'));
INSERT INTO t1 VALUES (11, 'THREE', 'Description for ONE', TO_DATE('01-JUL-2017','DD-MON-YYYY'));
INSERT INTO t1 VALUES (12, 'FOUR', 'Description for ONE', TO_DATE('01-JUL-2017','DD-MON-YYYY'));
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 't1', cascade => TRUE);
Display the current table partition and index information.
-- Table partitions.
COLUMN table_name FORMAT A30
COLUMN partition_name FORMAT A30
SELECT table_name,
partition_name,
num_rows
FROM user_tab_partitions
ORDER BY 1, 2;
TABLE_NAME PARTITION_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
T1 T1_2017 12
SQL>
-- Indexes.
COLUMN index_name FORMAT A30
COLUMN partitioned FORMAT A11
SELECT index_name,
partitioned,
status
FROM user_indexes
ORDER BY 1;
INDEX_NAME PARTITIONED STATUS
------------------------------ ----------- --------
T1_CODE_IDX NO VALID
T1_CREATED_DATE_IDX YES N/A
T1_PK NO VALID
SQL>
-- Index partitions.
SELECT index_name,
partition_name,
status
FROM user_ind_partitions
ORDER BY 1, 2;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
T1_CREATED_DATE_IDX T1_2017 USABLE
SQL>
Perform an online operation to split the 2017 partition to create a new partition for rows with a CREATED_DATE column value in 2015.
ALTER TABLE t1
SPLIT PARTITION t1_2017 AT (TO_DATE('31-DEC-2015 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
INTO (PARTITION t1_2015,
PARTITION t1_2017)
ONLINE;
EXEC DBMS_STATS.gather_table_stats(USER, 't1', cascade => TRUE);
Display the current status of the table partitions and indexes. Notice both the global and local indexes are left in a usable state without having to specify the UPDATE INDEXES clause.
-- Table partitions.
COLUMN table_name FORMAT A30
COLUMN partition_name FORMAT A30
SELECT table_name,
partition_name,
num_rows
FROM user_tab_partitions
ORDER BY 1, 2;
TABLE_NAME PARTITION_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
T1 T1_2015 4
T1 T1_2017 8
SQL>
-- Indexes.
COLUMN index_name FORMAT A30
COLUMN partitioned FORMAT A11
SELECT index_name,
partitioned,
status
FROM user_indexes
ORDER BY 1;
INDEX_NAME PARTITIONED STATUS
------------------------------ ----------- --------
T1_CODE_IDX NO VALID
T1_CREATED_DATE_IDX YES N/A
T1_PK NO VALID
SQL>
-- Index partitions.
SELECT index_name,
partition_name,
status
FROM user_ind_partitions
ORDER BY 1, 2;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
T1_CREATED_DATE_IDX T1_2015 USABLE
T1_CREATED_DATE_IDX T1_2017 USABLE
SQL>
For a comparison, perform an offline operation to split the 2017 partition to create a new partition for rows with a CREATED_DATE column value in 2016.
ALTER TABLE t1
SPLIT PARTITION t1_2017 AT (TO_DATE('31-DEC-2016 23:59:59', 'DD-MON-YYYY HH24:MI:SS'))
INTO (PARTITION t1_2016,
PARTITION t1_2017);
EXEC DBMS_STATS.gather_table_stats(USER, 't1', cascade => TRUE);
Display the current status of the table partitions and indexes. Notice both the global and local indexes on the affected partitions are left in a unusable state because we didn't specify the UPDATE INDEXES clause or ONLINE keyword.
-- Table partitions.
COLUMN table_name FORMAT A30
COLUMN partition_name FORMAT A30
SELECT table_name,
partition_name,
num_rows
FROM user_tab_partitions
ORDER BY 1, 2;
TABLE_NAME PARTITION_NAME NUM_ROWS
------------------------------ ------------------------------ ----------
T1 T1_2015 4
T1 T1_2016 4
T1 T1_2017 4
SQL>
-- Indexes.
COLUMN index_name FORMAT A30
COLUMN partitioned FORMAT A11
SELECT index_name,
partitioned,
status
FROM user_indexes
ORDER BY 1;
INDEX_NAME PARTITIONED STATUS
------------------------------ ----------- --------
T1_CODE_IDX NO UNUSABLE
T1_CREATED_DATE_IDX YES N/A
T1_PK NO UNUSABLE
SQL>
-- Index partitions.
COLUMN partition_name FORMAT A30
SELECT index_name,
partition_name,
status
FROM user_ind_partitions
ORDER BY 1, 2;
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
T1_CREATED_DATE_IDX T1_2015 USABLE
T1_CREATED_DATE_IDX T1_2016 UNUSABLE
T1_CREATED_DATE_IDX T1_2017 UNUSABLE
SQL>
Online SPLIT SUBPARTITION
Create a subpartitioned table with two partitions, each with two subpartitions, along with a global and partitioned index.
DROP TABLE t1 PURGE;
CREATE TABLE t1 (
id NUMBER,
code VARCHAR2(10),
description VARCHAR2(50),
created_date DATE,
CONSTRAINT t1_pk PRIMARY KEY (id)
)
PARTITION BY RANGE (created_date)
SUBPARTITION BY LIST (code)
SUBPARTITION TEMPLATE (
SUBPARTITION small VALUES ('ONE', 'TWO', 'THREE'),
SUBPARTITION others VALUES (DEFAULT)
)
(
PARTITION year_2016 VALUES LESS THAN ( TO_DATE('01-JAN-2017','DD-MON-YYYY')),
PARTITION year_2017 VALUES LESS THAN ( TO_DATE('01-JAN-2018','DD-MON-YYYY'))
);
CREATE INDEX t1_created_date_idx ON t1 (created_date) LOCAL;
CREATE INDEX t1_code_idx ON t1 (code);
INSERT INTO t1 VALUES (1, 'ONE', 'Description for ONE', TO_DATE('01-JUL-2016','DD-MON-YYYY'));
INSERT INTO t1 VALUES (2, 'TWO', 'Description for ONE', TO_DATE('01-JUL-2016','DD-MON-YYYY'));
INSERT INTO t1 VALUES (3, 'THREE', 'Description for ONE', TO_DATE('01-JUL-2016','DD-MON-YYYY'));
INSERT INTO t1 VALUES (4, 'FOUR', 'Description for ONE', TO_DATE('01-JUL-2016','DD-MON-YYYY'));
INSERT INTO t1 VALUES (5, 'ONE', 'Description for ONE', TO_DATE('01-JUL-2017','DD-MON-YYYY'));
INSERT INTO t1 VALUES (6, 'TWO', 'Description for ONE', TO_DATE('01-JUL-2017','DD-MON-YYYY'));
INSERT INTO t1 VALUES (7, 'THREE', 'Description for ONE', TO_DATE('01-JUL-2017','DD-MON-YYYY'));
INSERT INTO t1 VALUES (8, 'FOUR', 'Description for ONE', TO_DATE('01-JUL-2017','DD-MON-YYYY'));
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'T1', cascade => TRUE);
Display the current table partition and index information.
SET LINESIZE 120
-- Table subpartitions.
SELECT table_name,
partition_name,
subpartition_name,
num_rows
FROM user_tab_subpartitions
ORDER BY 1,2,3;
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
T1 YEAR_2016 YEAR_2016_OTHERS 1
T1 YEAR_2016 YEAR_2016_SMALL 3
T1 YEAR_2017 YEAR_2017_OTHERS 1
T1 YEAR_2017 YEAR_2017_SMALL 3
SQL>
-- Indexes.
SELECT index_name,
partitioned,
status
FROM user_indexes
ORDER BY 1;
INDEX_NAME PARTITIONED STATUS
------------------------------ ----------- --------
T1_CODE_IDX NO VALID
T1_CREATED_DATE_IDX YES N/A
T1_PK NO VALID
SQL>
-- Index subpartitions.
SELECT index_name,
partition_name,
subpartition_name,
status
FROM user_ind_subpartitions
ORDER BY 1,2,3;
INDEX_NAME PARTITION_NAME SUBPARTITION_NAME STATUS
------------------------------ ------------------------------ ------------------------------ --------
T1_CREATED_DATE_IDX YEAR_2016 YEAR_2016_OTHERS USABLE
T1_CREATED_DATE_IDX YEAR_2016 YEAR_2016_SMALL USABLE
T1_CREATED_DATE_IDX YEAR_2017 YEAR_2017_OTHERS USABLE
T1_CREATED_DATE_IDX YEAR_2017 YEAR_2017_SMALL USABLE
SQL>
Perform an online operation to split the "small" subpartition to create a new partition for rows with a CODE column with values 'TWO' and 'THREE'.
ALTER TABLE t1 SPLIT SUBPARTITION year_2016_small
VALUES ('TWO', 'THREE') INTO (
SUBPARTITION year_2016_two_three,
SUBPARTITION year_2016_small
)
ONLINE;
EXEC DBMS_STATS.gather_table_stats(USER, 'T1', cascade => TRUE);
Display the current status of the table subpartitions and indexes. Notice both the global and local indexes are left in a usable state without having to specify the UPDATE INDEXES clause.
-- Table subpartitions.
SELECT table_name,
partition_name,
subpartition_name,
num_rows
FROM user_tab_subpartitions
ORDER BY 1,2,3;
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
T1 YEAR_2016 YEAR_2016_OTHERS 1
T1 YEAR_2016 YEAR_2016_SMALL 1
T1 YEAR_2016 YEAR_2016_TWO_THREE 2
T1 YEAR_2017 YEAR_2017_OTHERS 1
T1 YEAR_2017 YEAR_2017_SMALL 3
SQL>
-- Indexes.
SELECT index_name,
partitioned,
status
FROM user_indexes
ORDER BY 1;
INDEX_NAME PARTITIONED STATUS
------------------------------ ----------- --------
T1_CODE_IDX NO VALID
T1_CREATED_DATE_IDX YES N/A
T1_PK NO VALID
SQL>
-- Index subpartitions.
SELECT index_name,
partition_name,
subpartition_name,
status
FROM user_ind_subpartitions
ORDER BY 1,2,3;
INDEX_NAME PARTITION_NAME SUBPARTITION_NAME STATUS
------------------------------ ------------------------------ ------------------------------ --------
T1_CREATED_DATE_IDX YEAR_2016 YEAR_2016_OTHERS USABLE
T1_CREATED_DATE_IDX YEAR_2016 YEAR_2016_SMALL USABLE
T1_CREATED_DATE_IDX YEAR_2016 YEAR_2016_TWO_THREE USABLE
T1_CREATED_DATE_IDX YEAR_2017 YEAR_2017_OTHERS USABLE
T1_CREATED_DATE_IDX YEAR_2017 YEAR_2017_SMALL USABLE
SQL>
For a comparison, perform an offline operation to split the subpartition for the second partition.
ALTER TABLE t1 SPLIT SUBPARTITION year_2017_small
VALUES ('TWO', 'THREE') INTO (
SUBPARTITION year_2017_two_three,
SUBPARTITION year_2017_small
);
EXEC DBMS_STATS.gather_table_stats(USER, 'T1', cascade => TRUE);
Display the current status of the table subpartitions and indexes. Notice both the global and local indexes on the affected subpartitions are left in a unusable state because we didn't specify the UPDATE INDEXES clause or ONLINE keyword.
-- Table subpartitions.
SELECT table_name,
partition_name,
subpartition_name,
num_rows
FROM user_tab_subpartitions
ORDER BY 1,2,3;
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME NUM_ROWS
------------------------------ ------------------------------ ------------------------------ ----------
T1 YEAR_2016 YEAR_2016_OTHERS 1
T1 YEAR_2016 YEAR_2016_SMALL 1
T1 YEAR_2016 YEAR_2016_TWO_THREE 2
T1 YEAR_2017 YEAR_2017_OTHERS 1
T1 YEAR_2017 YEAR_2017_SMALL 1
T1 YEAR_2017 YEAR_2017_TWO_THREE 2
SQL>
-- Indexes.
SELECT index_name,
partitioned,
status
FROM user_indexes
ORDER BY 1;
INDEX_NAME PARTITIONED STATUS
------------------------------ ----------- --------
T1_CODE_IDX NO UNUSABLE
T1_CREATED_DATE_IDX YES N/A
T1_PK NO UNUSABLE
SQL>
-- Index subpartitions.
SELECT index_name,
partition_name,
subpartition_name,
status
FROM user_ind_subpartitions
ORDER BY 1,2,3;
INDEX_NAME PARTITION_NAME SUBPARTITION_NAME STATUS
------------------------------ ------------------------------ ------------------------------ --------
T1_CREATED_DATE_IDX YEAR_2016 YEAR_2016_OTHERS USABLE
T1_CREATED_DATE_IDX YEAR_2016 YEAR_2016_SMALL USABLE
T1_CREATED_DATE_IDX YEAR_2016 YEAR_2016_TWO_THREE USABLE
T1_CREATED_DATE_IDX YEAR_2017 YEAR_2017_OTHERS USABLE
T1_CREATED_DATE_IDX YEAR_2017 YEAR_2017_SMALL UNUSABLE
T1_CREATED_DATE_IDX YEAR_2017 YEAR_2017_TWO_THREE UNUSABLE
SQL>
For more information see:
- About Splitting Partitions and Subpartitions
- ALTER TABLE
- All Partitioning Articles
- Partitioning Enhancements in Oracle Database 12c Release 2 (12.2)
- Online Operation Enhancements in Oracle Database 12c Release 1 (12.1)
Hope this helps. Regards Tim...