Re: Help Deleting Records

Frits Schouten (schouten.jf.frits@bhp.com.au) wrote: : You can however, let Informix free up the deleted : records. : It goes something like this: : First you run your DELETE FROM WHERE.... That's ok. DELETE only marks the space used by the deleted records as available, but doesn't reduce the file size of the .dat file. New records, however, will be inserted using the available space, and the .dat file will not grow until the number of inserted records exceeds the number of deleted records. : Next you run ALTER TABLE tablename MODIFY fieldname format : (format must differ from the original format of fieldname! : like from 'small float' to 'float') : Informix will recreate your table without the deleted records. : The last thing you have to do is running the same query again but now : with the original field format. IMHO, this is way too complicated, although it works. Of course, you must make sure that compatible data types are used! A much easier way is to issue "ALTER INDEX indexname TO CLUSTER". This will physically rearrange the records in the order of the index and necessitates a rewrite of the .dat file. As in the above solution, there MUST be enough space in the file system to duplicate the .dat file, since first a new .dat file is written, then the old one is deleted and the new one renamed to the name of the old file. Hope this helps, Richard -- +----------------------------+-------------------------------------------+ | Dr. Richard Spitz | INTERNET: spitz@ana.med.uni-muenchen.de | | EDV-Gruppe Anaesthesie | Tel : +49-89-7095-3413 | | Klinikum Grosshadern | FAX : +49-89-7095-8886 | | 81366 Munich, Germany | | +----------------------------+-------------------------------------------+