Re: De-fragmenting tables. Some questions

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=20 will not be freed. You can not build a table in a special chunk. You have to create a=20 seperate dbspace for it. NOTE: After reloading your table, you table locklevel is page not row! Hope that helps, Volker >>>>>>>>>>>>>>>>>> Urspr=FCngliche Nachricht <<<<<<<<<<<<<<<<<< Am 19.12.97, 01:08:12, schrieb=20 sujata_soman_at_omm-la2-infotech-001@internet.omm.com zum Thema=20 De-fragmenting tables. Some questions: >=20 > 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=20= OLTP kind > of env, and so fragmenting is not such a good idea.=20 > 1. What is the best way to de-fragment. Should I create a whole=20= new chunk > big enough to hold my table ( about 12 million rows, with rowsize =3D = 144 ) and > then some ( to allow for growth ) ? In that case, can I de-allocate=20= the space > that this table is currently using in the old chunk and re-use it in=20= the new > chunk ?? But then, I am not sure we can reduce the size of a chunk,=20= can we ( > though we can reduce the size of a logical volume ). > 2. Or am I supposed to re-arrange all the tables ( atleast the=20= ones that are > hopelessly intermingled with this table ) across the existing chunks=20= to keep > this table in one chunk and push the rest of the tables on to the=20 remaining ? > 3. Is there a third(fourth, fifth .. ) way ?? >=20 > What are the pros/cons, and what precautions that I should take (=20 apart from a > backup ?! ) > Also, what is the difference in keeping this table on a dbspace vs a=20= chunk of a > dbspace ?? >=20 > As usual, thanks for all suggestions >=20 > Sujata >=20 >=20 >=20 ********************************************************************* ** ********* > ***** > Sujata Soman = =20 =20 > = =20 =20 > =20 > E-Mail: ssoman@omm.com = =20 =20 > =20 > Database Administrator ( Informix ) = =20 =20 > =20 > O'Melveny & Myers, Los Angeles = =20 =20 > =20 >=20 ********************************************************************* ** ********* > ****** >=20 >=20 >=20 >=20 >=20 >=20 >=20 >=20 >=20