Re: no space after delete?

Heiko Luebbe (Heiko_Luebbe@petnet.in-berlin.de) wrote: : Hi, : thanks for your interest. : I have a problem with free table space after delete on my first INFORMIX- : project. I create many tables (also with indexes), then I store many, many : data. Whenever 90 % table space consumed, I will remove oldest data. : I remove with "delete from ... where ...;". The data was deleted, but no more : space was free! OnLine dynamically allocates pages to a table as it grows, but unfortunately never frees them after delete (at least as of 5.01). Running "tbcheck -pT" confirms this. : dbexport/dbimport is not good idea - too many data. I must remove oldest : data and then store new data. What can I do? Dropping tables releases their free space. This can be done by some kind of unload/drop/create/load sequence, but this is clumsy. An alter index statement is sometimes a convenient trick: ALTER INDEX index-name TO CLUSTER where index-name is any index of the table, but prefereably one that gets the most use. This basically rebuilds the entire table in place, by physically reordering the data rows in index order. Be careful: If your database has logging turned on, you must have enough logspace available to complete this statement or it will fail. This could be quite large depending on how much data the table contains after delete. : Heiko :) : hlu@petnet.in-berlin.de : +49 30 5425978 -- Jeff Sturm