Row Movement Common Questions and Problems on Partitioned Tables

1. What is Row Movement?

The row movement lets you specify whether the database can move a table row. It is possible for a row to move, for example, during table compression or an update operation on partitioned data.
For more information

2. How to enable row movement?

Use the option below with ALTER TABLE statement
{ ENABLE | DISABLE } ROW MOVEMENT

Default value is disable. For complete syntax, go to "ALTER TABLE" in 3. Concepts / how it works internally?

When enable row movement was not set a rowid was assigned when you inserted a row and that never changed. After row movement is enabled a simple update could change your rowid.
When you "Enable Row Movement", you are just giving "Permission" to move a row.
You do not need to delete and insert, you just update the row, and that causes delete and insert, and in turn changes the ROWID.
So, delete and insert is the implicit operation which happens in the background.
Row movement is enabled at the table level, and is disabled by default.

4. Is it only for partitioned tables?

No, Row Movement is associated with partitioned as well as non-partitioned tables.
It is allowed for non-partitioned tables starting 9.2. It comes in to affect for non-partitioned tables when operations like table compression is performed.

5. Does it invalidate index?

No, it does not invalidate indexes

6. Does it create row chaining?

Row Movement will not cause Row Chaining. If the row doesn’t fit into a single data block, it must be chained. Row Chaining basically is the distribution of a single table row across multiple data blocks.
Row Movement is different as it updates the corresponding indexes—the ROWID actually changes. This has benefits on the long run, because the additional block read can be avoided in the TABLE ACCESS BY INDEX ROWID operation.

7. Does it have impact on performance?

Yes, it can cause performance problem as it can cause Oracle to move rows to discontinuous data blocks.
There could be some performance impact as it will necessarily consume processing resources on your machine while running.
The reason being it will:
        - read the table
        - delete/insert the rows at the bottom of the table to move them up
        - generate redo
        - generate undo

8. What are restrictions associated with "Enable Row Movement"?

You cannot specify this clause for a nonpartitioned index-organized table.
Tables need to be in an ASSM (Automatic Segment Space Management) tablespace for this to work.