informix fragment 分片表的常見操作 ids9.40

1、建立分片表
create table cell_fragment
(
id int,
name varchar(10)
)
FRAGMENT by EXPRESSION
(id > 5 and id < 10) IN niosdbs,
(id <= 5) IN tpddbs;

2、生成測試資料
insert into cell_fragment values(1,'1111');
insert into cell_fragment values(4,'44');
insert into cell_fragment values(8,'81111');
insert into cell_fragment values(9,'91111');
insert into cell_fragment values(2,'21111');
insert into cell_fragment values(3,'31111');
insert into cell_fragment values(10,'31111');
insert into cell_fragment values(16,'31111');
insert into cell_fragment values(19,'31111');

3、查詢系統表，定位tabid
select * from systables where tabname ='cell_fragment'
select * from SYSFRAGMENTS where tabid = 1539

4、每次操作後更新統計資訊
update statistics high for table cell_fragment
5、add detach attach modify init更改分片
alter fragment on table cell_fragment add (id >=10 and id <20) in nrmdbs;
alter fragment on table cell_fragment detach nrmdbs temp_nrmdbs;
alter fragment on table cell_fragment attach temp_nrmdbs as (id >=10 and id <20);
alter fragment on table cell_fragment add remainder in kpidbs;
alter fragment on table cell_fragment modify kpidbs01 to (id <3) in tpddbs;
alter fragment on table cell_fragment init fragment by expression
id <3 in nrmdbs,
id >=3 and id <5 in niosdbs,
id >=5 and id <10 in kpidbs,
remainder in tpddbs;
select * from temp_nrmdbs;
alter table cell_fragment add CONSTRAINT primary key(id);

總結
1、ids9.4尚不支援 partition by expression partition part1 (id > 5 and id < 10) IN niosdbs類似的操作;ids11已經提供了支援
2、partition part1 in dbs01,partion part2 in dbs03,partition part3 in dbs01,partition part4 in dbs04;就是說每個partition要有每個具體的名稱，如果沒有具體的名

稱的話，分片的名稱就是所在dbspace的名稱。預設情況下每個分片所在不同的dbspace，如果多個分片在一個dbspace中要給每個分片起不同的名字。
[@more@]