oracle-databaseoracle10g

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


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 we enable ROW_MOVEMENT?
  2. Are there any downsides of enabling ROW_MOVEMENT?
  3. What triggers row movement? How does oracle decide it needs to move ROWS?

Any help would be highly appreciated. Thanks in advance!!


Solution

  • 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.