Partition maintenance on multiple partitions Oracle Database 12C release 1 (12.1)

Posted on by

This article presents enhancements for maintenance operations on multiple partitions in Oracle 12C.

Following multiple operations using just one single command are now possible in Oracle 12C:

  • ADD multiple partitions and subpartitions
  • DROP multiple partitions and subpartitions
  • MERGE multiple partitions and subpartitions
  • TRUNCATE multiple partitions

This feature is beautiful extension I really love it 🙂

ADD PARTITION

ADD PARTITION for multiple partitions are supported for

  • RANGE
  • SYSTEM
  • LIST
  • Composite RANGE-*
  • Composite LIST-*
ALTER TABLE table_name
ADD 
  PARTITION partition_name_1 ...,
  ...,
  PARTITION partition_name_n ...;

Example RANGE

create table tbl_range
(id number)
partition by range(id)
(
  partition p1 values less than(10)
);

alter table tbl_range
add 
  partition p2 values less than(20),
  partition p3 values less than(30),
  partition p4 values less than(40);

Example SYSTEM

create table tbl_system
(id number) 
partition by system
( 
  partition p1,
  partition p2,
  partition p_last
);

alter table tbl_system
add 
  partition p4,
  partition p5,
  partition p6
  before partition p_last;

Example RANGE-RANGE

create table tbl_range_range
(id number,
id1 number)
partition by range(id)
subpartition by range(id1)
(
  partition p1 values less than(10)
  ( 
    subpartition s1 values less than(10) 
  )
);

alter table tbl_range_range
add 
  partition p2 values less than(20),
  partition p3 values less than(30),
  partition p4 values less than(40);

ADD SUBPARTITION

ADD SUBPARTITION for multiple partitions are supported for

  • Composite *-RANGE
  • Composite *-LIST
ALTER TABLE table_name
MODIFY PARTITION partition_name
ADD
  SUBPARTITION subpartition_name_1 ...,
  ...
  SUBPARTITION subpartition_name_n ...;

Example HASH-RANGE

create table tbl_hash_range
(id number,
 id1 number)
partition by hash(id)
subpartition by range(id1)
(
  partition p1
  ( 
    subpartition s1 values less than(10) 
  )
);

alter table tbl_hash_range
modify partition p1 
add 
  subpartition s2 values less than(20),
  subpartition s3 values less than(30),
  subpartition s4 values less than(40);

Example LIST-LIST

create table tbl_list_list
(id number,
 id1 number)
partition by list(id)
subpartition by list(id1)
(
  partition p1 values(10)
  ( 
    subpartition s1 values(10) 
  )
);

alter table tbl_list_list
modify partition p1 
add 
  subpartition s2 values(20),
  subpartition s3 values(30),
  subpartition s4 values(40);

DROP PARTITION

DROP PARTITION for multiple partitions are supported for

  • RANGE
  • SYSTEM
  • LIST
  • Composite RANGE-*
  • Composite LIST-*
  • Composite INTERVAL-*
ALTER TABLE table_name
DROP PARTITION 
  partition_name_1, 
  ...,
  partition_name_n;

Example for RANGE

create table tbl_range
(
  id number
)
partition by range(id)
(
  partition p1 values less than(10),
  partition p2 values less than(20),  
  partition p3 values less than(30),
  partition p4 values less than(40)
);

alter table tbl_range
drop partition p1, p2, p3;

DROP SUBPARTITION

DROP SUBPARTITION for multiple partitions are supported for

  • Composite *-RANGE
  • Composite *-LIST
ALTER TABLE table_name
DROP SUBPARTITION 
  subpartition_name_1,
  ...
  subpartition_name_n;

Example for RANGE_LIST

create table tbl_range_list
(
  id  number,
  id1 number
)
partition by range(id)
subpartition by list(id1)
(
  partition p1 values less than(10)
  (
    subpartition s1 values(10),
    subpartition s2 values(20),
    subpartition s3 values(30),
    subpartition s4 values(40)
  )
);

alter table tbl_range_list
drop subpartition s1, s2, s3;

MERGE PARTITIONS

MERGE PARTITIONS for multiple partitions are supported for

  • RANGE
  • SYSTEM
  • LIST
  • Composite RANGE-*
  • Composite LIST-*
  • Composite INTERVAL-*
ALTER TABLE table_name
MERGE PARTITIONS
  partition_name_1,
  ...,
  partition_name_n
INTO PARTITION
  partition_name_new;

or (it’s supported only for RANGE)

ALTER TABLE table_name
MERGE PARTITIONS
  partition_name_1 TO partition_name_n
INTO PARTITION
  partition_name_new;

Example for LIST

create table tbl_list
(
  id number
)
partition by list(id)
(
  partition p1 values(10),
  partition p2 values(20),
  partition p3 values(30),
  partition p4 values(40)
);

