Re: De-fragmenting tables. Some questions

In article <67cdtc$2hk@cssun.mathcs.emory.edu>, sujata_soman_at_omm- la2-infotech-001@internet.omm.com writes > >Hi all, > One of our heavily accessed tables is pretty badly fragmented and I need to >de-fragment it ( if such a word exists ). Before you ask, ours is an OLTP kind >of env, and so fragmenting is not such a good idea. Yes, defragment is a word and a good idea!. > 1. What is the best way to de-fragment. Should I create a whole new chunk >big enough to hold my table ( about 12 million rows, with rowsize = 144 ) and This = 1.6Gb as a raw size. An unload file may well be > 2Gb!! >then some ( to allow for growth ) ? In that case, can I de-allocate the space >that this table is currently using in the old chunk and re-use it in the new >chunk ?? But then, I am not sure we can reduce the size of a chunk, can we ( >though we can reduce the size of a logical volume ). No, you can't reduce the size of chunk directly. you have to first drop and then create a new chunk. > 2. Or am I supposed to re-arrange all the tables ( atleast the ones that are >hopelessly intermingled with this table ) across the existing chunks to keep >this table in one chunk and push the rest of the tables on to the remaining ? Sort of. > 3. Is there a third(fourth, fifth .. ) way ?? > - Unload each table to a file - Get a dbschema of the database - Drop the database - Edit the dbschema file and add in initial and next extent sizes which give you (the table +20% in the initial extent and a next extent size of 10%). - use the dbschema file to a) create a table. b)_load the table c) create any indexes on the table. - Then create any contraints - then create any stored procedures - Then create any triggers - Then run update statistics - Finally tbcheck and take another level 0 archive. >What are the pros/cons, and what precautions that I should take ( apart from a >backup ?! ) take 2 level-0 archives, incase one tape goes bad + a full backup of UNIX. Get as many people of the machine as possible. Clearly they cannot access the database whilst this is happening but also try to stop any non-database activity. The more the machine is in use, the slower the process will be. Consider running the table loads and index build with PDQPRIORITY=100% and set the enviroment variable PSORT_NPROCS to a suitable value. Do everything WITHOUT transaction logging. >Also, what is the difference in keeping this table on a dbspace vs a chunk of a >dbspace ?? > >As usual, thanks for all suggestions > >Sujata > > >******************************************************************************** >***** > Sujata Soman > > > E-Mail: ssoman@omm.com > > Database Administrator ( Informix ) > > O'Melveny & Myers, Los Angeles > >******************************************************************************** >****** > > > > > > > > > -- David Williams