Wednesday, August 25, 2010

Oracle Tips: Table Reorg

The most efficient way to re-organize tables in 11.2 is
alter table <table_name> move;

The above command preserves all constraints and index definitions.
Of course, indexes based on row_id would be impacted. 
But its important to verify if any of the indexes on the table are UNUSABLE. If so rebuild the indexes as below
Alter index <index_name> rebuild tablespace <tablespace_name> ;

Another way I came across but have not tried is

alter table <table_name> enable row movement;
alter table <table_name> shrink space;

This is supposed to repack the rows and bring the High Water Mark down.


No comments: