On Jul 19, 10:05 pm, "Jack Parker" <jack.park...@verizon.net> wrote:
> I cannot agree with you on this one Art.
>
> A 40 million row delete is a multi-hour process.
>
> Granted, the space will be available for reuse quickly if the insert/delete
> scheme is date based. If there is some difference in the two schemes, then
> deleted row space may not necessarily free up, the page in question would
> have to drop below half-deleted, then go through a compression, then the
> space would be available again. But I digress.
>
> Certainly an alter fragment detach is not going to be happy with others
> holding even a shared lock on the table, we tried it with a lock mode wait,
> until we discovered that there was a process that started, opened a cursor
> to the table and then stayed there forever. We had to stop that process, do
> the purge and then start it back up again.
>
> Other things we ran into:
> - prepared statements have to be re-prepared - the table layout has
> changed.
> - detach/drop table/attach will not be happy with FK/PK constraints.
> - indexes (to detach cleanly) must have the same frqagmentation
> strategy - i.e. date.
> - ROWIDs cause an underlying index which does not have the date in it and
> will take a long time to rebuild.
>
> All of those are time sinks to work around or rebuild. Without those, a
> detach/drop table/attach takes about 1 second on a rinky-dink machine. The
> number of rows is immaterial to the length of time necessary to perform the
> operation.
>
> If our friend is a 24x7 shop and cannot go down momentarily occasionally,
> then yes he is stuck with a delete scenario. He could speed that up with a
> nibble strategy:
>
> build a temp table of identifying keys (empty)
> Stuff 10K keys to be deleted from the target table into the temp table
> delete from target table where key in (select key from temp_table)
> Rinse and repeat until there are no more.
>
> My .02
>
> j.
>
> -----Original Message-----
> From: informix-list-boun...@iiug.org
>
> [mailto:informix-list-boun...@iiug.org]On Behalf Of Art S. Kagel
> Sent: Thursday, July 19, 2007 7:50 PM
> To: informix-l...@iiug.org
> Subject: Re: Fragmented table, best way to free pages in DBspace
>
> 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......


Jack, he can't use fragment DETACH, he's using ROUND ROBIN
fragmentation, not fragment by expression to isolate his older data.
If her were, I'd agree. Meanwhile, there's no argument here. He is
already deleting the rows he needs to delete (yes different
fragmentation would make that easier) he just doesn't understand how
IDS works and how it deals with deleted row space and whether or not
it's reused. I'm confident that I answered the question. Aside, your
implied suggestion to refragment by expression is a VERY good one, and
I didn't look beyond the question at hand to see that it should be
made.

Art S. Kagel