Deletes/extents

We have gone through an update of Informix from 7.24 to 7.31 UC6 over the past week. Since the upgrade, we have run into an issue with deletes. When deleting whole tables, the extent size does not clean up, therefore filling dbspaces. Only way around this is to drop table and recreate it. I know this works, but seems a little archaic. _________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. Share information about yourself, create your own public profile at http://profiles.msn.com.

Informix does not, never has, released extents that contained deleted rows and became completely empty. The reason is that it assumes that the data will be replaced eventually so one should avoid releasing perfectly good contiguous space that may have to be reallocated anyway. To compress a newly emptied table do one of: 1) DROP TABLE....; CREATE TABLE....; 2) ALTER INDEX indexname_on_affected_table TO CLUSTER; (if already clustered, or another index is, first: ALTER INDEX clustered_indexname TO NOT CLUSTER;) 3) ALTER FRAGMENT ON TABLE tablename INIT IN dbspace_name_or_fragment_clause; (This can be done to the same dbspace(s) the table already resides in or can be used to move the table to another dbspace(s). Art S. Kagel Byrd Farmer wrote: > > We have gone through an update of Informix from 7.24 to 7.31 UC6 over the > past week. Since the upgrade, we have run into an issue with deletes. When > deleting whole tables, the extent size does not clean up, therefore filling > dbspaces. Only way around this is to drop table and recreate it. I know > this works, but seems a little archaic. > _________________________________________________________________________ > Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com. > > Share information about yourself, create your own public profile at > http://profiles.msn.com.