Table has no rows but has lots of pages
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?
Chris Bullivant wrote: > > 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. Simple. The rows of the above table were deleted. Informix does not release the space when the row is deleted. Next time when rows are added in the above tables, Informix will first use the existing pages (unless you load data via HPL express mode).
On Tue, 02 Mar 2004 19:54:04 -0500, Chris Bullivant wrote:
As someone mentioned, the empty space may be the result of deleted rows that
were once there or the result of having created the table with a large
initial extent size. To release the space, if the extent size is small (see
dbschema ... -ss or myschema output or the ti_fextsiz ) you can do:
ALTER FRAGMENT ON mytable INIT IN <dbspacename or fragment expression>;
Which will release all but the initial extent for use by other tables. If the
initial extent size is large you must drop the table and recreate it with a
smaller initial extent size.
Art S. Kagel
> 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?