/***********************************************************************/
informix 分片表
分区表的索引:
唯一索引: 对于轮转法要指定 DBSPACE
对于表达式法: 要指定DBSPACE 或 索引字段包含 分区字段
否则报:872: Invalid fragment strategy or expression for the unique index.
/***********************************************************************/
一 分区表的种类
cd /opt/dbs
touch dbs1 dbs2 dbs3 dbs4
chmod 660 *
onspaces -c -d dbs1 -p /opt/dbs/dbs1 -o 0 -s 100000 -k 16
onspaces -c -d dbs2 -p /opt/dbs/dbs2 -o 0 -s 100000 -k 16
onspaces -c -d dbs3 -p /opt/dbs/dbs3 -o 0 -s 100000 -k 16
onspaces -c -d dbs4 -p /opt/dbs/dbs4 -o 0 -s 100000 -k 16
1 round-robin 轮转法
create table a11(a int ,b int,c int,d datetime year to second )
fragment by round robin in dbs1,dbs2 ,dbs3,dbs4;
create table a12 (a int ,b int ,c date ,d datetime year to FRACTION(5))
fragment by round robin in dbs1,dbs2,dbs3,dbs4;
create table a13 ( a int ,b int ,c datetime year to fraction(3))
fragment by round robin in dbs1,dbs2,dbs3,dbs4;
--轮转法 : 唯一索引要指定 空间
create unique index idx_a11_a on a11(a);
872: Invalid fragment strategy or expression for the unique index.
create unique index idx_a11_a on a11(a) in datadbs1;
create index idx_a11_b on a11(b);
insert into a11 values(1,1,1,current);
insert into a11 values(2,2,2,current);
insert into a11 values(3,3,3,current);
insert into a11 values(4,4,4,current);
[informix@node1 ~]$ oncheck -ci test1:a11
Validating indexes for test1:informix.a11...
Index idx_a11_b
Index fragment partition dbs1 in DBspace dbs1
Index fragment partition dbs2 in DBspace dbs2
Index fragment partition dbs3 in DBspace dbs3
Index fragment partition dbs4 in DBspace dbs4
Index idx_a11_a
Index fragment partition datadbs1 in DBspace datadbs1
2 表达式
create table b11 (a int ,b int ,c date ,d datetime year to FRACTION(5))
fragment by expression
d<'2011-01-01 00:00:00' and d>='2010-01-01 00:00:00' in dbs1,
d<'2012-01-01 00:00:00' and d>='2011-01-01 00:00:00' in dbs2,
d<'2013-01-01 00:00:00' and d>='2012-01-01 00:00:00' in dbs3;
create unique index idx_b1_a on b11(a);
872: Invalid fragment strategy or expression for the unique index.
create unique index idx_b1_a on b11(a) in datadbs1;
create index idx_b1_b on b11(b);
create index idx_b1_d on b11(d);
[informix@node1 ~]$ oncheck -ci test1:b11
Validating indexes for test1:informix.b11...
Index idx_b1_a
Index fragment partition datadbs1 in DBspace datadbs1
Index idx_b1_b
Index fragment partition dbs1 in DBspace dbs1
Index fragment partition dbs2 in DBspace dbs2
Index fragment partition dbs3 in DBspace dbs3
Index idx_b1_d
Index fragment partition dbs1 in DBspace dbs1
Index fragment partition dbs2 in DBspace dbs2
Index fragment partition dbs3 in DBspace dbs3
drop index idx_b1_d;
create index idx_b1_d on b11(d)
fragment by expression
d<'2011-01-01 00:00:00' and d>='2010-01-01 00:00:00' in dbs1,
d<'2012-01-01 00:00:00' and d>='2011-01-01 00:00:00' in dbs2,
d<'2013-01-01 00:00:00' and d>='2012-01-01 00:00:00' in dbs3;
[informix@node1 ~]$ oncheck -ci test1:b11
Validating indexes for test1:informix.b11...
Index idx_b1_a
Index fragment partition datadbs1 in DBspace datadbs1
Index idx_b1_b
Index fragment partition dbs1 in DBspace dbs1
Index fragment partition dbs2 in DBspace dbs2
Index fragment partition dbs3 in DBspace dbs3
Index idx_b1_d
Index fragment partition dbs1 in DBspace dbs1
Index fragment partition dbs2 in DBspace dbs2
Index fragment partition dbs3 in DBspace dbs3
drop index idx_b1_a;
create unique index idx_b1_a on b11(a,d);
[informix@node1 ~]$ oncheck -ci test1:b11
Validating indexes for test1:informix.b11...
Index idx_b1_a
Index fragment partition dbs1 in DBspace dbs1
Index fragment partition dbs2 in DBspace dbs2
Index fragment partition dbs3 in DBspace dbs3
Index idx_b1_b
Index fragment partition dbs1 in DBspace dbs1
Index fragment partition dbs2 in DBspace dbs2
Index fragment partition dbs3 in DBspace dbs3
Index idx_b1_d
Index fragment partition dbs1 in DBspace dbs1
Index fragment partition dbs2 in DBspace dbs2
Index fragment partition dbs3 in DBspace dbs3
3 remainder
create table b12 (a int ,b int ,c date ,d datetime year to FRACTION(5))
fragment by expression
d<'2011-01-01 00:00:00' and d>='2010-01-01 00:00:00' in dbs1,
d<'2012-01-01 00:00:00' and d>='2011-01-01 00:00:00' in dbs2,
remainder in dbs3;
create index idx_b12_a on b12(a);
create index idx_b12_d on b12(d);
[informix@node1 ~]$ oncheck -ci test1:b12
Validating indexes for test1:informix.b12...
Index idx_b12_a
Index fragment partition dbs1 in DBspace dbs1
Index fragment partition dbs2 in DBspace dbs2
Index fragment partition dbs3 in DBspace dbs3
Index idx_b12_d
Index fragment partition dbs1 in DBspace dbs1
Index fragment partition dbs2 in DBspace dbs2
Index fragment partition dbs3 in DBspace dbs3
4 列表 LIST
create table b13(a int ,b int ,c int)
fragment by list(c)
partition p0 values(1,2) in dbs1,
partition p1 values(3,4) in dbs2,
partition p2 values(5) in dbs3;
create table b14(a int ,b int ,c int)
fragment by list(c)
partition p0 values(1,2) in dbs1,
partition p1 values(3,4) in dbs2,
partition p2 values(null) in dbs3;
5 固定间隔 INTERVAL
create table c11( a int ,b int ,c int ,d datetime year to fraction(5))
fragment by range (d)
interval (10 units day)
store in (dbs1,dbs2,dbs3)
partition p1 values<'2013-01-01 00:00:00' in datadbs1;
create unique index idx_c11_a on c11(a);
872: Invalid fragment strategy or expression for the unique index.
create unique index idx_c11_a on c11(a) in datadbs2;
drop index idx_c11_a;
create unique index idx_c11_a on c11(a,d) ;
[informix@node1 ~]$ oncheck -ci test1:c11
Validating indexes for test1:informix.c11...
Index idx_c11_a
Index fragment partition p1 in DBspace datadbs1
[informix@node1 ~]$
create table c12( a int ,b int ,c int ,d datetime year to fraction(5))
fragment by range (d)
interval (10 units day)
store in (dbs1,dbs2,dbs3)
partition p1 values<'2013-01-01 00:00:00' in datadbs1;
create table c13(a int ,b int,c int)
fragment by range (b)
interval (100)
store in (dbs1,dbs2,dbs3)
partition p0 values<200 in datadbs1,
partition p1 values&
informix 分片表
分区表的索引:
唯一索引: 对于轮转法要指定 DBSPACE
对于表达式法: 要指定DBSPACE 或 索引字段包含 分区字段
否则报:872: Invalid fragment strategy or expression for the unique index.
/***********************************************************************/
一 分区表的种类
cd /opt/dbs
touch dbs1 dbs2 dbs3 dbs4
chmod 660 *
onspaces -c -d dbs1 -p /opt/dbs/dbs1 -o 0 -s 100000 -k 16
onspaces -c -d dbs2 -p /opt/dbs/dbs2 -o 0 -s 100000 -k 16
onspaces -c -d dbs3 -p /opt/dbs/dbs3 -o 0 -s 100000 -k 16
onspaces -c -d dbs4 -p /opt/dbs/dbs4 -o 0 -s 100000 -k 16
1 round-robin 轮转法
create table a11(a int ,b int,c int,d datetime year to second )
fragment by round robin in dbs1,dbs2 ,dbs3,dbs4;
create table a12 (a int ,b int ,c date ,d datetime year to FRACTION(5))
fragment by round robin in dbs1,dbs2,dbs3,dbs4;
create table a13 ( a int ,b int ,c datetime year to fraction(3))
fragment by round robin in dbs1,dbs2,dbs3,dbs4;
--轮转法 : 唯一索引要指定 空间
create unique index idx_a11_a on a11(a);
872: Invalid fragment strategy or expression for the unique index.
create unique index idx_a11_a on a11(a) in datadbs1;
create index idx_a11_b on a11(b);
insert into a11 values(1,1,1,current);
insert into a11 values(2,2,2,current);
insert into a11 values(3,3,3,current);
insert into a11 values(4,4,4,current);
[informix@node1 ~]$ oncheck -ci test1:a11
Validating indexes for test1:informix.a11...
Index idx_a11_b
Index fragment partition dbs1 in DBspace dbs1
Index fragment partition dbs2 in DBspace dbs2
Index fragment partition dbs3 in DBspace dbs3
Index fragment partition dbs4 in DBspace dbs4
Index idx_a11_a
Index fragment partition datadbs1 in DBspace datadbs1
2 表达式
create table b11 (a int ,b int ,c date ,d datetime year to FRACTION(5))
fragment by expression
d<'2011-01-01 00:00:00' and d>='2010-01-01 00:00:00' in dbs1,
d<'2012-01-01 00:00:00' and d>='2011-01-01 00:00:00' in dbs2,
d<'2013-01-01 00:00:00' and d>='2012-01-01 00:00:00' in dbs3;
create unique index idx_b1_a on b11(a);
872: Invalid fragment strategy or expression for the unique index.
create unique index idx_b1_a on b11(a) in datadbs1;
create index idx_b1_b on b11(b);
create index idx_b1_d on b11(d);
[informix@node1 ~]$ oncheck -ci test1:b11
Validating indexes for test1:informix.b11...
Index idx_b1_a
Index fragment partition datadbs1 in DBspace datadbs1
Index idx_b1_b
Index fragment partition dbs1 in DBspace dbs1
Index fragment partition dbs2 in DBspace dbs2
Index fragment partition dbs3 in DBspace dbs3
Index idx_b1_d
Index fragment partition dbs1 in DBspace dbs1
Index fragment partition dbs2 in DBspace dbs2
Index fragment partition dbs3 in DBspace dbs3
drop index idx_b1_d;
create index idx_b1_d on b11(d)
fragment by expression
d<'2011-01-01 00:00:00' and d>='2010-01-01 00:00:00' in dbs1,
d<'2012-01-01 00:00:00' and d>='2011-01-01 00:00:00' in dbs2,
d<'2013-01-01 00:00:00' and d>='2012-01-01 00:00:00' in dbs3;
[informix@node1 ~]$ oncheck -ci test1:b11
Validating indexes for test1:informix.b11...
Index idx_b1_a
Index fragment partition datadbs1 in DBspace datadbs1
Index idx_b1_b
Index fragment partition dbs1 in DBspace dbs1
Index fragment partition dbs2 in DBspace dbs2
Index fragment partition dbs3 in DBspace dbs3
Index idx_b1_d
Index fragment partition dbs1 in DBspace dbs1
Index fragment partition dbs2 in DBspace dbs2
Index fragment partition dbs3 in DBspace dbs3
drop index idx_b1_a;
create unique index idx_b1_a on b11(a,d);
[informix@node1 ~]$ oncheck -ci test1:b11
Validating indexes for test1:informix.b11...
Index idx_b1_a
Index fragment partition dbs1 in DBspace dbs1
Index fragment partition dbs2 in DBspace dbs2
Index fragment partition dbs3 in DBspace dbs3
Index idx_b1_b
Index fragment partition dbs1 in DBspace dbs1
Index fragment partition dbs2 in DBspace dbs2
Index fragment partition dbs3 in DBspace dbs3
Index idx_b1_d
Index fragment partition dbs1 in DBspace dbs1
Index fragment partition dbs2 in DBspace dbs2
Index fragment partition dbs3 in DBspace dbs3
3 remainder
create table b12 (a int ,b int ,c date ,d datetime year to FRACTION(5))
fragment by expression
d<'2011-01-01 00:00:00' and d>='2010-01-01 00:00:00' in dbs1,
d<'2012-01-01 00:00:00' and d>='2011-01-01 00:00:00' in dbs2,
remainder in dbs3;
create index idx_b12_a on b12(a);
create index idx_b12_d on b12(d);
[informix@node1 ~]$ oncheck -ci test1:b12
Validating indexes for test1:informix.b12...
Index idx_b12_a
Index fragment partition dbs1 in DBspace dbs1
Index fragment partition dbs2 in DBspace dbs2
Index fragment partition dbs3 in DBspace dbs3
Index idx_b12_d
Index fragment partition dbs1 in DBspace dbs1
Index fragment partition dbs2 in DBspace dbs2
Index fragment partition dbs3 in DBspace dbs3
4 列表 LIST
create table b13(a int ,b int ,c int)
fragment by list(c)
partition p0 values(1,2) in dbs1,
partition p1 values(3,4) in dbs2,
partition p2 values(5) in dbs3;
create table b14(a int ,b int ,c int)
fragment by list(c)
partition p0 values(1,2) in dbs1,
partition p1 values(3,4) in dbs2,
partition p2 values(null) in dbs3;
5 固定间隔 INTERVAL
create table c11( a int ,b int ,c int ,d datetime year to fraction(5))
fragment by range (d)
interval (10 units day)
store in (dbs1,dbs2,dbs3)
partition p1 values<'2013-01-01 00:00:00' in datadbs1;
create unique index idx_c11_a on c11(a);
872: Invalid fragment strategy or expression for the unique index.
create unique index idx_c11_a on c11(a) in datadbs2;
drop index idx_c11_a;
create unique index idx_c11_a on c11(a,d) ;
[informix@node1 ~]$ oncheck -ci test1:c11
Validating indexes for test1:informix.c11...
Index idx_c11_a
Index fragment partition p1 in DBspace datadbs1
[informix@node1 ~]$
create table c12( a int ,b int ,c int ,d datetime year to fraction(5))
fragment by range (d)
interval (10 units day)
store in (dbs1,dbs2,dbs3)
partition p1 values<'2013-01-01 00:00:00' in datadbs1;
create table c13(a int ,b int,c int)
fragment by range (b)
interval (100)
store in (dbs1,dbs2,dbs3)
partition p0 values<200 in datadbs1,
partition p1 values&