| Lesson 5 | Enabling Row Movement |
| Objective | Describe how to enable Row Movement in a Partitioned Table |
Enabling Row Movement
Enabling row movement in a partitioned table using Oracle 11g allows records to be moved across different partitions when their partition key is updated. This can be useful in cases where the table has been partitioned based on some specific criteria and the records need to be updated in a way that would cause them to fall into a different partition.
The following steps provide a guide on how to enable row movement in a partitioned table:
Remember that enabling row movement will allow the records to move from one partition to another, based on the updates you perform on the partition key. This can affect the performance of your queries and applications, especially if the table is large, so it's a good idea to monitor the performance and adjust as necessary.
Also, when rows are moved across partitions, any global indexes on the partitioned table will be set to UNUSABLE. To maintain the validity of global indexes, you can use the UPDATE INDEXES clause while altering the table. For example:
- Confirm Current Row Movement Status:
Firstly, verify whether row movement is enabled or not. Run the following SQL command:
SELECT table_name, partitioned, row_movement FROM user_tables WHERE table_name = 'YOUR_TABLE_NAME';
Replace 'YOUR_TABLE_NAME' with the actual name of your table. If the row_movement column returns DISABLED, proceed to step 2. - Enable Row Movement: To enable row movement, you would need to alter your table as follows:
ALTER TABLE YOUR_TABLE_NAME ENABLE ROW MOVEMENT;
Replace YOUR_TABLE_NAME with the actual name of your table. This statement will enable row movement on your partitioned table. - Validate Row Movement Status: After enabling row movement, you should verify whether the operation was successful. Run the SQL command in step 1 again. If the operation was successful, the row_movement column should now return ENABLED.
Remember that enabling row movement will allow the records to move from one partition to another, based on the updates you perform on the partition key. This can affect the performance of your queries and applications, especially if the table is large, so it's a good idea to monitor the performance and adjust as necessary.
Also, when rows are moved across partitions, any global indexes on the partitioned table will be set to UNUSABLE. To maintain the validity of global indexes, you can use the UPDATE INDEXES clause while altering the table. For example:
ALTER TABLE YOUR_TABLE_NAME ENABLE ROW MOVEMENT UPDATE INDEXES;
Maintenance of Data
Once your table has been partitioned, you may come across a problem in the maintenance of the data.
Let us say that the partitioning is based on the value of the
Oracle has stopped the update because it would cause the row to belong to a different partition. Oracle has implemented a new feature, called row movement[1], which allows you to make this kind of update. When you complete the update, the row moves to the appropriate partition automatically. The new feature can be implemented when you first create the table (the
PUBLISHED_DATE of a CD or phonograph record. For example, when you insert a row with a date, the row is inserted into the first partition. Later, you discover that the date is incorrect. You attempt to update the date, but you get an error message: ORA-14402: updating partition key column would cause a partition change
Oracle has stopped the update because it would cause the row to belong to a different partition. Oracle has implemented a new feature, called row movement[1], which allows you to make this kind of update. When you complete the update, the row moves to the appropriate partition automatically. The new feature can be implemented when you first create the table (the
CREATE TABLE command) or you can implement it with an ALTER TABLE command. In the following diagram, you can see the syntax and examples of the CREATE TABLE command.
- Include optional storage clauses here such as TABLESPACE, PCTFREE, and PCTUSED.
- Define the partitions here.
- To allow row movement, add this clause to the end of the statement. The default is no row movement.
CREATE TABLE tablename (column1 datatype, column2 ..., physical storage clauses PARTITION BY RANGE (column list) (PARTITION partname1 VALUES LESS THAN (values list), PARTITION partname2 ...)) ENABLE ROW MOVEMENT
Include optional storage clauses here such as TABLESPACE, PCTFREE, and PCTUSED
CREATE TABLE WHOLESALE_ORDER (ORDER_ID NUMBER, CUST_ID NUMBER, LAST_PART_ORDERED NUMBER, ORDER_DATE DATE) TABLESPACE USERS PCTFREE 20 PARTITION BY RANGE (LAST_PART_ORDERED) (PARTITION LOW_PART VALUES LESS THAN (1000), (PARTITION MED_PART VALUES LESS THAN (50000), (PARTITION HIGH_PART VALUES LESS THAN (MAXVALUE)) ENABLE ROW MOVEMENT
Enable Row Movement
To change a partitioned table to allow row movement, use this command:
To change a partitioned table to allow row movement, use this command:
ALTER TABLE tablename ENABLE ROW MOVEMENT;
To change it back to not allowing row movement, use this command: ALTER TABLE tablename DISABLE ROW MOVEMENT;
The next lesson covers renaming, moving, and coalescing partitions.
[1]Row movement: The automatic shifting of a row from one partition to another when the value of the partitioning column in the row changes so that the row belongs in a different partition.
