达梦创建分区表

分区表的目的：逻辑上把一张大表分成若干个小表，从而分散IO，方便管理。

类型：水平分区（范围、哈希、列表、组合/多级）、垂直分区。

Oracle支持范围分区、哈希分区、列表分区、参考分区、系统分区、组合分区等十几种分区策略。

 

可以给每个分区指定不同的表空间，实现分散IO的目的。

 

范围分区表（一般按照数字、日期来分区）

创建范围分区表：

CREATE TABLE R_T (A INT,B VARCHAR)

PARTITION BY RANGE(A)

(

 PARTITION P1 VALUES LESS THAN (10) TABLESPACE TBS1,

 PARTITION P2 VALUES LESS THAN (20) STORAGE(ON TBS2),

 PARTITION P3 VALUES LESS THAN (30) TABLESPACE TBS3

);

此时会报警告：范围分区未包含MAXVALUE,可能无法定位到分区

不是报错，只是提醒插入数据时可能会出现找不到合适的分区的报错。

若不想出现该警告，可以增加MAXVALUE分区：

ALTER TABLE R_T ADD PARTITION P6 VALUES LESS THAN (MAXVALUE);

若有最大值分区之后，后面的新增分区操作无法成功，因为新增分区值必须是递增的，若想多划分几个分区，可以用拆分分区来实现。

 

查看某张表是否为分区表：

SELECT TABLE_NAME,PARTITIONED FROM DBA_TABLES WHERE TABLE_NAME = 'R_T';

 

查看分区表详细信息：

SELECT * FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME = 'R_T';

 

查看分区表的分区字段：

SELECT * FROM DBA_PART_KEY_COLUMNS;

 

查看分区表及该表的分区类型，以及是否有子分区，子分区类型：

SELECT * FROM DBA_PART_TABLES;

 

新增分区：

ALTER TABLE R_T ADD PARTITION P4 VALUES LESS THAN (40);

新增分区时值必须递增，否则会报错：范围分区值非递增

 

合并分区：

ALTER TABLE R_T MERGE PARTITIONS P1,P2 INTO PARTITION P1_2;

 

拆分分区（记得放到不同的表空间里）：

ALTER TABLE R_T SPLIT PARTITION P1_2 AT (10) INTO (PARTITION P1,PARTITION P2);

测试时指定了不同的表空间，但是去查询DBA_TAB_PARTITIONS时，发现拆分之后的分区并没有按照指定的那样存放在不同的表空间，可能是bug。

 

删除分区（删除分区时，会连同分区里的数据一起删掉）：

ALTER TABLE R_T DROP PARTITION P4;

 

查看单个分区中的数据：

SELECT * FROM R_T PARTITION (P1);

或直接：

select * from "R_T_P4";

交换分区：

CREATE TABLE T(A INT,B VARCHAR);

INSERT INTO T VALUES (1,'HAHA');

COMMIT;

ALTER TABLE R_T EXCHANGE PARTITION P1 WITH TABLE T;

注意，交换分区时，普通表和分区表的结构必须是一样的，包括索引、约束等。

还需要注意的是，交换分区时，不会检查数据是否满足分区划分的要求，所以需要用户自己保证数据交换进正确的分区了。

 

清空某个分区数据：

ALTER TABLE R_T TRUNCATE PARTITION P2;

 

分区改名：

ALTER TABLE R_T RENAME PARTITION P2 TO P22;

 

范围分区常见限制：

1. 当创建普通范围分区表，且该表有主键时，若主键不包含分区列，则会报错

CREATE TABLE R_T1 (A INT,B VARCHAR PRIMARY KEY)

PARTITION BY RANGE(A)

(

 PARTITION P1 VALUES LESS THAN (10) TABLESPACE TBS1,

 PARTITION P2 VALUES LESS THAN (20) STORAGE(ON TBS2),

 PARTITION P3 VALUES LESS THAN (30) TABLESPACE TBS3

);

错误[-2683]:局部唯一索引必须包含全部分区列

2. 上述报错，创建水平分区堆表没有此限制，即分区表为堆表时，分区列不需要是主键列

CREATE TABLE R_T2 (A INT,B VARCHAR PRIMARY KEY)

PARTITION BY RANGE(A)

(

 PARTITION P1 VALUES LESS THAN (10),

 PARTITION P2 VALUES LESS THAN (20),

 PARTITION P3 VALUES LESS THAN (30)

) STORAGE(NOBRANCH);

但是水平分区堆表有一个限制，就是不能将分区放在不同的表空间上

CREATE TABLE R_T3 (A INT,B VARCHAR PRIMARY KEY)

PARTITION BY RANGE(A)

(

 PARTITION P1 VALUES LESS THAN (10) TABLESPACE TBS1,

 PARTITION P2 VALUES LESS THAN (20) STORAGE(ON TBS2),

 PARTITION P3 VALUES LESS THAN (30) TABLESPACE TBS3

) STORAGE(NOBRANCH);

错误[-2757]:水平分区堆表各子表必须位于同一个表空间

 

哈希分区

把数据平均分配到各个子分区。

创建哈希分区：有两种方式，一是自己指定分区名；二是由系统默认生成分区名。

自己指定分区名，这种方式可以将分区分散在不同的表空间：

CREATE TABLE H_T (A INT,B VARCHAR)

PARTITION BY HASH(B)

