[Oracle] Enable Row Movement

Enable Row Movement.

I’m trying to share one of my past experience with “row movement” in my early career days…

An Application deleted about 80 million rows (approx)  in a table where the table had just above 87 million rows. This was a routine activity for them at their business EOD.And they saw performance hiss for their reporting app which used to hit this table until their next business day begins and similar count of data gets populated.

Technically, this lead to many number of partial empty blocks below the HWM. And large amount of I/O was definite as it has to scan through the all the blocks pertaining to table until it reaches the HWM.

Shrinking the data  possible ?? Yes it is. In Oracle 9i version, we had to export and import the table back…. while 10g and over we have a amazing concept of doing this online   in just 3 steps. Enable the row movement on the table, shrink space cascade and followed by disabling the row movement.

• Alter table  <tab_name>  enable row movement;
• Alter table  <tab_name> shrink space cascade;
• Alter table  <tab_name>  disable row movement;

The pre-req’s for this activity would be the table should be in an  ASSM tablespace.

Let us try to understand what actually happens when the above operation is performed.

Oracle in background does deletes and inserts followed by filling up the empty spaces with the existing rows.Yes, this is bound to generate lots of redo and undo & significantly consumes lots of processing power. Also, the row id’s would change as the data is moved from bottom to the table to the top the table. So if there are any triggers on row id’s of the table … its time to be cautious

Benefits:

•   Less i/o overhead
•   Efficient FTS (As the HWM is come down)
•   Better Index range scans (as the data is less spread and HWM has come down)

It’s better practice to run the utlrp.sql once the shrink operation is completed.(This is due to the dependent objects on the table getting  invalid and since 11.2.0.3 there is an enhancement made, i.e… to get the dependent objects in a  valid state.