Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

Versions available for this page: CUBRID 8.3.0 |  CUBRID 8.3.1 |  CUBRID 8.4.0 |  CUBRID 8.4.1 |  CUBRID 8.4.3 |  CUBRID 9.0.0 | 

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> ) ),

... )

)

  • partition_expression : Specifies the partition expression. The expression can be specified by the name of the column to be partitioned or by a function. For more information of the data types and functions available, see Data Types Available for Partition Expression.
  • partition_name : Specifies the partition name.
  • range_value : Specifies the partition-by value.
Example 1

The following is an example of creating the participant2 table with the participating countries, and inserting 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
  • The maximum number of partitions possible for a given table is 1024.
  • If the partition key value is NULL, the data is stored in the first partition (see Example 2).