分区是指将表、索引等数据库对象划分为较小的可管理片段的技术,每一个片段称为分区子表或分区索引。一个表被分区后,对表的查询操作可以局限于某个分区进行,而不是整个表,这样可以大大提高查询速度。
DM 支持对大表进行水平分区。例如,通讯公司将用户通话记录保存在一张表中,一年这个表产生 40GB 的数据。假设要对用户的通话信息按照季度进行统计,那么这样的统计需 要在全表范围内进行。如果对表按季度进行水平分区,那么每个分区的大小平均为 10GB 左右,这样在进行统计时,只需在 10GB 的范围内进行即可。
DM 采用子表方式创建分区表,分区表作为分区主表,而每一个分区以一个子表实体存在,即每一个分区都是一个完整的表,一般命名为主表名_分区名。对于水平分区,子表跟主表具有相同的逻辑结构,即分区子表与分区主表有相同的列定义和约束定义。在 DM 分区表中,主表本身不存储数据,所有数据只存储在子表中,从而实现不同分区的完全独立性。水平分区子表删除后,会将子表上的数据一起删除。
由于每一个分区都以一个子表作为实体,那么不同分区可以存储于相同表空间,也可以位于不同的表空间中。将这些分区放在不同的表空间中具有以下的好处:
1. 减少所有数据都损坏的可能性,一个表空间损坏不影响其他表空间,提高可用性;
2. 恢复时间大大减少;
3. 可以将同一个表中的数据分布在不同的磁盘上,从而均衡磁盘上的 I/O 操作;
4. 提高了表的可管理性、可利用性和访问效率。
分区操作对现存的应用和运行在分区表上的标准 DML 语句来说是透明的。但是,可以通过在 DML 中使用分区子表名字来对应用进行编程,使其充分利用分区的优点。
分区表的类型:水平分区
水平分区类型:范围分区,列表分区,哈希分区,间隔分区
目的:把一张超大表,逻辑拆分出多张小表,分散 i/o,方便管理,提高数据查询和 DML 操作的效率。
达梦数据库 DM 支持对表进行水平分区。对于水平分区,提供以下分区方式:
1. 范围(range)水平分区:对表中的某些列上值的范围进行分区,根据某个值的范围,决定将该数据存储在哪个分区上;
2. 哈希(hash)水平分区:通过指定分区编号来均匀分布数据的一种分区类型,通过在 I/O 设备上进行散列分区,使得这些分区大小基本一致;
3. 列表(list)水平分区:通过指定表中的某个列的离散值集,来确定应当存储在一起的数据。例如,可以对表上的 status 列的值在('A','H','O')放在一个分区,值在('B','I','P')放在另一个分区,以此类推;
4. 多级分区表:按上述三种分区方法进行任意组合,将表进行多次分区,称为多级分区表。
- 范围分区:
要求:分区列是数字或是日期类型
建表:
create tablespace TEST datafile 'D:\dameng\DM\data\DAMENG\test_01.dbf' size 512;
create user TEST identified by dameng123 default tablespace TEST;
CREATE
TABLE TEST.T1
(
ID INT,
NAME VARCHAR(20)
)
PARTITION BY RANGE
(
ID
)
(
PARTITION P1 VALUES LESS THAN(100),
PARTITION P2 VALUES LESS THAN(200),
PARTITION P3 VALUES LESS THAN(300)
);
插入数据:
begin
for i in 1..299
loop
insert into test.t1 values
(i, 'CCC'||i
) ;
commit;
end loop;
end;
查询全表数据及分区表第二个分区数据:
SELECT * FROM TEST.T1;
SELECT * FROM TEST.T1_P2 ; 或
SELECT * FROM TEST.T1 PARTITION (p2);
插入一行数据:(使其位于第三个分区的最大边界)
insert into test.t1 values (300,'CCC300');
新增分区:
alter table test.t1 add partition pn values less than(maxvalue);
- 列表分区
范围分区是按照某个列上的数据范围进行分区的,如果某个列上的数据无法通过划分范围的方法进行分区,并且该列上的数据是相对固定的一些值,可以考虑使用 LIST 分区。一般来说,对于数字型或者日期型的数据,适合采用范围分区的方法;而对于字符型数据,取值比较固定的,则适合于采用 LIST 分区的方法。
例如,创建一个产品销售记录表 sales,记录产品的销量情况。由于产品只在几个固
定的城市销售,所以可以按照销售城市对该表进行分区。
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
(
'广州',
'深圳'
)
);
在创建 LIST 分区时,通过“PARTITION BY LIST”子句指定对表进行 LIST 分区,
然后在每个分区中分区列的取值通过 VALUES 子句指定。当用户向表插入数据时,只要分
区列的数据与 VALUES 子句指定的数据之一相等,该行数据便会写入相应的分区子表中。
注意的是,LIST 分区的分区键必须唯一。
alter table sales add partition pn values(default);
- 哈希分区
在很多情况下,用户无法预测某个列上的数据变化范围,因而无法实现创建固定数量的范围分区或 LIST 分区。
在这种情况下,DM 哈希分区提供了一种在指定数量的分区中均等地划分数据的方法,
基于分区键的散列值将行映射到分区中。当用户向表中写入数据时,数据库服务器将根据一个哈希函数对数据进行计算,把数据均匀地分布在各个分区中。在哈希分区中,用户无法预测数据将被写入哪个分区中。
现在重新考虑产品销售表的例子。如果销售城市不是相对固定的,而是遍布全国各地,
这时很难对表进行 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
);
将分区列的值进行 hash 运算,然后将数据平均分配各个子分区,存储数据非常快,取数据慢,如果 hash 分区不指定分区表名,那就通过指定的哈希分区数来创建。
分区表名统一使用 DMHASHPART+分区号(从 0 开始)作为分区名。
CREATE
TABLE sales02
(
sales_id INT ,
saleman CHAR(20),
saledate DATETIME,
city CHAR(10)
)
PARTITION BY HASH
(
city
)
PARTITIONS 4 STORE IN
(
tbs1,
tbs2,
tbs3,
tbs4
);
PARTITIONS 后的数字表示哈希分区的分区数,STORE IN 子句中指定了哈希分区依次使用的表空间。使用这种方式建立的哈希分区表分区名是匿名的,DM 统一使用DMHASHPART+分区号(从 0 开始)作为分区名。例如,需要查询 sales 第一个分区的数据,可执行以下语句:
SELECT * FROM sales02 PARTITION (dmhashpart0);
- 多级分区表
在很多情况下,经过一次分区并不能精确地对数据进分类,这时需要多级分区表。
例如,创建一个产品销售记录表 sales,记录产品的销量情况。由于产品需要按地点
和销售时间进行统计,则可以对该表进行 LIST-RANGE 分区。
DROP TABLE SALES;
CREATE
TABLE SALES
(
SALES_ID INT ,
SALEMAN CHAR(20),
SALEDATE DATETIME,
CITY CHAR(10)
)
PARTITION BY LIST -----主分区
(
CITY
)
SUBPARTITION BY RANGE ------子分区
(
SALEDATE
)
SUBPARTITION TEMPLATE ------子分区模板
(
SUBPARTITION P11 VALUES LESS THAN ('2012-04-01'),
SUBPARTITION P12 VALUES LESS THAN ('2012-07-01'),
SUBPARTITION P13 VALUES LESS THAN ('2012-10-01'),
SUBPARTITION P14 VALUES EQU
OR LESS THAN (MAXVALUE)
)
(
PARTITION P1 VALUES -----创建多级分区
(
'北京',
'天津'
)
( SUBPARTITION P11_1 VALUES LESS THAN ('2012-10-01'), SUBPARTITION P11_2 VALUES EQU
OR LESS THAN (MAXVALUE) ),
PARTITION P2 VALUES
(
'上海',
'南京',
'杭州'
)
,
PARTITION P3 VALUES
(
DEFAULT
)
);
在创建多级分区表时,指定了子分区模板,同时子分区 P1 自定义了子分区描述 P11_1和 P11_2。P1 有两个子分区 P11_1 和 P11_2。而子分区 P2 和 P3 有四个子分区 P11、P12、P13 和 P14。
DM 支持最多八层多级分区。
下面给出一个三级分区的例子,更多级别的分区表的建表语句语法类推。
CREATE
TABLE STUDENT
(
NAME VARCHAR(20) ,
AGE INT ,
SEX VARCHAR(10) CHECK (SEX IN ('MAIL', 'FEMAIL')),
GRADE INT CHECK (GRADE IN (7, 8, 9))
)
PARTITION BY LIST
(
GRADE
)
SUBPARTITION BY LIST
(
SEX
)
SUBPARTITION TEMPLATE
(
SUBPARTITION Q1 VALUES
(
'MAIL'
)
,
SUBPARTITION Q2 VALUES
(
'FEMAIL'
)
)
,
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
)
);