Hmmm ... when detach partition rebuilds indexes ...

create table tab1_jj ( id serial8 not null , cr_date datetime year to second default current year to second not null , acc_id integer not null ) fragment by expression partition tab1_part_2009_q3 (cr_date < '2009-10-01 00:00:00' and cr_date >= '2009-07-01 00:00:00') in vlat2_data7, partition tab1_part_2009_q4 (cr_date < '2010-01-01 00:00:00' and cr_date >= '2009-10-01 00:00:00') in vlat2_data8, partition tab1_part_2010_q1 (cr_date < '2010-04-01 00:00:00' and cr_date >= '2010-01-01 00:00:00') in vlat2_data1, partition tab1_part_2010_q2 (cr_date < '2010-07-01 00:00:00' and cr_date >= '2010-04-01 00:00:00') in vlat2_data2, partition tab1_part_2010_q3 (cr_date < '2010-10-01 00:00:00' and cr_date >= '2010-07-01 00:00:00') in vlat2_data3, partition tab1_part_2010_q4 (cr_date < '2011-01-01 00:00:00' and cr_date >= '2010-10-01 00:00:00') in vlat2_data4, partition tab1_part_2011_q1 (cr_date < '2011-04-01 00:00:00' and cr_date >= '2011-01-01 00:00:00') in vlat2_data5, partition tab1_part_2011_q2 (cr_date < '2011-07-01 00:00:00' and cr_date >= '2011-04-01 00:00:00') in vlat2_data6, partition tab1_part_2011_q3 (cr_date < '2011-10-01 00:00:00' and cr_date >= '2011-07-01 00:00:00') in vlat2_data7, partition tab1_part_2011_q4 (cr_date < '2012-01-01 00:00:00' and cr_date >= '2011-10-01 00:00:00') in vlat2_data8, partition tab1_part_2012_q1 (cr_date < '2012-04-01 00:00:00' and cr_date >= '2012-01-01 00:00:00') in vlat2_data1, partition tab1_part_2012_q2 (cr_date < '2012-07-01 00:00:00' and cr_date >= '2012-07-01 00:00:00') in vlat2_data2, partition tab1_part_2012_q3 (cr_date < '2012-10-01 00:00:00' and cr_date >= '2012-04-01 00:00:00') in vlat2_data3, partition tab1_part_2012_q4 (cr_date < '2013-01-01 00:00:00' and cr_date >= '2012-10-01 00:00:00') in vlat2_data4, partition tab1_part_2013_q1 (cr_date < '2013-04-01 00:00:00' and cr_date >= '2013-01-01 00:00:00') in vlat2_data5, partition tab1_part_2013_q2 (cr_date < '2013-07-01 00:00:00' and cr_date >= '2013-04-01 00:00:00') in vlat2_data6, partition tab1_part_2013_q3 (cr_date < '2013-10-01 00:00:00' and cr_date >= '2013-07-01 00:00:00') in vlat2_data7, partition tab1_part_2013_q4 (cr_date < '2014-01-01 00:00:00' and cr_date >= '2013-10-01 00:00:00') in vlat2_data8, partition tab1_part_2014_q1 (cr_date < '2014-04-01 00:00:00' and cr_date >= '2014-01-01 00:00:00') in vlat2_data1, partition tab1_part_2014_q2 (cr_date < '2014-07-01 00:00:00' and cr_date >= '2014-04-01 00:00:00') in vlat2_data2, partition tab1_part_2014_q3 (cr_date < '2014-10-01 00:00:00' and cr_date >= '2014-07-01 00:00:00') in vlat2_data3, partition tab1_part_2014_q4 (cr_date < '2015-01-01 00:00:00' and cr_date >= '2014-10-01 00:00:00') in vlat2_data4, partition tab1_part_2015_q1 (cr_date < '2015-04-01 00:00:00' and cr_date >= '2015-01-01 00:00:00') in vlat2_data5, partition tab1_part_2015_q2 (cr_date < '2015-07-01 00:00:00' and cr_date >= '2015-04-01 00:00:00') in vlat2_data6, partition tab1_part_2015_q3 (cr_date < '2015-10-01 00:00:00' and cr_date >= '2015-07-01 00:00:00') in vlat2_data7 extent size 16777216 next size 16777216 lock mode row; create unique index ix1_jj on tab1_jj (id, cr_date) using btree fragment by expression partition tab1_part_2009_q3_x1 (cr_date < '2009-10-01 00:00:00' and cr_date >= '2009-07-01 00:00:00') in vlat2_index7, partition tab1_part_2009_q4_x1 (cr_date < '2010-01-01 00:00:00' and cr_date >= '2009-10-01 00:00:00') in vlat2_index8, partition tab1_part_2010_q1_x1 (cr_date < '2010-04-01 00:00:00' and cr_date >= '2010-01-01 00:00:00') in vlat2_index1, partition tab1_part_2010_q2_x1 (cr_date < '2010-07-01 00:00:00' and cr_date >= '2010-04-01 00:00:00') in vlat2_index2, partition tab1_part_2010_q3_x1 (cr_date < '2010-10-01 00:00:00' and cr_date >= '2010-07-01 00:00:00') in vlat2_index3, partition tab1_part_2010_q4_x1 (cr_date < '2011-01-01 00:00:00' and cr_date >= '2010-10-01 00:00:00') in vlat2_index4, partition tab1_part_2011_q1_x1 (cr_date < '2011-04-01 00:00:00' and cr_date >= '2011-01-01 00:00:00') in vlat2_index5, partition tab1_part_2011_q2_x1 (cr_date < '2011-07-01 00:00:00' and cr_date >= '2011-04-01 00:00:00') in vlat2_index6, partition tab1_part_2011_q3_x1 (cr_date < '2011-10-01 00:00:00' and cr_date >= '2011-07-01 00:00:00') in vlat2_index7, partition tab1_part_2011_q4_x1 (cr_date < '2012-01-01 00:00:00' and cr_date >= '2011-10-01 00:00:00') in vlat2_index8, partition tab1_part_2012_q1_x1 (cr_date < '2012-04-01 00:00:00' and cr_date >= '2012-01-01 00:00:00') in vlat2_index1, partition tab1_part_2012_q2_x1 (cr_date < '2012-07-01 00:00:00' and cr_date >= '2012-04-01 00:00:00') in vlat2_index2, partition tab1_part_2012_q3_x1 (cr_date < '2012-10-01 00:00:00' and cr_date >= '2012-07-01 00:00:00') in vlat2_index3, partition tab1_part_2012_q4_x1 (cr_date < '2013-01-01 00:00:00' and cr_date >= '2012-10-01 00:00:00') in vlat2_index4, partition tab1_part_2013_q1_x1 (cr_date < '2013-04-01 00:00:00' and cr_date >= '2013-01-01 00:00:00') in vlat2_index5, partition tab1_part_2013_q2_x1 (cr_date < '2013-07-01 00:00:00' and cr_date >= '2013-04-01 00:00:00') in vlat2_index6, partition tab1_part_2013_q3_x1 (cr_date < '2013-10-01 00:00:00' and cr_date >= '2013-07-01 00:00:00') in vlat2_index7, partition tab1_part_2013_q4_x1 (cr_date < '2014-01-01 00:00:00' and cr_date >= '2013-10-01 00:00:00') in vlat2_index8, partition tab1_part_2014_q1_x1 (cr_date < '2014-04-01 00:00:00' and cr_date >= '2014-01-01 00:00:00') in vlat2_index1, partition tab1_part_2014_q2_x1 (cr_date < '2014-07-01 00:00:00' and cr_date >= '2014-04-01 00:00:00') in vlat2_index2, partition tab1_part_2014_q3_x1 (cr_date < '2014-10-01 00:00:00' and cr_date >= '2014-07-01 00:00:00') in vlat2_index3, partition tab1_part_2014_q4_x1 (cr_date < '2015-01-01 00:00:00' and cr_date >= '2014-10-01 00:00:00') in vlat2_index4, partition tab1_part_2015_q1_x1 (cr_date < '2015-04-01 00:00:00' and cr_date >= '2015-01-01 00:00:00') in vlat2_index5, partition tab1_part_2015_q2_x1 (cr_date < '2015-07-01 00:00:00' and cr_date >= '2015-04-01 00:00:00') in vlat2_index6, partition tab1_part_2015_q3_x1 (cr_date < '2015-10-01 00:00:00' and cr_date >= '2015-07-01 00:00:00') in vlat2_index7; alter fragment on table tab1_jj detach partition tab1_part_2009_q3 detached_tab1_part_2009_q3; This results in all indexes being rebuilt (checking for CINDEX in the logical logs shows ... loads :P ) If the index is created thus create unique index ix1_jj on tab1_jj (id, cr_date) using btree; Then the detach goes through immediately ... as expected. (Note a stripped down example of the above). Surely, the fragmentation scheme is "equivalent" so the detach should go through immediately when the index partitions are in separate dbspaces?? Am I missing something? JJ

