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 ---------

You forgot to tell us your version. 1. Yes, the database has free pages which it will use for later inserts. 2. If you needed this space for the table once you will probably need it again so do not do anything. If you are adding rows I do not understand why you would want to reclaim the space which the table is just going to use again. 3. It depends upon how fast your server and disk are and how busy they are at the time. Once correctly sized we never touch tables again. It is the number of extents that you have not shown that is more important. MW -----Original Message----- From: informix-list-bounces@iiug.org [mailto:informix-list-bounces@iiug.org] On Behalf Of mohitanchlia@gmail.com Sent: Thursday, 19 July 2007 1:24 p.m. 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

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