Reclaiming space from deleted records
I have a table which had grown to rather ungainly size, so I have been trimming it by deleting data for the last couple of weeks. The table has gone from some 40 million rows to 10338595 rows (5 columns, 39 bytes per row). Alas, this does not seem to have freed up any chunk space. What do I have to do to recover this space ? This is Informix Dynamic Server Version 9.30.UC1, running on SunOS 5.8 Generic_108528-22 sun4u sparc SUNW,Ultra-60. Thanks for any tips, RTFMs, etc. ! Greg Williamson DBA GlobeXplorer LLC
Greg The table from which you have removed the records will now have 'gaps' which can be filed by new records being inserted into this table. To completely recover the space to allow it to be used by any table you will need to reorganise this table. Either: 1 - unload, drop recreate, load, reindex this table 2 - drop all except the PK index then alter index to clustered followed by recreate of other indexes (requires enough space to hold two copies of the table while running) 3 - other mechanism are useable, check c.d.i Keith -> -----Original Message----- -> From: Gregory S. .... [mailto:gsw@globexplorer.com] -> Sent: Monday, May 30, 2005 11:05 PM -> To: ids@iiug.org -> Subject: Reclaiming space from deleted records [5056] -> -> -> I have a table which had grown to rather ungainly size, so I -> have been trimming it by deleting data for the last couple -> of weeks. The table has gone from some 40 million rows to -> 10338595 rows (5 columns, 39 bytes per row). -> -> Alas, this does not seem to have freed up any chunk space. -> -> What do I have to do to recover this space ? -> -> This is Informix Dynamic Server Version 9.30.UC1, running on -> SunOS 5.8 Generic_108528-22 sun4u sparc SUNW,Ultra-60. -> -> Thanks for any tips, RTFMs, etc. ! -> -> Greg Williamson -> DBA -> GlobeXplorer LLC -> -> ******************************************************************************** ** 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. ******************************************************************************** **
Gregory S. .... said: > I have a table which had grown to rather ungainly size, so I have been > trimming it by deleting data for the last couple of weeks. The table has > gone from some 40 million rows to 10338595 rows (5 columns, 39 bytes per > row). > > Alas, this does not seem to have freed up any chunk space. > > What do I have to do to recover this space ? > > This is Informix Dynamic Server Version 9.30.UC1, running on SunOS 5.8 > Generic_108528-22 sun4u sparc SUNW,Ultra-60. > > Thanks for any tips, RTFMs, etc. ! IDS will just re-use the space. If you want to compact the table, you need to either unload/reload or use ALTER FRAGMENT ... INIT IN ... to rearrange your data. PS your version of IDS is unsupported and far too bloody old. -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule" - Coluche A smile is a gift that is free to the giver and precious to the recipient. But giving someone the finger is free too, and I find it more personal and sincere.
Thank you for the advice ... yes, it is old, a legacy sytem on its last legs, I think. G -----Original Message----- From: Obnoxio The Clown [mailto:obnoxio@serendipita.com] Sent: Tue 5/31/2005 2:42 AM To: Gregory S. Williamson Cc: ids@iiug.org Subject: Re: Reclaiming space from deleted records [5056] Gregory S. .... said: > I have a table which had grown to rather ungainly size, so I have been > trimming it by deleting data for the last couple of weeks. The table has > gone from some 40 million rows to 10338595 rows (5 columns, 39 bytes per > row). > > Alas, this does not seem to have freed up any chunk space. > > What do I have to do to recover this space ? > > This is Informix Dynamic Server Version 9.30.UC1, running on SunOS 5.8 > Generic_108528-22 sun4u sparc SUNW,Ultra-60. > > Thanks for any tips, RTFMs, etc. ! IDS will just re-use the space. If you want to compact the table, you need to either unload/reload or use ALTER FRAGMENT ... INIT IN ... to rearrange your data. PS your version of IDS is unsupported and far too bloody old. -- Bye now, Obnoxio "C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule" - Coluche A smile is a gift that is free to the giver and precious to the recipient. But giving someone the finger is free too, and I find it more personal and sincere. !DSPAM:429c3025179375089917554!