Yes there is a better way.

Fragment the tables by expression, according to your timeframe and 'delete'
schedule. When it comes time delete old data, detach the fragment (and then
drop it) and add a new fragment for the next period. With V10, this can be
done with partitions in the same dbspace.

1 - the 'delete' takes seconds.
2 - the space you free up is in fact freed up as contiguous space that can
be re-allocated to wherever you choose (typically back to the table).
3 - you leave no half empty pages in the table that may or may not be reused
over time.

Yes, #3 is really just an addendum to #2.

j.

-----Original Message-----
From: informix-list-bounces@iiug.org
[mailto:informix-list-bounces@iiug.org]On Behalf Of
mohitanchlia@gmail.com
Sent: Wednesday, July 18, 2007 9:24 PM
To: informix-list@iiug.org
Subject: Fragmented table, best way to free pages in DBspace


Because of the large size of the table we recently chose to fragment
by round robin into 8 dbspaces. After inserting lot of rows we ran our
delete process so that we free up the space. But it appears just
delete alone is not freeing up the space/pages. I looked at oncheck -
pT and pages are not being freed by delete alone. I read in the manual
that the best way is to run ALTER FRAGMENT ON TABLE T INIT FRAGMENT BY
ROUND ROBIN clause. So my question is:


1. If we don't run alter fragment would Informix know that these pages
are available and will use them anyway ?
2. In real time scenario we could insert 40+ million rows which are of
huge size. And as part of our process we delete them from the table
after certain timeframe. Now if we have to run alter fragment after
every delete then in production we are going to face a) downtime
because I think we need to disconnect any connections to database
before running alter fragment b) For 40+ M rows it's going to be
awfully long to do alter fragment.
3. How long does it take to execute this command.

Is there any better strategy ? How can we make it better.

Snippet from oncheck -pT
---------
Pagesize (k) 8
First extent size 1249999
Next extent size 249999
Number of pages allocated 10407781
Number of pages used 10407781
Number of data pages 55450
Number of rows 3380349
---------

_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list