What Is Row Migration And Row Chaining?
Row migration and row chaining are two known problems in Oracle that can degrade the database performance.
Row migration refers to the scenario where an entire row moves from one block to another, as it becomes unfit to the present block. Usually UPDATE statements results in row migration.
Let’s see how this happens.
Consider a row with a few null values. When we update these null values with non-null values, the block may fail to accommodate the entire row. Then, the row will migrate to another block , leaving behind the forwarding address.
This will not affect the performance in case of full table scan . During full table scan, oracle will ignore the forwarding address and continue with the scan as it can read the data from any of the other blocks. But in case of index read, this will result in an additional I/O.
To certain extend we can avoid row migration by setting an appropriate value for pct free to accommodate future updates.
Row chaining occurs when a single row is chained across different blocks. Normally INSERT statements result in row chaining.
Say, the db block size of our database is 4K and the size of the row to be inserted is 6K, then the row has to spread across 2 blocks as a single block cannot accommodate it.
In short, if the row size is greater that the block size, then it will result in row chaining.
Article Contributed By : Anju