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:
ROW_MOVEMENT
?ROW_MOVEMENT
?Any help would be highly appreciated. Thanks in advance!!
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.