What is the difference between row chaining and row migration?

 Overview

If you notice poor performance in your Oracle database Row Chaining and Migration may be one of several reasons, but we can prevent some of them by properly designing and/or diagnosing the database. Row Migration & Row Chaining are two potential problems that can be prevented. By suitably diagnosing, we can improve database performance.

Migrated rows affect OLTP systems which use indexed reads to read singleton rows. In the worst case, you can add an extra I/O to all reads which would be really bad. Truly chained rows affect index reads and full table scans.

Figure 1-1 Oracle Block

Row Chaining

If the row is too large to fit into an empty data block in this case the oracle stores the data for the row in a chain of one or more data blocks, which can occur when the row is inserted.For example, if you use a 4KB blocksize for your database, and you need to insert a row of 8KB into it, Oracle will use 3 blocks and store the row in pieces. 

                                                    Figure 1-2 Row Chaining

Some conditions that will cause row chaining are: 

• Tables whose rowsize exceeds the blocksize. 
• Tables with LONG and LONG RAW columns are prone to having chained rows. 
• Tables with more than 255 columns will have chained rows as Oracle breaks wide tables up into pieces. 

So, instead of just having a forwarding address on one block and the data on another we have data on two or more blocks. Chained rows affect us differently. Here, it depends on the data we need. If we had a row with two columns that were spread over two blocks, the query:

1. SELECT column1 FROM table where column1 is in Block 1, would not cause any table fetch continued row. It would not actually have to get column2, it would not follow the chained row all of the way out. 
2. On the other hand, if we ask for: SELECT column2 FROM table and column2 is in Block 2 due to row chaining, then you would in fact see a table fetch continued row.

Row Migration

An update statement increases the amount of data in a row so that the row no longer fits in its data blocks. Now the oracle tries to find another free block with enough space to hold the entire row if such a block is available oracle moves the entire row to the new block. We will migrate a row when an update to that row would cause it to not fit on the block anymore (with all of the other data that exists there currently). A migration means that the entire row will move and we just leave behind the forwarding address. So, the original block just has the rowid of the new block and the entire row is moved. 

Figure 1-2 Row Migration

1. Full Table Scans are not affected by migrated rows : 

The forwarding addresses are ignored. We know that as we continue the full scan, we'll eventually get to that row so we can ignore the forwarding address and just process the row when we get there. Hence, in a full scan migratedrows don't cause us to really do any extra work -- they are meaningless.

2. Index Read will cause additional IO's on migrated rows :

        When we Index Read into a table, then a migrated row will cause additional IO's. That is because the index will tell us to go to file X, block Y, and slot Z to find this row. But when we get there we find a message that says well, really go to file A, block B, slot C to find this row. We have to do another IO (logical or physical) to find the row.  

Please feel free to ask. thank you 🙂
Toufique Khan