Re: Shrink Table: IEstimating Rollback Space

  • From: Ian MacGregor <ian@xxxxxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 17 Oct 2008 08:40:36 -0700

Again , does that include the time taken to rebuild the indexes.

On 10/17/08 8:27 AM, "Tim Gorman" <tim@xxxxxxxxx> wrote:

> Insert operations are *ALWAYS* faster (i.e. using direct-path) and more
> scalable (i.e. using parallel) than update/delete operations; direct-path
> operations pretty much eliminate undo, and NOLOGGING option for direct-path
> can pretty much eliminate redo.  Rebuild is essentially an insert operation,
> same as move.  So, move/rebuild will absolutely take less elapsed time than
> shrink (unless the environment is short of CPU/IO resources);  the only thing
> in favor of ALTER TABLE ... SHRINK SPACE is the online capability.
> It's the age-old "speed vs flexibility" trade-off that permeates just about
> everything in computing.  It's nice to have a choice.
> Ian MacGregor wrote:
>>  Re: Shrink Table: IEstimating Rollback Space Move requires rebuilding of the
>> indexes.  I¹m nrt sure it will be quicker.
>> Ian
>> On 10/16/08 6:46 PM, "rjamya" <rjamya@xxxxxxxxx> <mailto:rjamya@xxxxxxxxx>
>> wrote:
>>> i haven't see a formula but there is a metalink note that says for larger
>>> tables ' alter table move' might be better since shrink is insert (in the
>>> beginning) and delete (from tail end) operation and hence takes longer and
>>> generated more redo.
>>> Raj
>>> On Thu, Oct 16, 2008 at 4:52 PM, Ian MacGregor <ian@xxxxxxxxxxxxxxxxx>
>>> <mailto:ian@xxxxxxxxxxxxxxxxx>  wrote:
>>>> I have a 450 GB table with 150 GB of free space.  I'd like to shrink the
>>>> table, but I'm not sure how much rollback will be needed.  Is there a
>>>> formula?  One that accounts for the indexes as well?
>>>> Ian MacGregor
>>>> SLAC National Accelerator Laboratory
>>> --

Other related posts: