Quick question:

I have a simple database with a table that has a BLOB field. There are no
blobspaces.

When I delete a row from the table with the BLOB field, the space used in
the chunk as reported by onstat -d does not go down.

Is space permanently allocated to BLOBs when you don't have a blobspace
for them?

--
John Hardin KA7OHZ <johnh@aproposretail.com>
Internal Systems Administrator voice: (425) 672-1304
Apropos Retail Management Systems, Inc. fax: (425) 672-0192
-----------------------------------------------------------------------
Failure to plan ahead on someone else's part does not constitute an
emergency on my part.
- David W. Barts in a.s.r

John Hardin wrote:

> Quick question:
>
> I have a simple database with a table that has a BLOB field. There are no
> blobspaces.
>
> When I delete a row from the table with the BLOB field, the space used in
> the chunk as reported by onstat -d does not go down.
>
> Is space permanently allocated to BLOBs when you don't have a blobspace
> for them?

No, but it is permanently allocated to the table - until you alter its
fragmentation, or alter an index to cluster, or do an alter table that
cannot be done in place.


--
Jonathan Leffler #include <disclaimer.h>
Email: jleffler@earthlink.net, jleffler@us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

This is expected behaviour. The space is allocated to the "tablespace"
in extents, not on a per row basis. Space is not freed up from the
tablespace as data is deleted.

Extents are generally bigger than 1 row, but space isn't even freed up
when a whole exetnt is empty

The theory is that the table will probably need this space again,
tables generally don't shrink.So to save time allocating the space to
the table again later Informix doesn't remove it.

If you have run a large delete and do want to reclaim the tablespace
that was allocated to the table, if you cluster one of your indexes so
that the data is physically re ordered then this will free up the
unused space.

Most if not all of this is covered in the Informix System
Administrators Course, which I would recommend if you have not already
been on it.


"John Hardin" <johnh@aproposretail.com> wrote in message news:<pan.2004.05.21.23.53.39.40504@aproposretail. com>...
> Quick question:
>
> I have a simple database with a table that has a BLOB field. There are no
> blobspaces.
>
> When I delete a row from the table with the BLOB field, the space used in
> the chunk as reported by onstat -d does not go down.
>
> Is space permanently allocated to BLOBs when you don't have a blobspace
> for them?