What is the impact of enabling/disabling ROW_MOVEMENT in Oracle 10g?

Question

We are using oracle 10G and one of the table takes lot of time if we query/delete data. This table contains around 10 million records.

Recently we discovered that ROW_MOVEMENT is disabled on this table, we want to understand following:

1. What performance gain we can get if enable ROW_MOVEMENT ?
2. Are there any downsides of enabling ROW_MOVEMENT ?
3. What trigger's row movement ? How does oracle decides it needs to move ROWS ?

Any help would be highly appreciated.

Thanks in advance !!

Answer 1

Row movement is mainly applied to partition tables. It allows rows to be moved across partitions. With row movement disabled, which is the default, you can't move a row with an update:

SQL> CREATE TABLE part_table (ID NUMBER)
  2  PARTITION BY RANGE (ID)
  3     (PARTITION p0 VALUES LESS THAN (1),
  4      PARTITION p1 VALUES LESS THAN (MAXVALUE));

Table created

SQL> INSERT INTO part_table VALUES (0);

1 row inserted

SQL> UPDATE part_table SET ID = 2;

UPDATE part_table SET ID = 2

ORA-14402: updating partition key column would cause a partition change

When you allow row movement, you can move rows with an update:

SQL> ALTER TABLE part_table ENABLE ROW MOVEMENT;

Table altered

SQL> UPDATE part_table SET ID = 2;

1 row updated

This feature doesn't affect performance in most cases: the rows are stored and queried in exactly the same manner whether the feature is enabled or not. However, when row movement is enabled, the rows can be physically moved (similar to delete+insert) with ALTER TABLE SHRINK SPACE for example. This may in turn affect index cluster factor for instance, which may affect the performance of some queries.

Row movement is disabled by default because it implies that the rowid of a row may change, which is not the usual behaviour in Oracle.

Answer 2

Downside of row movement is that ROWIDs may be changed. So if you have any queries based on ROWID they may return wrong results