RE: Attach fragment to index not atomic - FIXED

Indeed - the BEFORE part of the fragmentation clause was the killer. Take that off and it runs in a second or two. Much better! Thanks for the help - odd it affected the indexes and not the table. Jack - Yes the unique index is forming part of the primary key constraint. I was seeing the same problems on the other index as well (not part of a constraint). ________________________________ From: Jack Parker [mailto:jack.parker4@verizon.net] Sent: Wednesday, 17 December 2008 2:19 a.m. To: Jarrod Teale; informix-list@iiug.org Subject: RE: Attach fragment to index not atomic Get rid of the 'BEFORE', otherwise there is indeed an insane re-reading of the data to ensure that it fits the fragmentation clause and that no migration is necessary. The minimal performance benefit you gain from having the index show up earlier in the frag clause isn't worth the cost of that read IMHO. I would be interested to hear if you are using a primary key constraint on underlying fragmented data with the same scheme - I saw a lot of problems with that as well and wound up dropping the constraint itself - still have the unique index which is composite across the key + frag_date. That works well. j. Sane ego te vocavi. Forsitan capedictum tuum desit. -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org]On Behalf Of Jarrod Teale Sent: Monday, December 15, 2008 9:58 PM To: informix-list@iiug.org Subject: Attach fragment to index not atomic Hi, IDS11.10.UC2W2 on RHEL4 We have the index (and a few more like it) below. It is used for the primary key of a table. When I attach a new fragment to the system it reads every page of the index. So adding 12 fragments, one for each month is a pain. alter fragment on index pk_tagdata_73 add (dt_filter = 200901 ) instan_03a_idx_2009_01 before stan_03a_idx_2008_01 As you can see from the schema, there is no remainder clause, so I would expect this operation to be atomic. The fragments for the table are (same fragment scheme). There is no data in the table for the fragments being added (the date filter is for Jan 2009) Any ideas why this is not running as an atomic operation? It's taking a really long time over 5 TB data! create unique index "sdev".pk_tagdata_73 on "sdev".tagdata_73 (group_id,dt_filter,sample_dt) using btree fragment by expression (dt_filter = 200801 ) in stan_03a_idx_2008_01 , (dt_filter = 200802 ) in stan_03a_idx_2008_02 , (dt_filter = 200803 ) in stan_03a_idx_2008_03 , (dt_filter = 200804 ) in stan_03a_idx_2008_04 , (dt_filter = 200805 ) in stan_03a_idx_2008_05 , (dt_filter = 200806 ) in stan_03a_idx_2008_06 , (dt_filter = 200807 ) in stan_03a_idx_2008_07 , (dt_filter = 200808 ) in stan_03a_idx_2008_08 , (dt_filter = 200809 ) in stan_03a_idx_2008_09 , (dt_filter = 200810 ) in stan_03a_idx_2008_10 , (dt_filter = 200811 ) in stan_03a_idx_2008_11 , (dt_filter = 200812 ) in stan_03a_idx_2008_12 , (dt_filter = 200701 ) in stan_03a_idx_2007_01 , (dt_filter = 200702 ) in stan_03a_idx_2007_02 , (dt_filter = 200703 ) in stan_03a_idx_2007_03 , (dt_filter = 200704 ) in stan_03a_idx_2007_04 , (dt_filter = 200705 ) in stan_03a_idx_2007_05 , (dt_filter = 200706 ) in stan_03a_idx_2007_06 , (dt_filter = 200707 ) in stan_03a_idx_2007_07 , (dt_filter = 200708 ) in stan_03a_idx_2007_08 , (dt_filter = 200709 ) in stan_03a_idx_2007_09 , (dt_filter = 200710 ) in stan_03a_idx_2007_10 , (dt_filter = 200711 ) in stan_03a_idx_2007_11 , (dt_filter = 200712 ) in stan_03a_idx_2007_12 ; Thanks in advance for the help Jarrod Teale Team Lead - Manufacturing Execution Systems Automation & Process Control Group NZ Technical Fonterra Fonterra Extn: 77525 DDI: +64 7 850 7525 Mobile: +64 21 968 364 fax: +64 7 849 7855 email: jarrod.teale@fonterra.com <mailto:jarrod.teale@fonterra.com> DISCLAIMER: This email contains confidential information and may be legally privileged. If you are not the intended recipient or have received this email in error, please notify the sender immediately and destroy this email. You may not use, disclose or copy this email or its attachments in any way. Any opinions expressed in this email are those of the author and are not necessarily those of the Fonterra Co-operative Group. http://www.fonterra.com/ DISCLAIMER: This email contains confidential information and may be legally privileged. If you are not the intended recipient or have received this email in error, please notify the sender immediately and destroy this email. You may not use, disclose or copy this email or its attachments in any way. Any opinions expressed in this email are those of the author and are not necessarily those of the Fonterra Co-operative Group. http://www.fonterra.com/