Tip#18 Rebuilding A Table

The DBA may have to rebuild a table after maintenance e.g. after a huge delete would like to lower the High Water Mark (HWM). There are several options to do it,

Option 1: Export and Import the table
Option 2: CTAS - Create table as Select
Option 3: Alter table MOVE (> 8.1.6)
Optoin 4: Alter table SHRINK (> 10g )

First two option are fairly straight forward so I wont go in the details here.

ALTER TABLE MOVE :

This one is my preferred option compare to the first two (if not already on 10g or above) and would like to point out few facts.

The ALTER TABLE MOVE command was implemented in 8.1.6. The command will allow tables to be moved from one tablespace to another, reorganise a table, provide the ability to modify of the INITIAL parameter. Also note that we don't lose grants (unlike CTAS), above all it is easy and fast.

Although it has a drawback, please note that when you use it, all the indices on the table become invalid, have to do an ALTER INDEX REBUILD.

e.g. I have a large table MBS with 50 million rows and now due to some process change I need to delete 25 million rows. After such huge delete obviously I would like to lower the HWM to avoid scanning those empty blocks below HWM.

Since I dont want to change the tablespace or initial parametere I would simply execute this,

Alter table MBS move; --tablespace test_tbs pctfree 20 pctused 40;

Since indices on the table will become invalid, I will also need to do this,

SELECT index_name FROM user_indexes WHERE table_name = 'MBS';

Once you know the indices used by the table just rebuild each of them as shown below,

Alter index pk_mbs rebuild; -- unrecoverable; --to avoid generating redo


ALTER TABLE SHRINK :

If you are on 10g or above, both of the above mentioned steps (table move and index rebuild) are done using single statement using SHRINK option.

alter table MBS enable row movement;
alter table MBS shrink space cascade;
alter table MBS disable row movement;

Just for comparison of Alter table options,

1.) deallocate unused : claims back unused space above HWM and doesnt lowers the HWM.
2.) move : claims back unused space below HWM and lowers the HWM
3.) shrink : claims back unused space below and above HWM and lowers the HWM

This blog was intended to just give you an overview, for more details on both the command please refer to the documentation. Also as usual always do backup before and after the changes.