Range Partitioning Definition

Description

You can define a range partition by using the PARTITION BY RANGE clause.

Syntax

CREATE TABLE(

...

)

PARTITION BY RANGE ( <partition_expression> ) (

PARTITION <partition_name> VALUES LESS THAN ( <range_value> ),

PARTITION <partition_name> VALUES LESS THAN ( <range_value> ) ),

... )

)

Example 1

The following example shows how to create the participant2 table with the participating countries, and insert data that partitions the years into before and after the 2000 Olympic Games. When inserting data, the countries that participated in the 1988 and 1996 Olympic Games are stored in before_2000; the rest of them are stored in before_2008.

CREATE TABLE participant2 (host_year INT, nation CHAR(3), gold INT, silver INT, bronze INT)

PARTITION BY RANGE (host_year)

(PARTITION before_2000 VALUES LESS THAN (2000),

PARTITION before_2008 VALUES LESS THAN (2008) );

 

INSERT INTO participant2 VALUES (1988, 'NZL', 3, 2, 8);

INSERT INTO participant2 VALUES (1988, 'CAN', 3, 2, 5);

INSERT INTO participant2 VALUES (1996, 'KOR', 7, 15, 5);

INSERT INTO participant2 VALUES (2000, 'RUS', 32, 28, 28);

INSERT INTO participant2 VALUES (2004, 'JPN', 16, 9, 12);

Example 2

As shown below, the partition key value in a range partition is NULL, the data are stored in the first partition.

INSERT INTO participant2 VALUES(NULL, 'AAA', 0, 0, 0);

Caution