Re: no space after delete?

> 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! Your problem has to do with the way tables get allocated space. When you add rows to a table, an empty row is looked for in your table. If there is no room, an extent of space (that you may specify) is allocated to the table. When you delete, free slots are left in your table, but the extent remains intact, and dedicated to that table (for more free rows). In other words, deleting causes your table to look like swiss cheese (lots of empty rows). Overall pages allocated remain the same. > The next test: I consumed all table space (see 0 free Chunks from tbstat -d) > and I can no more data stored. Now delete _all_ datas on all tables - now > space! I also remove indexes - without better result. When I remove one table > self, in that moment the space is free. I use ESQL/C, but I have equal result That confirms the issue. Whe your drop a table, all of its extents are freed. When you delete a row (or drop an index) room in your table is made for new rows. > with SQL-statements over isql command. > dbexport/dbimport is not good idea - too many data. I must remove oldest > data and then store new data. What can I do? The bad news is that there is no easy solution to "shrinking" your tables back down, without unloading/re-extenting/and reloading (using dbload). There are several ways to just re-extent efficiently, but this shrinking problem has not been solved in 5.0 versions and older of Informix, though i'd be happy to stand corrected. I believe there is enough flexibility in the new distributed architecture in releases 6.0/7.0 to help with this problem, but we wont be on the new releases for quite awhile. For the time being, we let our tables stabilize to the maximum number of rows we expect at one time, set our extents carefully. In general, having ample disk space makes maintenance much easier. When I size a database, I like to leave room to copy my biggest table. This is not always feasible. If you had enough room to create a new table in your partition, you could extent it carefully, and copy rows from one table to another (with a select * from oldtable insert into newtable type scenario). Its fasterthan unloading to ascii files. If you choose this method, be sure to turn off logging and lock each table exclusively, so as not to overflow locks or logs. Naomi -- Naomi Walker (aka N7FSA) | naomi@anasazi.com | Phoenix, Arizona Everything should be made as simple | as possible, but not simpler --Einstein | Visualize Whirled Peas