On Jul 18, 9:24 pm, mohitanch...@gmail.com wrote:
> 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're working at this too hard! IDS will reuse slots and pages from
deleted rows immediately for new data within the same table/fragment.
You would only have to release the enwly unused pages to the free pool
if you need to use that space for a DIFFERENT table or fragment.
Otherwise, just: insert, delete, insert, delete......

Art S. Kagel