Re: release after delete

"Sybrand Bakker" <gooiditweg_at_sybrandb.nospam.demon.nl> wrote in message news:qncnsvo2c9ic8pfn44j0kihjr0od91td23_at_4ax.com...
> On Mon, 1 Dec 2003 21:48:16 +0100, "zeb"
> <thierry.constant1.nospam_at_free.fr> wrote:
>
> >Hi
> >
> >Is it possible to release the space after a big delete ?
> >other than export/import
> >Thanks in advance
> >( oracle 8.1.7, HP-UX 11.0)
> >
>
>
> There are various strategies:
> - issue alter table <blah> deallocate unused manually after your
> delete. The deallocated extent must be completely empty
> - modify pctincrease and pctused so a block is being put on the free
> list sooner
> - other than that: copy the remaining rows, truncate the table, copy
> the rows back in.
>
> With Automatic Segment Storage Management in Oracle 9i this issue
> should become history

Why?

With ASSM, a bulk delete will still not free up any extents. You'll still be left with a lot of emtpy extents that I believe our original poster wants to see de-allocated from the segment altogether,

If you mean that the freed blocks within those extents will be used again sooner than they would be with old-fashioned freelist management, that's true enough... but is that such a good thing? ASSM has the effect of scattering inserts throughout the table, sure enough, instead of clobbering one block to death before moving on to the next. But you'd best pray you're not doing any full table scans if you think that's such a good idea.

HJR Received on Mon Dec 01 2003 - 16:28:56 CST