Re: Shrink Table: IEstimating Rollback Space

From: Tim Gorman <tim_at_evdbt.com>
Date: Fri, 17 Oct 2008 10:02:47 -0600
Message-ID: <48F8B727.7060909@evdbt.com>

Both operations manipulate both tables and indexes.  Move/rebuild means moving the table the table and rebuilding the indexes, and if you take advantage of PARALLEL and NOLOGGING in both, it will blow the doors off SHRINK SPACE.  Even if you don't take advantage of those.

Also, don't forget the time necessary to re-gather statistics on tables and indexes;  with REBUILD you can COMPUTE STATISTICS and save on that as well.



Ian MacGregor wrote:

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


On 10/17/08 8:27 AM, "Tim Gorman" <tim@evdbt.com> 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@gmail.com> <mailto:rjamya@gmail.com>  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@slac.stanford.edu> <mailto:ian@slac.stanford.edu>  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

-- http://www.freelists.org/webpage/oracle-l

-- http://www.freelists.org/webpage/oracle-l Received on Fri Oct 17 2008 - 11:02:47 CDT