RE: Table has no rows but has lots of pages

Hi Chris. Have you tried UPDATE STATISTICS MEDIUM ? You may refer Information of sysmaster database at http://www.oninit.com/database/redirect.php?page=http://www.oninit.com/oninit/sysmaster/index.html HTH. -- Tsutomu Ogiwara from Tokyo Japan. ICQ#:168106592 >From: cbullivant@orange.net.au (Chris Bullivant) >Reply-To: cbullivant@orange.net.au (Chris Bullivant) >To: informix-list@iiug.org >Subject: Table has no rows but has lots of pages >Date: 2 Mar 2004 16:54:04 -0800 > >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? _________________________________________________________________ MSN 8 helps eliminate e-mail viruses. Get 2 months FREE*. http://join.msn.com/?page=features/virus sending to informix-list