Re: Table has no rows but has lots of pages

the safe way would be to do something like, "alter fragment on table TABLE init in DBSPACE", where the DBSPACE is the same dbspace that the table is already in -- this can be found using "dbschema -d DB -t TABLE -ss"; you can adjust the next extent size before doing this type of reorg by issuing "alter table TABLE modify next size KB" if the initial extent size is larger than you want (see dbschema), then you may have to do the reorg the old fashioned way.... and modify the schema 'next size' before recreating the table. cbullivant@orange. net.au (Chris To: informix-list@iiug.org Bullivant) cc: Sent by: Subject: Table has no rows but has lots of pages owner-informix-lis t@iiug.org 03/02/2004 07:54 PM Please respond to cbullivant I'm working on a project to reclaim space from inside a DB. I've written a simple script: select tabname, ti_nrows, ti_nptotal from sysmaster:systabinfo i,sysmaster:systabnames n where i.ti_partnum = n.partnum and tabname not like 'sys%' order by 2 Here is a few rows of output: tabname ti_nrows ti_nptotal Table 1 0 8 Table 2 0 1424 Table 3 0 8 Table 4 0 8 Table 5 0 55296 Table 6 0 8 Table 7 0 8 Table 8 0 4000 What is happening here? How can a table have 0 rows in it and yet claim 55K pages? Am I interpreting the output correctly. Is it possible to reclaim this space? I tried "alter index ... to cluster" on one of them but it made no difference. I could drop and re-create the table but I'm concerned that I won't get all the indexes, grants, triggers, etc. exactly right. Also, there are some other tables with only a few rows but are using lots of pages so the drop/recreate wouldn't work for them. What I'd like is a command that says "Collect Garbage". Does something like this exist? Thanks for any ideas, Chris P.S. Is there something on the Web that describes the tables and meanings of the sysmaster DB? sending to informix-list

Are you truely looking at just your tables? Remember, a detached index (the default in 9.x) is going to have space (nptotal) with a row count of 0 (nrows).