(

 PARTITION P1 TABLESPACE TBS1,

 PARTITION P2 TABLESPACE TBS2,

 PARTITION P3 TABLESPACE TBS3

);

系统默认生成分区名，这种方式不能分散表空间：

CREATE TABLE H_T1(A INT,B VARCHAR)

PARTITION BY HASH(B) PARTITIONS 3;

 

哈希分区不支持新增、删除、合并、拆分分区操作。

哈希分区存数据性能比较好，但是读数据性能不高。

 

列表分区

创建列表分区：

CREATE TABLE L_T(A INT,B VARCHAR)

PARTITION BY LIST(B)

(

 PARTITION P1 VALUES ('武汉'),

 PARTITION P2 VALUES ('襄阳')

);

 

列表分区支持新增、删除分区，以及给分区改名，但不支持合并、拆分分区。

ALTER TABLE L_T ADD PARTITION P3 VALUES ('黄冈');

ALTER TABLE L_T DROP PARTITION P3;

 

列表分区设置默认值（即不在划分的列表内的默认分区）：

CREATE TABLE L_T1(A INT,B VARCHAR)

PARTITION BY LIST(B)

(

 PARTITION P1 VALUES ('武汉'),

 PARTITION P2 VALUES ('襄阳'),

 PARTITION P3 VALUES (DEFAULT)

);

 

多级分区

以LIST-RANGE为例：

可以指定子分区模板：

CREATE TABLE M_T(A INT,B VARCHAR)

PARTITION BY LIST(B)

SUBPARTITION BY RANGE(A)

SUBPARTITION TEMPLATE

(

 SUBPARTITION SUB1 VALUES LESS THAN (10),

 SUBPARTITION SUB2 VALUES LESS THAN (20)

)

(

 PARTITION P1 VALUES ('湖北'),

 PARTITION P2 VALUES ('湖南')

);

也可以在存在子分区模板的情况下，单独为某个主分区配置子分区：

CREATE TABLE M_T2(A INT,B VARCHAR)

PARTITION BY LIST(B)

SUBPARTITION BY RANGE(A)

SUBPARTITION TEMPLATE

(

 SUBPARTITION SUB1 VALUES LESS THAN (10),

 SUBPARTITION SUB2 VALUES LESS THAN (20)

)

(

 PARTITION P1 VALUES ('湖北'),

 PARTITION P2 VALUES ('湖南')

 (

   SUBPARTITION P2_1 VALUES LESS THAN (100),

   SUBPARTITION P2_2 VALUES LESS THAN (200)

  )

);

问题：多级分区查询DBA_TAB_PARTITIONS时只能看到第一层分区的信息，下面的子分区信息如何查询？

答：SELECT * FROM ALL_SUBPART_KEY_COLUMNS;

 

三级分区表示例：

CREATE TABLE M_T3(A INT,B VARCHAR,C INT)

PARTITION BY RANGE(A)

SUBPARTITION BY LIST(B)

SUBPARTITION TEMPLATE

(

 SUBPARTITION SP1_1 VALUES ('湖北'),

 SUBPARTITION SP1_2 VALUES ('湖南')

),

SUBPARTITION BY RANGE(C)

SUBPARTITION TEMPLATE

(

 SUBPARTITION SP2_1 VALUES LESS THAN (100),

 SUBPARTITION SP2_2 VALUES LESS THAN (200)

)

(

 PARTITION P1 VALUES LESS THAN (10),

 PARTITION P2 VALUES LESS THAN (20)

 (

  SUBPARTITION P2_1 VALUES ('北京')

  (

   SUBPARTITION P2_1_1 VALUES LESS THAN (1024),

   SUBPARTITION P2_1_2 VALUES LESS THAN (2048)

  ),

  SUBPARTITION P2_2 VALUES ('上海')

 )

);

 

间隔分区

当分区列类型为数值类型时：

CREATE TABLE R_I_T(A INT,B VARCHAR)

PARTITION BY RANGE(A)

INTERVAL (10)

(

 PARTITION P1 VALUES LESS THAN (10),

 PARTITION P2 VALUES LESS THAN (20)

);

插入数据不在现有分区段内：

INSERT INTO R_I_T VALUES (22,'HAHA');

COMMIT;

查看子分区：

SELECT * FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME = 'R_I_T';

 

当分区列类型为日期类型时：

CREATE TABLE R_I_T1(A DATETIME,B VARCHAR)

PARTITION BY RANGE(A) 

INTERVAL(NUMTOYMINTERVAL(1,'MONTH'))

(

 PARTITION P1 VALUES LESS THAN ('2020-01-01'),

 PARTITION P2 VALUES LESS THAN ('2020-02-01')

);

 

插入数据不在现有分区段内：

INSERT INTO R_I_T1 VALUES ('2020-03-19','HAHA');

COMMIT;

 

查看子分区：

SELECT * FROM DBA_TAB_PARTITIONS WHERE TABLE_NAME = 'R_I_T1';

 

达梦中，间隔分区表，若分区类型为日期，间隔类型只能取NUMTOYMINTERVAL，可以以年或月为间隔。

Oracle中间隔分区表，若分区类型为日期，间隔类型可以取NUMTOYMINTERVAL或NUMTODSINTERVAL，可以以年、月、周、日、时为间隔。

 

普通表转换成分区表，达梦可以采取导出导入(dexp/dimp)的方式。

Oracle可以使用导出导入的方式，也可以使用DBMS_REDIFINITION包方法进行转换。