oracle 分区表外键建索引,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 andcustomer_num<100000) in dbs11,

(customer_num>=100000 andcustomer_num<200000) in dbs12,

(customer_num>=200000 andcustomer_num<300000) in dbs13,

(customer_num>=300000 andcustomer_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 < 500000and customer_num >= 400000 ) in dbs11

--小于1second完成

--CASE 2把一个表增加到分片表中

alter fragment on table t_fragment_test attach t_fragment_test_dbs11 as customer_num < 100000and customer_num >= 1in 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-200910:02:28)

------

select * from t_fragment_test where res_dtime>= current year to second

andres_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