INFORMIX 分片表及索引

 

在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