创建RANGE分片表
CREATE TABLE employee
(
id INTEGER,
name CHAR(32),
basepay DECIMAL (10,2),
varpay DECIMAL (10,2),
dept CHAR(2),
hiredate DATE)
FRAGMENT BY RANGE (id)
INTERVAL (100) STORE IN (datadbs01)
PARTITION p0 VALUES IS NULL IN datadbs01,
PARTITION p1 VALUES < 200 IN datadbs01,
PARTITION p2 VALUES < 400 IN datadbs01;
插入数据
drop procedure IF EXISTS proc8;
create procedure proc8(p_in int) ;
define i int;
let i = 1;
while(i<=p_in) loop
insert into employee values(
i,
'aa',
3.14,
31.4,
'bb',
current
);
exit when i = p_in;
let i = i + 1;
end loop;
end procedure;
分片结果
#分片表达式
FRAGMENT BY RANGE (id)
INTERVAL (100) STORE IN (datadbs01)
PARTITION p0 VALUES IS NULL IN datadbs01,
PARTITION p1 VALUES < 200 IN datadbs01,
PARTITION p2 VALUES < 400 IN datadbs01;
#执行这样两次插入
> call proc8(10);
> call proc8(1000);
#结果如下:
gbasedbt[hdr2]$oncheck -pt db01:employee |grep partitio
Table fragment partition p0 in DBspace datadbs01
Table fragment partition p1 in DBspace datadbs01
Table fragment partition p2 in DBspace datadbs01
Table fragment partition sys_p3 in DBspace datadbs01
Table fragment partition sys_p4 in DBspace datadbs01
Table fragment partition sys_p5 in DBspace datadbs01
Table fragment partition sys_p6 in DBspace datadbs01
Table fragment partition sys_p7 in DBspace datadbs01
Table fragment partition sys_p8 in DBspace datadbs01
Table fragment partition sys_p9 in DBspace datadbs01
gbasedbt[hdr2]$
gbasedbt[hdr2]$oncheck -pt db01:employee |grep "Number of rows"
Number of rows 0
Number of rows 209
Number of rows 200
Number of rows 100
Number of rows 100
Number of rows 100
Number of rows 100
Number of rows 100
Number of rows 100
Number of rows 1
gbasedbt[hdr2]$
#这个一行数据是1000
gbasedbt[hdr2]$oncheck -pP 3 4928
addr stamp chksum nslots flag type frptr frcnt next prev
3:4928 402653 3798 1 801 DATA 78 16298 0 0
slot ptr len flg
1 24 54 0
slot 1:
0: 0 0 3 e8 61 61 20 20 20 20 20 20 20 20 20 20 ...haa
16: 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
32: 20 20 20 20 c1 3 e 0 0 0 c1 1f 28 0 0 0 A.....A.(...
48: 62 62 0 0 ae e3 bb...c..........
gbasedbt[hdr2]$printf "%d\n" 0x3e8
1000
gbasedbt[hdr2]$
小结: RANGE分片表,自动扩充分片。range规则是一说,指定规则则是更高优先级的一说。