AntDB用户手册——建表之创建分区表

AntDB在支持分片的同时，也支持分区表。

Oracle 语法下使用 Oracle 的创建语句：

/*ora*/
create table t_part_range 
(
    id number,
    in_date date,
    name VARCHAR2(30)
)
partition by range(in_date)
(
    partition t_part_range_part1 values less than (to_date('2016-01-01','yyyy-mm-dd')),
    partition t_part_range_part2 values less than (to_date('2017-01-01','yyyy-mm-dd')),
    partition t_part_range_part3 values less than (to_date('2018-01-01','yyyy-mm-dd')),
    partition t_part_range_part4 values less than (to_date('2019-01-01','yyyy-mm-dd')),
    partition t_part_range_partmax values less than (MAXVALUE)
)
distribute by hash(name);

查看分区表的详细信息：

antdb=> \d+ t_part_range
                                   Table "user1.t_part_range"
 Column  |     Type     | Collation | Nullable | Default | Storage  | Stats target | Description 
---------+--------------+-----------+----------+---------+----------+--------------+-------------
 id      | numeric      |           |          |         | main     |              | 
 in_date | oracle.date  |           |          |         | plain    |              | 
 name    | varchar2(30) |           |          |         | extended |              | 
Partition key: RANGE (in_date)
Partitions: t_part_range_part1 FOR VALUES FROM (MINVALUE) TO ('2016-01-01 00:00:00'),
            t_part_range_part2 FOR VALUES FROM ('2016-01-01 00:00:00') TO ('2017-01-01 00:00:00'),
            t_part_range_part3 FOR VALUES FROM ('2017-01-01 00:00:00') TO ('2018-01-01 00:00:00'),
            t_part_range_part4 FOR VALUES FROM ('2018-01-01 00:00:00') TO ('2019-01-01 00:00:00'),
            t_part_range_partmax FOR VALUES FROM ('2019-01-01 00:00:00') TO (MAXVALUE)
Distribute By: HASH(name)
Location Nodes: ALL DATANODES

上述分区表的创建方式是AntDB兼容了Oracle的语法，如果要使用AntDB原生的分区表创建，请使用如下语句：

antdb=> create table t_part_range_2
antdb-> (id int,name varchar(100),i_time timestamp not null) 
antdb-> partition by range(id);
CREATE TABLE
antdb=> create table t_range_1 partition of t_part_range_2 for values from (1) to (1000);
CREATE TABLE
antdb=> create table t_range_2 partition of t_part_range_2 for values from (1000) to (3000);
create table t_range_3 partition of t_part_range_2 for values from (3000) to (5000);
create table t_range_4 partition of t_part_range_2 for values from (5000) to (8000);
create table t_range_5 partition of t_part_range_2 for values from (8000) to (10000);CREATE TABLE
antdb=> create table t_range_3 partition of t_part_range_2 for values from (3000) to (5000);
CREATE TABLE
antdb=> create table t_range_4 partition of t_part_range_2 for values from (5000) to (8000);
CREATE TABLE
antdb=> create table t_range_5 partition of t_part_range_2 for values from (8000) to (10000);
CREATE TABLE

查看分区表的详细信息：

antdb=> \d+ t_part_range_2
                                         Table "user1.t_part_range_2"
 Column |            Type             | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
 id     | integer                     |           |          |         | plain    |              | 
 name   | character varying(100)      |           |          |         | extended |              | 
 i_time | timestamp without time zone |           | not null |         | plain    |              | 
Partition key: RANGE (id)
Partitions: t_range_1 FOR VALUES FROM (1) TO (1000),
            t_range_2 FOR VALUES FROM (1000) TO (3000),
            t_range_3 FOR VALUES FROM (3000) TO (5000),
            t_range_4 FOR VALUES FROM (5000) TO (8000),
            t_range_5 FOR VALUES FROM (8000) TO (10000)
Distribute By: HASH(id)
Location Nodes: ALL DATANODES

注意：与Oracle不同的是，AntDB中分区表的分区名与主表在同一个命令空间中，所以不同的分区表，不能使用同样的分区名。