Re: Data deletion in Informix

> Subject: Data deletion in Informix > Date: 19 Dec 1994 12:43:43 GMT > Reply-To: sgs@technet.sg (Alan Chen) > Organization: Technet, Singapore > > If I have done a mass deletion on tables, when I do a tbstat -d , I don't see > the space being free up. Is it the space was free logically but not > physically ?? Alan, Correct. Informix marks the deleted rows as available, and will re-use them to store new rows, before allocating new pages. However, it does not auto- matically release any free space, even if all rows in a page are deleted. One way to compact your data without doing an unload/reload is to alter a non-clustered index to clustered. This will copy the table to a new area, *in the order of the clustered index*, rename the new table to replace the old one, and then drop the old table. The data rows will be compacted in the process. If you already have a clustered index on the table, alter it to non-clustered and then alter it again to clustered, to start the process. Note that this process changes the value of systables.tabid for the table. Regards, Another Alan ___________________________ ______________________| R. Alan Popiel |__________________________ \\ Internet: | Martin Marietta, SLS | / \\ alan@den.mmc.com | P.O. Box 179, M/S 3810 | Std disclaimers apply. / )Voice: | Denver, CO 80201-0179 USA | ( / 303-977-9998 |___________________________| (But you knew that!) \\ /________________________) (____________________________\\