Re: De-fragmenting tables. Some questions

Download my "dbreorg" program from the IIUG archives. It generates all the SQL code needed to unload, drop, create, & load data. It also rebuilds constraints, views, triggers, etc. DAS -- David Snyder @ Snide Computer Services - Folcroft, PA EMAIL: (h) dave@snide.com (w) snyded@towers.com WEB: http://www.ece.vill.edu/~dave/ (Now enhanced w/Frames!) "Peter Tashkoff" <TashkoP@kiwi.co.nz> wrote in article <68bns4$emr@cssun.mathcs.emory.edu>... > This is what I use; > set indexes,constraints on table_name disabled; > alter fragment on table table_name init in dbspace_name;> set indexes, constraints on table_name enabled; > > I usually move it to a different dbspace when i do this, but I recall that = > it does work when being written back to the same dbspace. > Note the table does not have to be =5Binformix=5D fragmented for this to = > work. > > This is also the method I use when moving tables to free up space or to = > balance i/o. > rgds > > > Peter Tashkoff <tashkop=40iname.com> > Zespri International Limited Std Disclaimers Apply > All rights reserved. No party may use this document to vilify another. > > Zespri New Zealand Kiwifruit, The World=27s Finest > > > >>> =22Dave Otto=22 <dotto=40themoneystore.com> 31/12/97 05:41:57 >>> > Typically, it is substantially faster to do this entirely within Informix. > > create table tmp_t1..... > (=5E unindexed )> > insert into tmp_t1 > select * from t1;> > rename table tmp_t1 t1; > > create index/procedure/etc. > > Depending on you table fragmentation and temp DBspaces, this > should run 2-5 times faster than running the data to disk > > You can add an =22order by=22 clause to =22cluster=22 the data. > Note that if there are any dependencies (FKs, Multi-table views, > etc.) you will need to drop these also and re-declare them. > > -d > > >Volker Fraenkle wrote in message <19971230.13191334=40ff>... > >The easiest way to defragment a table is: > > > >- unload table to disk > >- drop table > >- create table > >- load table from disk > >- create indexes > > > >If you do not drop the table, then the reserved pages of your table > >will not be freed. > > > >You can not build a table in a special chunk. You have to create a > >seperate dbspace for it. > > > >NOTE: After reloading your table, you table locklevel is page not row=21 > > > >Hope that helps, > >Volker > > > > > >