RE: Record deleted but space still not freed.

Better also to drop all other indexes first and rebuild afterwards. Even better to ensure you have enough log space to handle the reorg in a single transaction, else you get nasty things like a long transaction roll-back (so you are back to square one after many hours of nail-biting) or worse running out of log space completely if you have dodgy config parameters. Keith -> -----Original Message----- -> From: ART KAGEL, BLOOMBERG/ 65E 55TH [mailto:KAGEL@bloomberg.net] -> Sent: Friday, February 11, 2005 2:07 PM -> To: informix-list@iiug.org -> Subject: Re: Record deleted but space still not freed. -> -> -> -> It requires a table reorg to release unused space from -> within a table for reuse -> by other tables. Normally Informix holds that space for the -> table itself to -> reuse only. There are several ways to reorg, the dbexport, -> drop, dbimport is -> one. Alternatively you could use the ipload utility which -> would be faster to -> perform the unload and load. -> -> Another time honored method is to CLUSTER and index then -> change the index to NOT -> CLUSTER. The CUSTERing will sort the table by the index -> key into a new table -> and recreate the indexes. This frees the table's original -> space to the free -> pool and the new table will take up minimal space. -> -> The fastest method is to: -> -> ALTER FRAGMENT ON mytable INIT IN <dbspace or fragmentatio -> specification>; -> -> This works for fragmented or non-fragmented tables and you -> can even specify the -> same dbspace or fragmentation scheme the table is already -> using. It will cause -> the table to be compressed into the minimum number of -> extents and pages -> releasing all of the original pages to the free pool. Lock -> the table in -> exclusive mode before the ALTER (requires a BEGIN WORK) and -> COMMIT WORK to -> release the lock. -> -> Art S. Kagel -> -> ----- Original Message ----- -> From: Kh Teoh <khteoh2001@yahoo.com> -> At: 6/20 2:15 -> -> > Before I do record purging for a table, I keep a copy of -> "oncheck -pe" -> > result. After I purged the records. I keep another copy of "oncheck -> > -pe". Although there has been many records being deleted from the -> > table. Why there is no different on the output(free size -> column) from -> > "oncheck -pe"(before purge and after purge) for that -> particular table -> > ? The space will only be released when I do a dbexport, -> drop the table -> > then dbimport the table. Is there a command to release the deleted -> > record's space back to the dbspace pool ? Rather then doing the -> > dbexport/dbimport task. -> > -> > And also there are many free gaps in between the tables in the -> > "oncheck -pe" output. Will it affect the performance ? How -> to get rib -> > of the gaps(defragment) ? -> -> -> -> sending to informix-list -> sending to informix-list -> ********************************************************************************** This message is sent in strict confidence for the addressee only. It may contain legally privileged information. The contents are not to be disclosed to anyone other than the addressee. Unauthorised recipients are requested to preserve this confidentiality and to advise the sender immediately of any error in transmission. This footnote also confirms that this email message has been swept for the presence of computer viruses, however we cannot guarantee that this message is free from such problems. ********************************************************************************** sending to informix-list