RE: Shrink Table: IEstimating Rollback Space

If you use cascade the indexes will shrink, however I'm not certain this would be the ideal choice. In order to increase performance I'd drop the indexes, re-organize the table (dbms_redefinition or alter table move) and re-build the indexes.

hth

Guillermo Alan Bort
DBA / DBA Main Team

EDS, an HP company
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Hemant K Chitale Sent: Friday, October 17, 2008 11:47 AM
To: ian_at_slac.stanford.edu; oracle-l_at_freelists.org Subject: Re: Shrink Table: IEstimating Rollback Space

Undo (and Redo !) will be quite noticeable-- more so as you have indexes on the table.

A SHRINK operates as a DELETE (which will be the very significant portion of Undo) *and* an INSERT.
Both operations also have to update Indexes . The Indexes will not shrink until and unless you do a COALESCE or REBUILD. See MetaLink Note#577375.1 and Bug# 3888229.

Hemant K Chitale

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