在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