Re: Record deleted but space still not freed.
It requires a table reorg to release unused space from within a table for reuse
by other tables. Normally Informix holds that space for the table itself to
reuse only. There are several ways to reorg, the dbexport, drop, dbimport is
one. Alternatively you could use the ipload utility which would be faster to
perform the unload and load.
Another time honored method is to CLUSTER and index then change the index to NOT
CLUSTER. The CUSTERing will sort the table by the index key into a new table
and recreate the indexes. This frees the table's original space to the free
pool and the new table will take up minimal space.
The fastest method is to:
ALTER FRAGMENT ON mytable INIT IN <dbspace or fragmentatio specification>;
This works for fragmented or non-fragmented tables and you can even specify the
same dbspace or fragmentation scheme the table is already using. It will cause
the table to be compressed into the minimum number of extents and pages
releasing all of the original pages to the free pool. Lock the table in
exclusive mode before the ALTER (requires a BEGIN WORK) and COMMIT WORK to
release the lock.
Art S. Kagel
----- Original Message -----
From: Kh Teoh <khteoh2001@yahoo.com>
At: 6/20 2:15
> Before I do record purging for a table, I keep a copy of "oncheck -pe"
> result. After I purged the records. I keep another copy of "oncheck
> -pe". Although there has been many records being deleted from the
> table. Why there is no different on the output(free size column) from
> "oncheck -pe"(before purge and after purge) for that particular table
> ? The space will only be released when I do a dbexport, drop the table
> then dbimport the table. Is there a command to release the deleted
> record's space back to the dbspace pool ? Rather then doing the
> dbexport/dbimport task.
>
> And also there are many free gaps in between the tables in the
> "oncheck -pe" output. Will it affect the performance ? How to get rib
> of the gaps(defragment) ?
sending to informix-list
sending to informix-list