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 second scheme definitely seems worthy of looking into and playing > with. > > I've got 3 huge tables that've gotten away from me in this way, so I'll > be interested in finding the best solution. > > Thanks again for your input and help. > > <SNIP> Floyd: Try creating a stand-alone table for each of the new extents. Alter the last extent to the new expression and attach each of the new ones. That might work. No time to test now, sorry .... Art S. Kagel

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 second scheme definitely seems worthy of looking into and playing > with. > > I've got 3 huge tables that've gotten away from me in this way, so I'll > be interested in finding the best solution. > > Thanks again for your input and help. > > <SNIP> Floyd: Try creating a stand-alone table for each of the new extents. Alter the last extent to the new expression and attach each of the new ones. That might work. No time to test now, sorry .... Art S. Kagel _______________________________________________ Informix-list mailing list Informix-list@iiug.org http://www.iiug.org/mailman/listinfo/informix-list

Well attaching should work, dependant on what version one has to set an undoc env var. (sorry dono anymore..) AND create check constraints on the tables to be attached!!!!!!!! This is pretty much doced in the perf guide V10. Check it out. Or you may want to reload the table using HPL. Superboer. On 6 apr, 01:26, "Art S. Kagel" <k...@bloomberg.net> wrote: > Floyd Wellershaus wrote: > > Thanks very much Jpreanaut. > > You are right, I did want to avoid the full INIT. > > You're second scheme definitely seems worthy of looking into and playing > > with. > > > I've got 3 huge tables that've gotten away from me in this way, so I'll > > be interested in finding the best solution. > > > Thanks again for your input and help. > > > <SNIP> > > Floyd: > > Try creating a stand-alone table for each of the new extents. Alter the > last extent to the new expression and attach each of the new ones. That > might work. No time to test now, sorry .... > > Art S. Kagel