INFORMIX 分片表及索引
来源:互联网 发布:linux ssh简介 编辑:程序博客网 时间:2024/06/06 03:18
在INFORMIX 中,分片表可以支持除了在分片主键上进行创建本地索引外,还可以创建多个其他非分片键字段的本地索引,而在对该表进行分片管理时(删除一个分片,增加一个分片)不影响表的使用,索引不需要重建。【oracle中需要重建index】。
1、创建分片表
create table t_fragment_test
(
customer_num integer,
call_dtime datetime year to minute,
user_id char(32)
default user,
call_code char(1),
call_descr char(240),
res_dtime datetime year to minute,
res_descr char(240)
)fragment by expression
(customer_num>=1 and customer_num<100000) in dbs11,
(customer_num>=100000 and customer_num<200000) in dbs12,
(customer_num>=200000 and customer_num<300000) in dbs13,
(customer_num>=300000 and customer_num<400000) in dbs14
extent size 102400 next size 10240;
2、生成数据
--产生很多280万条记录,分布在dbs11~dbs14
3、创建索引
create unique index idx_t_fragment_test1 on t_fragment_test(customer_num,call_dtime) ;
--该索引第一个字段为分片键customer_num,以便在查询中可以消除分片,提升效率。
create index idx_t_fragment_test2 on t_fragment_test(res_dtime);
create index idx_t_fragment_test3 on t_fragment_test(call_code);
--创建2个不包含分片键的索引,但这2个索引会自动创建为本地索引(索引与数据存储在相同的dbspace上)
--注意事项:对于需要动态进行分片表分片调整,即动态删除(detach )增加(attach)分片的分片表,我们需要注意: informix数据库会为表中的primary key,unique 约束自动创建全局索引,同时不要包含外键。考虑到性能估需要注意不要在create table中使用primary key,unique约束,请使用unique index来替代实现。
4、删除一个分片
alter fragment on table t_fragment_test detach dbs11 t_fragment_test_dbs11;
--小于 1 second完成
5、增加一个分片
--CASE 1增加一个空分片
alter fragment on table t_fragment_test add (customer_num < 500000 and customer_num >= 400000 ) in dbs11
--小于1second完成
--CASE 2把一个表增加到分片表中
alter fragment on table t_fragment_test attach t_fragment_test_dbs11 as customer_num < 100000 and customer_num >= 1 in dbs11
--执行时间较长,若只有idx_t_fragment_test1情况,速度很快
总结:
以下是为在删除分片前、后、增加一个分片后的两个SQL语句的执行计划,从执行计划可以得知INFORMIX的分片表及索引的使用情况十分优异。
select * from t_fragment_test where customer_num >=100000 and
customer_num <=100010
Estimated Cost: 14
Estimated # of Rows Returned: 60
1) informix.t_fragment_test: INDEX PATH
(1) Index Name: informix.idx_t_fragment_test1
Index Keys: customer_num call_dtime (Serial, fragments: 0)
Fragments Scanned: (0) dbs12
Lower Index Filter: informix.t_fragment_test.customer_num >= 100000
Upper Index Filter: informix.t_fragment_test.customer_num <= 100010
QUERY: (OPTIMIZATION TIMESTAMP: 10-21-2009 10:02:28)
------
select * from t_fragment_test where res_dtime>= current year to second
and res_dtime<= current year to second +interval(3) hour to hour
Estimated Cost: 9
Estimated # of Rows Returned: 1
1) informix.t_fragment_test: INDEX PATH
(1) Index Name: informix.idx_t_fragment_test2
Index Keys: res_dtime (Serial, fragments: ALL)
(fragments might be eliminated at runtime because filter contains
runtime constants)
Lower Index Filter: informix.t_fragment_test.res_dtime >= CURRENT year to second
Upper Index Filter: informix.t_fragment_test.res_dtime <= CURRENT year to second+ interval( 3) hour to hour
- INFORMIX 分片表及索引
- informix 分片表
- VM扩充、分区、创建裸设备及informix创建数据空间、分片表
- informix建索引
- Informix 表、索引对字段个数、长度的限制情况
- 序列:索引,分片,运算
- 索引、分片和矩阵
- python 索引和分片
- informix中怎样查看索引
- 序列之索引和分片
- python 字符串 索引和分片
- mongo分片及驱动
- MTU及分片总结
- mongodb 集群及分片
- Informix数据库利用索引提高查询效率
- 统计INFORMIX库中表索引INDEX数量
- Informix 删除主索引或主键
- Informix索引性能调优的建议
- Delphi中WebBrowser自动填表模板
- 13579
- Android Building System 分析
- WinCE6.0 驱动中的中断处理的问题
- pku [1144] zju[1311]
- INFORMIX 分片表及索引
- 网络管理员日记(8)里应外合:Linux下的后门和日志工具
- MySQL中文参考手册-- 常用查询的例子
- Android数据存储
- Linux系统下面制作RPM安装文件
- windows下ftp自动上传
- MySQL的SELECT技巧大全
- android adapter的体系
- 用小票打印机打印