Re: a couple of quick fragmentation questions

Wow. Some pretty heady, yet detailed stuff there. Honestly, I don't feel comfortable enough with this to manipulate the fragments like that. I do appreciate the creativity in it though. :-) I think since this table is so huge, we're going to re-evaluate the expression to make sure we have the best one, and hopefully NEVER have to do this again on this particular table. In that vein, I would be interested in hearing more about how to speed up the HPL process with the multiple gzip files, bearing in mind that we are on a San, with raid 10, or 1+0 ( I forget ) and are using Aix plaiding, all of which means, we really don't know where any data is on the disk. Thanks much. Floyd ======================== -<<Floyd Wellershaus>>- Database Administrator Unix Administrator email: fwellers@yahoo.com Home: 703-430-0805 Cell: 703-477-6045 ======================== http://www.one.org/ ----- Original Message ---- From: Jack Parker <jack.parker4@verizon.net> To: informix-list@iiug.org Sent: Friday, April 6, 2007 7:17:34 AM Subject: RE: a couple of quick fragmentation questions Sorry, been too wrapped up to contribute meaningfully to this thread. And then I meander around through this note as I check odds and ends - sigh. If you are going to use ATTACH, ensure that the schema of the to-be-attached table is identical, including constraints and defaults. Indices will be 'corrected' for you. The only way the index would not have to be corrected is if it where using the same fragmentation schema as your table. You are not doing that. All FOUR of your indices will have to be rebuilt (the with rowid is a 4th index). If you use Art's suggestion - at the end of the day (if I understand it correctly) you will have multiple fragments with overlapping expressions. You will want to alter the expression of the sthst_f6 fragment as well. It will work, but you will still have all this allocated space in sthst_f6 which you cannot recover. Let's go back to the core problem - you have data in a fragment (or partition) that you want to migrate to another set of fragments (or partitions (note that with v10 a partition is analogous to a fragment except that it you can have multiple parititions in a single dbspace)). You have three options - alter the fragmentation expression and sit back and watch the engine move data around. - unload/drop/recreate/reload using HPL (with internal format to/from gzip files - if you need help with this let me know). - create a new version of the table and insert into it from the source table (again HPL to pipe can speed this up) - an eterenal devotion to the pope, no wait 4 options. (ok, three options that come to mind). Using the alter strategy will move the data for you, but it will not clean up the dbspace that you are pulling the data out of. You will still have all of this allocated space in the originally overloaded dbspace that you cannot recover without detaching that fragment and rebuilding it. (detach fragment to a new table, unload that to a file, drop the fragment, recreate it, reload it, attach it back). Note that DETACH will drop your constraints, defaults and attached indices. For the space reason alone I would opt for a table rebuild strategy. How big is the table (in GB), divide that by the number of CPUs, divide again by 7 - that is your unload time to gzip files (in hours). SIZE/CPU/5 is your reload time. Probably faster - my rule of thumb is about 10 years old. If you have the space, then you could go for option three - build a new copy of the table in place, load it, drop the old one, rename the new one. Again you can use HPL for this and get a faster time than option 2. If you are happy with the other fragments and only want to work on the single portion of the table, then combine the strategies, detach the fragment in question to a new table, rebuild it with HPL, re-attach the new fragments. This give you the flexibility to work only with the subsection of data and still allows you to put stitch it back together cleanly. It is still going to have to rebuild the indices, which is going to take time. alter fragment on table (table) detach fragment (name) (new_table); (note we just lost all the not nulls and indices) HPL to unload in internal format to gzip files (spread across multipledisks). Create table (table) same schema, on disk where you want that fragment. HPL to reload from same files into new table, load only the range of stprofil_tokens that you want for this fragment. Repeat for each new fragment. alter fragment on table (table) ATTACH (detached table) as fragment (name) (expression) - I know I have the syntax wrong on this one. (you could create the new table with all the fragments you want to addpre-attached, I would not feel comfortable with this, been bitten too many times by features of which I was unaware). ----- You may also want to reverse the order order of your fragmentation scheme: (stprofil_token <= 6000000 ) in sthst_f01 , ((stprofil_token > 6000000 ) AND (stprofil_token <= 12000000 ) ) in sthst_f02 , ((stprofil_token > 12000000 ) AND (stprofil_token <= 19000000 ) ) in sthst_f3 , ((stprofil_token > 19000000 ) AND (stprofil_token <= 26000000 ) ) in sthst_f4 , ((stprofil_token > 26000000 ) AND (stprofil_token <= 33000000 ) ) in sthst_f5 , (stprofil_token > 33000000 ) in sthst_f6 If you are inserting a value of 12000001, then the engine will evaluate: Am I <= 6000000 (no) Am I > 6000000 (yes) Am I <= 12000000 (no) Am I > 12000000 (yes) Am I <=19000000 (yes) - put it here As opposed to: (stprofil_token <= 6000000 ) in sthst_f01 , ((stprofil_token <= 12000000 ) AND (stprofil_token > 6000000 ) ) in sthst_f02 , ((stprofil_token <= 19000000 ) AND (stprofil_token > 12000000 ) ) in sthst_f3 , (etc.) the engine will evaluate: Am I <= 6000000 (no) Am I <= 12000000 (no) Am I <= 19000000 (yes) Am I > 12000000 (yes) - put it here One less evaluation for each fragment checked. For that matter, if you are always inserting higher values of st_profil_token, you can move them to the top of your eval list so they get evaluated first: ((stprofil_token <= 19000000 ) AND (stprofil_token > 12000000 ) ) in sthst_f3 , ((stprofil_token <= 12000000 ) AND (stprofil_token > 6000000 ) ) in sthst_f02 , (stprofil_token <= 6000000 ) in sthst_f01 , (etc.) Am I <= 19000000 (yes) Am I > 12000000 (yes) - put it here - hit it on the first eval. j. -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org]On Behalf Of Art S. Kagel Sent: Thursday, April 05, 2007 7:27 PM To: informix-list@iiug.org Subject: Re: a couple of quick fragmentation questions Floyd Wellershaus wrote: > Thanks very much Jpreanaut. > You are right, I did want to avoid the full INIT. > You're