达梦数据库系列—36.分区表

目录

1、分区表的分类

1.1 范围分区表

1.2 List分区表

1.3  哈希分区表

1.4 多级分区表

二级分区

三级分区

2、分区表的维护

2.1 增加分区

2.2 删除分区

2.3 交换分区

2.4 融合分区

3、全局索引和局部索引

---

1、分区表的分类

范围(range)水平分区：对表中的某些列上值的范围进行分区，根据某个值的范围，决定将该数据存储在哪个分区上；

哈希（hash）水平分区：通过指定分区编号来均匀分布数据的一种分区类型，通过在 I/O 设备上进行散列分区，使得这些分区大小基本一致；

列表（list）水平分区：通过指定表中的某个列的离散值集，来确定应当存储在一起的数据。例如，可以对表上的 status 列的值在('A'，'H'，'O')放在一个分区，值在('B'，'I'，'P')放在另一个分区，以此类推；

多级分区表：按上述三种分区方法进行任意组合，将表进行多次分区，称为多级分区表。

1.1 范围分区表

例：

    CREATE  TABLE  callinfo(

    caller CHAR(15),

    callee CHAR(15),

    time DATETIME,

    duration INT

    )

    PARTITION BY RANGE(time)(

    PARTITION p1 VALUES LESS THAN ('2010-04-01'),

    PARTITION p2 VALUES LESS THAN ('2010-07-01'),

    PARTITION p3 VALUES LESS THAN ('2010-10-01'),

    PARTITION p4 VALUES EQU OR LESS THAN ('2010-12-31')  //'2010-12-31'也可替换为MAXVALUE

    );

1.2 List分区表

例：

    CREATE  TABLE  sales(

    sales_id INT,

    saleman CHAR(20),

    saledate DATETIME,

    city CHAR(10)

    )

    PARTITION BY LIST(city)(

    PARTITION p1 VALUES ('北京', '天津'),

    PARTITION p2 VALUES ('上海', '南京', '杭州'),

    PARTITION p3 VALUES ('武汉', '长沙'),

    PARTITION p4 VALUES ('广州', '深圳')

    );

1.3  哈希分区表

在很多情况下，用户无法预测某个列上的数据变化范围，因而无法实现创建固定数量的范围分区或 LIST 分区。

基于分区键的散列值将行映射到分区中。当用户向表中写入数据时，数据库服务器将根据一个哈希函数对数据进行计算，把数据均匀地分布在各个分区中。

例：

    CREATE  TABLE  sales01(

    sales_id INT,

    saleman CHAR(20),

    saledate DATETIME,

    city CHAR(10)

    )

    PARTITION BY HASH(city)(

    PARTITION p1,

    PARTITION p2,

    PARTITION p3,

    PARTITION p4

    );

1.4 多级分区表

二级分区

例：

CREATE TABLE "SYSDBA"."SALES_NEW"

(

"SALES_ID" INT,

"SALEMAN" CHAR(20),

"SALEDATE" DATETIME(6),

"CITY" CHAR(10))

PARTITION BY LIST("CITY")

SUBPARTITION BY RANGE("SALEDATE") SUBPARTITION TEMPLATE

(

SUBPARTITION  "P2021"  VALUES LESS THAN(DATETIME'2022-01-01 00:00:00'),

SUBPARTITION  "P2022"  VALUES LESS THAN(DATETIME'2023-01-01 00:00:00'),

SUBPARTITION  "P2023"  VALUES LESS THAN(DATETIME'2024-01-01 00:00:00'),

SUBPARTITION  "P2024"  VALUES LESS THAN(DATETIME'2025-01-01 00:00:00'),

SUBPARTITION  "P2025"  VALUES LESS THAN(DATETIME'2026-01-01 00:00:00'),

SUBPARTITION  "P2026"  VALUES LESS THAN(DATETIME'2027-01-01 00:00:00'),

SUBPARTITION  "P2027"  VALUES LESS THAN(DATETIME'2028-01-01 00:00:00'),

SUBPARTITION  "P2028"  VALUES LESS THAN(DATETIME'2029-01-01 00:00:00'),

SUBPARTITION  "P2029"  VALUES LESS THAN(DATETIME'2030-01-01 00:00:00'),

SUBPARTITION  "P2030"  VALUES LESS THAN(DATETIME'2031-01-01 00:00:00'),

SUBPARTITION  "P2099"  VALUES EQU OR LESS THAN(MAXVALUE)

)