alter table tbl_list
merge partitions p1, p2, p3
into partition p1;

Example for RANGE-HASH

create table tbl_range_hash
(
  id  number,
  id1 number
)
partition by range(id)
subpartition by hash(id1)
(
  partition p1 values less than(10)
  (
    subpartition s1,
    subpartition s2,
    subpartition s3
  ),
  partition p2 values less than(20)
  (
    subpartition s4
  ),
  partition p3 values less than(30)
  (
    subpartition s5
  )
);

alter table tbl_range_hash
merge partitions p1 to p3
into partition p0;

MERGE SUBPARTITIONS

MERGE SUBPARTITIONS for multiple partitions are supported for

  • Composite *-RANGE
  • Composite *-LIST
ALTER TABLE table_name
MERGE SUBPARTITIONS
  subpartition_name_1,
  ...,
  subpartition_name_n
INTO SUBPARTITION
  subpartition_name_new;

or (it’s supported only for RANGE)

ALTER TABLE table_name
MERGE SUBPARTITIONS
  subpartition_name_1 TO subpartition_name_n
INTO SUBPARTITION
  subpartition_name_new;

example for RANGE-RANGE

create table tbl_range_range
(
  id  number,
  id1 number
)
partition by range(id)
subpartition by range(id1)
(
  partition p1 values less than(10)
  (
    subpartition s1 values less than(10),
    subpartition s2 values less than(20),
    subpartition s3 values less than(30)
  )
);

alter table tbl_range_range
merge subpartitions s1,s2,s3
into subpartition s0;

SPLIT PARTITION

SPLIT PARTITION for multiple partitions are supported for

  • RANGE
  • SYSTEM
  • LIST
  • Composite RANGE-*
  • Composite LIST-*
  • Composite INTERVAL-*
ALTER TABLE table_name
SPLIT PARTITION partition_name INTO
(
  PARTITION partition_p_1 ...,
  ...
  PARTITION partition_p_n
);

Example for RANGE

create table tbl_range
(id number)
partition by range(id)
(
  partition p0 values less than(30),
  partition p4 values less than(40)
);

alter table tbl_range
split partition p0 into
(
  partition p1 values less than(10),
  partition p2 values less than(20),
  partition p3
);

SPLIT SUBPARTITION

SPLIT SUBPARTITION for multiple partitions are supported for

  • Composite *-RANGE
  • Composite *-LIST
ALTER TABLE table_name 
SPLIT SUBPARTITION subpartition_name 
INTO 
( 
  SUBPARTITION subpartition_p_1 ..., 
  ... 
  SUBPARTITION subpartition_p_n
);

Example for LIST-RANGE

create table tbl_list_range
(id number,
 id1 number)
partition by list(id)
subpartition by range(id1)
(
  partition p1 values(10)
  ( 
    subpartition s4 values less than(40) 
  )
);

alter table tbl_list_range
split subpartition s4 into
(
  subpartition s1 values less than(10),
  subpartition s2 values less than(20),
  subpartition s3 values less than(30),
  subpartition s4
);

TRUNCATE PARTITION

TRUNCATE PARTITION for multiple partitions are supported for

  • RANGE
  • SYSTEM
  • LIST
  • HASH
  • INTERVAL
  • REFERENCE
  • Composite INTERVAL-*
  • Composite RANGE-*
  • Composite LIST-*
ALTER TABLE table_name 
TRUNCATE PARTITIONS
  partition_name_1,
  ...,
  partition_name_n;

Example for HASH

create table tbl_hash
(id number)
partition by hash(id)
(
  partition h1,
  partition h2,
  partition h3,
  partition h4
);

alter table tbl_hash
truncate partitions h1,h2,h3;

Example for LIST

create table tbl_list
(
  id  number,
  id1 number
)
partition by list(id)
(
  partition p1 values(10),
  partition p2 values(20),
  partition p3 values(30),
  partition p4 values(40)
);

alter table tbl_list
truncate partitions p1,p2,p3;

TRUNCATE SUBPARTITION

TRUNCATE SUBPARTITION for multiple partitions are supported for

  • Composite *-HASH
  • Composite *-RANGE
  • Composite *-LIST
ALTER TABLE table_name 
TRUNCATE SUBPARTITIONS
  partition_name_1,
  ...,
  partition_name_n;

Example for HASH-HASH

create table tbl_hash_hash
(
  id  number,
  id1 number
)
partition by hash(id)
subpartition by hash(id1)
(
  partition h1
  (
    subpartition s1,
    subpartition s2,
    subpartition s3,
    subpartition s4
  )
);

alter table tbl_hash_hash
truncate subpartitions s1,s2,s3;

Have a fun 🙂

Tomasz

 

 

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.