Moving Data by Changing Partitioning Key Value

Description

If a partition key value is changed, the changed instance can be moved to another partition by the partition expression.

Example

The following is an example of moving the instance to another partition by changing the partition key value.

If you change the sport event information of Hwang Young-Cho in the event1 partition from Athletics to Football, the instance is moved to the event3 partition.

CREATE TABLE athlete2( name VARCHAR(40), event VARCHAR(30) )

PARTITION BY LIST (event) (

PARTITION event1 VALUES IN ('Swimming', 'Athletics ' ),

PARTITION event2 VALUES IN ('Judo', 'Taekwondo','Boxing'),

PARTITION event3 VALUES IN ('Football', 'Basketball', 'Baseball')

);

INSERT INTO athlete2 VALUES ('Hwang Young-Cho', 'Athletics');

INSERT INTO athlete2 VALUES ('Lee Seung-Yuop', 'Baseball');

csql> SELECT * FROM athlete2__p__event1;

csql> ;x

=== <Result of SELECT Command in Line 1> ===

  name                  event

============================================

  'Hwang Young-Cho'     'Athletics'

1 rows selected.

csql> UPDATE athlete2 SET event = 'Football' WHERE name = 'Hwang Young-Cho';

csql> ;x

1 rows affected.

csql> SELECT * FROM athlete2__p__event3;

csql> ;x

=== <Result of SELECT Command in Line 1> ===

  name                  event

============================================

  'Lee Seung-Yuop'      'Baseball'

  'Hwang Young-Cho'     'Football'

2 rows selected.

Caution

• Be aware that when moving data between partitions by changing a partition key value, it can cause performance degradation due to internal deletions and insertions.