Re: De-fragmenting tables. Some questions

This is a multi-part message in MIME format. --------------EBDAD2FB2963F407A1C5580D Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit Volker: I think that if you have referential constraints created on the table, droping it will cause that all of them will be droped too. So, I suggest that you should alter an index on the table to clustered, since that all the data will be rewritten in the same order of that index and the unused space to be freed. ALTER INDEX <indexname> TO CLUSTER Volker Fraenkle wrote: } 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 --------------EBDAD2FB2963F407A1C5580D Content-Type: text/x-vcard; charset=us-ascii; name="vcard.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for Melvin Perez Content-Disposition: attachment; filename="vcard.vcf" begin: vcard fn: Melvin Perez n: Perez;Melvin org: CAM Informatica. S. A. email;internet: cam@codetel.net.do title: Technical Vicepresident tel;work: (809) 686-5574 tel;fax: (809) 686-5467 tel;home: (809) 227-2893 x-mozilla-cpt: ;0 x-mozilla-html: FALSE version: 2.1 end: vcard --------------EBDAD2FB2963F407A1C5580D--