SEO

I have a table, ds_archive, I just run

alter fragment on table ds_archive add partition pt7 (archive_dt >=
datetime(2008-01-01 00:00:00) year to second) AND (archive_dt <
datetime(2009-01-01 00:00:00) year to second ) in dbdw1 before rmd;

and added the fragment pt7(see below).

It took more than half hour and locked the whole table to do that.( not
tolerable for online maintenance)

Fact: The remainder fragment has no data.
Observation: Looks IDS does not take the knowledge about the
fragmentation criteria, otherwise the Alter statement should be finished
fast.

Thanks,
Frank


create table "informix".ds_archive
(
dataset_name varchar(255,44) not null ,
archive_site char(10) not null ,
path_name varchar(250,50),
archive_dt datetime year to second
) with crcols
fragment by expression
partition pt1 (archive_dt < datetime(2006-01-01 00:00:00)
year to second ) in dbdw00 ,
partition pt5 ((archive_dt >= datetime(2006-01-01 00:00:00)
year to second ) AND (archive_dt < datetime(2007-01-01
00:00:00) year to second ) ) in dbdw01 ,
partition pt6 ((archive_dt >= datetime(2007-01-01 00:00:00)
year to second ) AND (archive_dt < datetime(2008-01-01
00:00:00) year to second ) ) in dbdw01 ,
partition pt7 ((archive_dt >= datetime(2008-01-01 00:00:00)
year to second ) AND (archive_dt < datetime(2009-01-01
00:00:00) year to second ) ) in dbdw01 ,
partition rmd remainder in dbdw01
extent size 4096 next size 4096 lock mode row;


create index "informix".ds_arch_idx on "informix".ds_archive (archive_dt)
using btree ;
create unique index "informix".ds_arch_uidx on "informix".ds_archive
(dataset_name,archive_site,archive_dt) using btree ;
alter table "informix".ds_archive add constraint primary key
(dataset_name,archive_site,archive_dt) constraint "informix"
.ds_arch_pk ;




--
Yunyao "Frank" Qu
Computer Sciences Corporation(CSC)
NOAA/CLASS, (301)817-4696