Sep 5, 2010

Which Command is more efficient: Move vs Shrink?

Common Advantages:

1) Reset the high water mark of a table.
2) Resolves the row-chaining.
3) “Alter table move” moves the rows to un-used space and reset the High-Water Mark.
4) Better index access takes place because of a smaller b-tree.
5) Chained-rows are repaired.
6) Can be done online.


a) Doesn't lock the objects in exclusive mode.
b) Executes in LOCKED_MODE=3 (Row Lock Mode), which enables recovering without stopping operations.

a) Locks the objects in Exclusive mode.
b) Executes in LOCKED_MODE=6 (exclusive mode).

Restrictions/Disadvantages on Shrink:

1) You cannot shrink following:
    a) UNDO segments
    b) Temporary segments
    c) Clustered tables
    d) Tables with a column of datatype LONG
    e) LOB indexes
    f) IOT mapping tables and IOT overflow segments
    g) Tables with MVIEWS with ON COMMIT
    h) Tables with MVIEWS which are based on ROWIDs
2) Possible in tablespace with ASSM (Automatic segment Space management)
3) Need full (exclusive) table lock.
4) Indexes will be left with the status unusable (because they contain the old rowids) and must be rebuilt.

Different ways of reclaiming the space:

1) Create table as select:
2) Export and Import (exp & imp or expdp & impdp)
3) Online re-org
4) Alter table shrink

More Details to come....

No comments: