informix 分片表

最新推荐文章于 2024-04-23 22:08:34 发布

yutonging2016

最新推荐文章于 2024-04-23 22:08:34 发布

阅读量5.4k

点赞数

分类专栏： informix-Administrator informix-sql-plsql

本文链接：https://blog.csdn.net/yutonging2016/article/details/70756846

版权

本文详细介绍了Informix数据库中分片表的创建和管理，包括轮转法、表达式法、剩余法、列表法和固定间隔法等分区方式，并通过实例演示了如何处理唯一索引和分区表的关系，以及如何将非分区表转换为分区表，特别是间隔分区表的创建和维护。

摘要由CSDN通过智能技术生成

/***********************************************************************/
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&