If the index has its own fragmentation scheme and is not "attached" to the table, following the table's schema, even if that index's fragmentation expression is identical to the table's, it will be rebuilt when you detach or attach a fragment. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Wed, Aug 8, 2012 at 3:09 PM, JJ <JonRitson@sky.com> wrote: > create table tab1_jj > ( > id serial8 not null , > cr_date datetime year to second default current year to second not > null , > acc_id integer not null > ) > fragment by expression > partition tab1_part_2009_q3 (cr_date < '2009-10-01 00:00:00' and > cr_date >= '2009-07-01 00:00:00') in vlat2_data7, > partition tab1_part_2009_q4 (cr_date < '2010-01-01 00:00:00' and > cr_date >= '2009-10-01 00:00:00') in vlat2_data8, > partition tab1_part_2010_q1 (cr_date < '2010-04-01 00:00:00' and > cr_date >= '2010-01-01 00:00:00') in vlat2_data1, > partition tab1_part_2010_q2 (cr_date < '2010-07-01 00:00:00' and > cr_date >= '2010-04-01 00:00:00') in vlat2_data2, > partition tab1_part_2010_q3 (cr_date < '2010-10-01 00:00:00' and > cr_date >= '2010-07-01 00:00:00') in vlat2_data3, > partition tab1_part_2010_q4 (cr_date < '2011-01-01 00:00:00' and > cr_date >= '2010-10-01 00:00:00') in vlat2_data4, > partition tab1_part_2011_q1 (cr_date < '2011-04-01 00:00:00' and > cr_date >= '2011-01-01 00:00:00') in vlat2_data5, > partition tab1_part_2011_q2 (cr_date < '2011-07-01 00:00:00' and > cr_date >= '2011-04-01 00:00:00') in vlat2_data6, > partition tab1_part_2011_q3 (cr_date < '2011-10-01 00:00:00' and > cr_date >= '2011-07-01 00:00:00') in vlat2_data7, > partition tab1_part_2011_q4 (cr_date < '2012-01-01 00:00:00' and > cr_date >= '2011-10-01 00:00:00') in vlat2_data8, > partition tab1_part_2012_q1 (cr_date < '2012-04-01 00:00:00' and > cr_date >= '2012-01-01 00:00:00') in vlat2_data1, > partition tab1_part_2012_q2 (cr_date < '2012-07-01 00:00:00' and > cr_date >= '2012-07-01 00:00:00') in vlat2_data2, > partition tab1_part_2012_q3 (cr_date < '2012-10-01 00:00:00' and > cr_date >= '2012-04-01 00:00:00') in vlat2_data3, > partition tab1_part_2012_q4 (cr_date < '2013-01-01 00:00:00' and > cr_date >= '2012-10-01 00:00:00') in vlat2_data4, > partition tab1_part_2013_q1 (cr_date < '2013-04-01 00:00:00' and > cr_date >= '2013-01-01 00:00:00') in vlat2_data5, > partition tab1_part_2013_q2 (cr_date < '2013-07-01 00:00:00' and > cr_date >= '2013-04-01 00:00:00') in vlat2_data6, > partition tab1_part_2013_q3 (cr_date < '2013-10-01 00:00:00' and > cr_date >= '2013-07-01 00:00:00') in vlat2_data7, > partition tab1_part_2013_q4 (cr_date < '2014-01-01 00:00:00' and > cr_date >= '2013-10-01 00:00:00') in vlat2_data8, > partition tab1_part_2014_q1 (cr_date < '2014-04-01 00:00:00' and > cr_date >= '2014-01-01 00:00:00') in vlat2_data1, > partition tab1_part_2014_q2 (cr_date < '2014-07-01 00:00:00' and > cr_date >= '2014-04-01 00:00:00') in vlat2_data2, > partition tab1_part_2014_q3 (cr_date < '2014-10-01 00:00:00' and > cr_date >= '2014-07-01 00:00:00') in vlat2_data3, > partition tab1_part_2014_q4 (cr_date < '2015-01-01 00:00:00' and > cr_date >= '2014-10-01 00:00:00') in vlat2_data4, > partition tab1_part_2015_q1 (cr_date < '2015-04-01 00:00:00' and > cr_date >= '2015-01-01 00:00:00') in vlat2_data5, > partition tab1_part_2015_q2 (cr_date < '2015-07-01 00:00:00' and > cr_date >= '2015-04-01 00:00:00') in vlat2_data6, > partition tab1_part_2015_q3 (cr_date < '2015-10-01 00:00:00' and> cr_date >= '2015-07-01 00:00:00') in vlat2_data7 > extent size 16777216 next size 16777216 lock mode row; > > create unique index ix1_jj on tab1_jj (id, cr_date) using btree > fragment by expression > partition tab1_part_2009_q3_x1 (cr_date < '2009-10-01 00:00:00' and > cr_date >= '2009-07-01 00:00:00') in vlat2_index7, > partition tab1_part_2009_q4_x1 (cr_date < '2010-01-01 00:00:00' and > cr_date >= '2009-10-01 00:00:00') in vlat2_index8, > partition tab1_part_2010_q1_x1 (cr_date < '2010-04-01 00:00:00' and > cr_date >= '2010-01-01 00:00:00') in vlat2_index1, > partition tab1_part_2010_q2_x1 (cr_date < '2010-07-01 00:00:00' and > cr_date >= '2010-04-01 00:00:00') in vlat2_index2, > partition tab1_part_2010_q3_x1 (cr_date < '2010-10-01 00:00:00' and > cr_date >= '2010-07-01 00:00:00') in vlat2_index3, > partition tab1_part_2010_q4_x1 (cr_date < '2011-01-01 00:00:00' and > cr_date >= '2010-10-01 00:00:00') in vlat2_index4, > partition tab1_part_2011_q1_x1 (cr_date < '2011-04-01 00:00:00' and > cr_date >= '2011-01-01 00:00:00') in vlat2_index5, > partition tab1_part_2011_q2_x1 (cr_date < '2011-07-01 00:00:00' and > cr_date >= '2011-04-01 00:00:00') in vlat2_index6, > partition tab1_part_2011_q3_x1 (cr_date < '2011-10-01 00:00:00' and > cr_date >= '2011-07-01 00:00:00') in vlat2_index7, > partition tab1_part_2011_q4_x1 (cr_date < '2012-01-01 00:00:00' and > cr_date >= '2011-10-01 00:00:00') in vlat2_index8, > partition tab1_part_2012_q1_x1 (cr_date < '2012-04-01 00:00:00' and > cr_date >= '2012-01-01 00:00:00') in vlat2_index1, > partition tab1_part_2012_q2_x1 (cr_date < '2012-07-01 00:00:00' and > cr_date >= '2012-04-01 00:00:00') in vlat2_index2, > partition tab1_part_2012_q3_x1 (cr_date < '2012-10-01 00:00:00' and > cr_date >= '2012-07-01 00:00:00') in vlat2_index3, > partition tab1_part_2012_q4_x1 (cr_date < '2013-01-01 00:00:00' and > cr_date >= '2012-10-01 00:00:00') in vlat2_index4, > partition tab1_part_2013_q1_x1 (cr_date < '2013-04-01 00:00:00' and > cr_date >= '2013-01-01 00:00:00') in vlat2_index5, > partition tab1_part_2013_q2_x1 (cr_date < '2013-07-01 00:00:00' and > cr_date >= '2013-04-01 00:00:00') in vlat2_index6, > partition tab1_part_2013_q3_x1 (cr_date < '2013-10-01 00:00:00' and > cr_date >= '2013-07-01 00:00:00') in vlat2_index7, > partition tab1_part_2013_q4_x1 (cr_date < '2014-01-01 00:00:00' and > cr_date >= '2013-10-01 00:00:00') in vlat2_index8, > partition tab1_part_2014_q1_x1 (cr_date < '2014-04-01 00:00:00' and > cr_date >= '2014-01-01 00:00:00') in vlat2_index1, > partition tab1_part_2014_q2_x1 (cr_date < '2014-07-01 00:00:00' and > cr_date >= '2014-04-01 00:00:00') in vlat2_index2, > partition tab1_part_2014_q3_x1 (cr_date < '2014-10-01 00:00:00' and > cr_date >= '2014-07-01 00:00:00') in vlat2_index3, > partition tab1_part_2014_q4_x1 (cr_date < '2015-01-01 00:00:00' and > cr_date >= '2014-10-01 00:00:00') in vlat2_index4, > partition tab1_part_2015_q1_x1 (cr_date < '2015-04-01 00:00:00' and > cr_date >= '2015-01-01 00:00:00') in vlat2_index5, > partition tab1_part_2015_q2_x1 (cr_date < '2015-07-01 00:00:00' and> cr_date

http://publib.boulder.ibm.com/infocenter/idshelp/v115/topic/com.ibm.perf.doc/ids_prf_487.htm