(

PARTITION  "P0310"  VALUES('0310      ')   STORAGE(ON "TBS_DATA_1", CLUSTERBTR) ,

PARTITION  "P0311"  VALUES('0311      ')   STORAGE(ON "TBS_DATA_2", CLUSTERBTR) ,

PARTITION  "P0312"  VALUES('0312      ')   STORAGE(ON "TBS_DATA_3", CLUSTERBTR) ,

PARTITION  "P0313"  VALUES('0313      ')   STORAGE(ON "TBS_DATA_4", CLUSTERBTR) ,

PARTITION  "P0314"  VALUES('0314      ')   STORAGE(ON "TBS_DATA_5", CLUSTERBTR) ,

PARTITION  "P0315"  VALUES('0315      ')   STORAGE(ON "TBS_DATA_6", CLUSTERBTR) ,

PARTITION  "P0316"  VALUES('0316      ')   STORAGE(ON "TBS_DATA_7", CLUSTERBTR) ,

PARTITION  "P0317"  VALUES('0317      ')   STORAGE(ON "TBS_DATA_8", CLUSTERBTR) ,

PARTITION  "P0318"  VALUES('0318      ')   STORAGE(ON "TBS_DATA_9", CLUSTERBTR) ,

PARTITION  "P0319"  VALUES('0319      ')   STORAGE(ON "TBS_DATA_10", CLUSTERBTR) ,

PARTITION  "P0335"  VALUES('0335      ')   STORAGE(ON "TBS_DATA_11", CLUSTERBTR) ,

PARTITION  "P9999"  VALUES(DEFAULT)   STORAGE(ON "TBS_DATA_12", CLUSTERBTR)

) STORAGE(HASHPARTMAP(1), ON "TBS_DATA_13", CLUSTERBTR) ;

三级分区

例：

CREATE TABLE STUDENT(

NAME VARCHAR(20),

AGE INT,

SEX VARCHAR(10) CHECK (SEX IN ('MAN','FEMAN')),

GRADE INT CHECK (GRADE IN (7,8,9))

)

PARTITION BY LIST(GRADE)

SUBPARTITION BY LIST(SEX) SUBPARTITION TEMPLATE

(

SUBPARTITION Q1 VALUES('MAN'),

SUBPARTITION Q2 VALUES('FEMAN')

),

SUBPARTITION BY RANGE(AGE) SUBPARTITION TEMPLATE

(

SUBPARTITION R1 VALUES LESS THAN (12),

SUBPARTITION R2 VALUES LESS THAN (15),

SUBPARTITION R3 VALUES LESS THAN (MAXVALUE)

)

(

PARTITION P1 VALUES (7),

PARTITION P2 VALUES (8),

PARTITION P3 VALUES (9)

);

2、分区表的维护

2.1 增加分区

--范围分区表

  ALTER TABLE callinfo

  ADD PARTITION p5 VALUES LESS THAN ('2011-4-1') STORAGE (ON ts5);

--List分区表

  ALTER TABLE sales

  ADD PARTITION p5 VALUES ('拉萨', '呼和浩特') STORAGE (ON ts5);

--hash分区表

ALTER TABLE SYSDBA.HASH_TEST ADD PARTITION P4;

2.2 删除分区

  ALTER TABLE callinfo DROP PARTITION p1;

注：hash分区不支持删分区

2.3 交换分区

DM 支持用 ALTER TABLE EXCHANGE PARTITION 语句将分区数据跟普通表数据进行交换，交换的两张表需要具有相同的结构。仅范围分区和 LIST 分区支持交换分区。

CREATE  TABLE  callinfo_2011Q2(

caller CHAR(15),

callee CHAR(15),

time DATETIME,

duration INT

);

//交换分区

ALTER TABLE callinfo EXCHANGE PARTITION p2 WITH TABLE callinfo_2011Q2;

//删除原分区

ALTER TABLE callinfo DROP PARTITION p2;

//新增分区，记录2011年第二季度通话记录

ALTER TABLE callinfo

ADD PARTITION p6 VALUES LESS THAN ('2011-7-1') STORAGE (ON ts2);

2.4 融合分区

例：

  ALTER TABLE callinfo MERGE PARTITIONS p3, p4 into partition p3_4;

哈希分区表不支持。

拆分分区

  ALTER TABLE callinfo SPLIT PARTITION p3_4 AT ('2010-9-30') INTO (PARTITION p3, PARTITION p4);

3、全局索引和局部索引

全局索引：全局索引是以整张表的数据为对象而建立的索引

局部索引：局部索引是在分区表的每个分区上创建的索引

全局索引：带GLOBAL 关键字，否则创建局部索引。

//首先，创建表t1

create table t1(c1 int, c2 int, c3 int)  partition by range(c1)

(

partition p1 values less than(100),

partition p2 values less than(200),

PARTITION p3 VALUES EQU OR LESS THAN (MAXVALUE)

);

//其次，指定GLOBAL创建全局索引idx1

create index idx1 on t1(c2) GLOBAL;

//再次，缺省GLOBAL创建局部索引idx2

create index idx2 on t1(c3);

创建分区表后，每个分区会自动创建一个聚簇索引

注：如果表中指定了PK，默认创建全局索引，若没有指定PK，创建局部索引(PARTITIONED)

表上创建全局索引，每个分区会创建一个Normal索引

表上创建局部索引，每个分区也会创建一个Normal索引

、

查询表的某个分区(T1_P2)，走局部索引。

达梦技术社区：达梦数据库 - 新一代大型通用关系型数据库 | 达梦在线服务平台