Retrieving and Manipulating Data in Partitioning

Description

When retrieving data, the SELECT statement can be used not only for partitioned tables but also for each partition.

Example

The following is an example of creating the athlete2 table to be partitioned by the list of sport events, inserting data, and retrieving the event1 and event2 partitions.

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');

INSERT INTO athlete2 VALUES ('Moon Dae-Sung','Taekwondo');

INSERT INTO athlete2 VALUES ('Cho In-Chul', 'Judo');

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> select * from athlete2__p__event2;

csql> ;x

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

  name                  event

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

  'Moon Dae-Sung'       'Taekwondo'

  'Cho In-Chul'         'Judo'

2 rows selected.

Caution

• Data manipulation such as insert, update and delete for each partition of the partitioned table is not allowed.