Informix Dynamic Server数据库分段存储

create table dcc_saacnamt1 ( 
sa_acct_no char(28) not null , 
…… 
etl_load_date date ) 
FRAGMENT BY EXPRESSION 
etl_load_date <= "20050630" and etl_load_date >= "20050101" 
in datatest2, 
etl_load_date <="20051231" and etl_load_date>="20050701" 
in datatest1;

date >aa {输出开始时间} 
dbaccess -s testfrag<<!{打开测试用数据库} 
set explain on;{打开informix跟踪器} 
SET OPTIMIZATION FIRST_ROWS;{打开informix选择最优的查询路径} 
set pdqpriority high;{打开informix PDQ并行查询开关} 
drop table test_poll1; 
create table test_poll1{建立测试用表} 
( 
sa_no char(28), 
etl_date date 
) in datatest1; 
insert into test_poll1 (sa_no,etl_date) 
select sa_acct_no,etl_load_date from dcc_saacnamt1 
从分段存储表中读取数据} 
where (etl_load_date <= "20050630" 
and etl_load_date>= "20050101"); 
! 
date >>aa{输出分段存储读取数据结束时间} 
dbaccess -s testfrag<<!{打开测试用数据库} 
set explain on;{打开informix跟踪器} 
SET OPTIMIZATION FIRST_ROWS; {
打开informix选择最优的查询路径} 
set pdqpriority high;{打开informix PDQ并行查询开关} 
drop table test_poll; 
create table test_poll{建立测试用表} 
( 
sa_no char(28), 
etl_date date 
) in datatest1; 
insert into test_poll (sa_no,etl_date) 
select sa_acct_no,etl_load_date from dcc_saacnamt 
{从非分段存储表中读取数据} 
where (etl_load_date <= "20050630" 
and etl_load_date>= "20050101"); 
! 
date >>aa{输出结束时间}

QUERY: 
{表分段存储后查询数据时Informix跟踪结果} 
insert into test_poll1 (sa_no,etl_date) 
select sa_acct_no,
etl_load_date from dcc_saacnamt1 
where (etl_load_date <= "20050630" 
and etl_load_date>= "20050101") 
Estimated Cost: 2 
Estimated # of Rows Returned: 11 
Maximum Threads: 1 
1) brow.dcc_saacnamt1: 
SEQUENTIAL SCAN (Parallel, fragments: 0) 
Filters: (brow.dcc_saacnamt1.etl_load_date 
<= 2005/06/30 AND brow.dcc_saacna 
mt1.etl_load_date >= 2005/01/01 ) 
QUERY: 
{表非分段存储后查询数据时Informix跟踪结果} 
...... 
insert into test_poll (sa_no,etl_date) 
select sa_acct_no,etl_load_date from dcc_saacnamt 
where (etl_load_date <= "20050630" 
and etl_load_date>= "20050101") 
Estimated Cost: 2 
Estimated # of Rows Returned: 111204 
Maximum Threads: 1 
1) brow.dcc_saacnamt: SEQUENTIAL SCAN 
Filters: (brow.dcc_saacnamt.etl_load_date 
<= 2005/06/30 AND brow.dcc_saacnam 
t.etl_load_date >= 2005/01/01 )