分区表的目的:逻辑上把一张大表分成若干个小表,从而分散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包方法进行转换。