Hi,
I am trying to make ALTER FRAGMENT ATTACH as seamless as possible, ideally to the point that given the right conditions the attach will just involve updating system tables and no data movement. Therefore it will take a matter of seconds no matter how large the tables involved are.
Here is a little test case:
-- Original table, in reality a table that has existed for some time with many millions of rows
create table original_table
(
id serial not null ,
oid integer not null ,
cid integer not null ,
called_date datetime year to second not null ,
status char(1) not null ,
show char(1) default 'N' not null ,
recur_index integer default 0
) in dbspace1;
create unique index original_table_u1 on original_table (id) using btree;
alter table original_table add constraint primary key (id) constraint original_table_pk;
-- Insert data
insert into original_table (oid, cid, called_date, status) values (1,1,current, 'A');
insert into original_table (oid, cid, called_date, status) values (2,2,current, 'B');
insert into original_table (oid, cid, called_date, status) values (3,3,current, 'C');
insert into original_table (oid, cid, called_date, status) values (4,4,current, 'D');
insert into original_table (oid, cid, called_date, status) values (5,5,current, 'E');
-- Insert more rows....
-- Empty new partitioned table
create table new_partitioned_table
(
id int not null ,
oid integer not null ,
cid integer not null ,
called_date datetime year to second not null ,
status char(1) not null ,
show char(1) default 'N' not null ,
recur_index integer default 0
) fragment by range(called_offer_id)
interval(115000000)
store in (dbspace1, dbspace2, dbspace3, dbspace4)
partition new_partitioned_table_p0 VALUES < 0 in dbspace1 statlevel fragment;
Then the attach:
ALTER FRAGMENT ON TABLE new_partitioned_table ATTACH original_table AS PARTITION new_partitioned_table_p1 VALUES < 115000000;
It all works fine but is not especially quick. Although there is little write activity, studying the partition profiles shows what appears to be a full scan of the original table during the alter fragment operation. I am not sure why this is necessary. The part number is then re-used as a partition of the new table.
In reality the situation gets more complicated and the command takes longer with indices on the new table, which also seem to involve more I/O than you might hope for. In the interests of the simplest case possible I am not asking about those right now.
Has anyone got any tips or experience to share?
Ben.
------------------------------
Benjamin Thompson
------------------------------
#Informix