Re: Shrink Table: IEstimating Rollback Space

From: Tim Gorman <>
Date: Fri, 17 Oct 2008 09:27:00 -0600
Message-ID: <>


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.

On 10/16/08 6:46 PM, "rjamya" <> 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.


On Thu, Oct 16, 2008 at 4:52 PM, Ian MacGregor <> 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
-- Received on Fri Oct 17 2008 - 10:27:00 CDT

Original text of this message