reclaim table space

jesolom@pbrock.srv.PacBell.COM (Rock Solomon) writes: > I have several very large tables that I would like to shrink (delete the rows > that have already been "marked" for deletion"). First of all, is there a way > to tell how many of these blank rows exist in a table. Also, are there any > tools to shrink tables, rather than modifying it, then changing it back to > it's original form? > > This is for the standard engine.... This one should go in the FAQ, after everyone has flamed it to a toasty consistency... Reclaiming space in an empty extent References: Informix "OnLine Administrator's Guide" v5.0 pg 2-107. Joe Lumbley's "INFORMIX DBA Survival Guide", chapter 7. any more references? There are two ways to do this: 1) If the table has an index, use SQL to ALTER INDEX TO CLUSTER. This will physically re-arrange the data in the table to match the index, freeing up unused extents space along the way. This method requires free dbspace >= (tablesize after purging unused rows). 2) Create a schema for the table (OnLine versions <6 remember to alter the schema to include dbspace, locking, and extent sizing info), unload the table to a file, drop the table, re-create the table from the schema, reload the table. BUT... You should rarely have to! Under normal operations, an application will add to a table, and purge from a table, keeping a set of data which has a consistent size. All of your tables will have a max and min size, any you may as well leave the extents sized for the max size and not have to worry about reclaiming extents. If you are having to reclaim space very often then you need to size your tables smaller and/or purge more often. After some period of time (depends on data volatility) you should do one of the above just to keep your data from looking like swiss cheese, but that's a different issue. Also see the discussion about sizing extents... __________________________________________________________________ | Clem Akins Standard Disclaimers Apply | |Reynolds Metals Co, Alloys Plant "Climb High, Cave Deep!" | | Muscle Shoals, Alabama USA cwakins@leia.alloys.rmc.com | |________________________________________________________________|