Re: Help Deleting Records

When is Informix going to allow 'TRUNCATE TABLE tablename' ??? All the other guys have it... Also, 'CREATE TABLE tablename AS SELECT * from tablename1' is much needed... In article <4p3b3s$3s5@sparcserver.lrz-muenchen.de>, spitz@GANS2X.ana.med.uni-muenchen.deW says... > >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 | | >+----------------------------+-------------------------------------------+ -- ---------------------------------------------------------------------------- Glenn Travis Database Administrator (Ingres/Oracle/Sybase/Informix) Circuit City Stores, Inc. Richmond, VA USA ---------------------------------------------------------------------------