What’s the Difference between Row Migration and Row Chaining?

In Oracle Database migrated and chained rows are often confused. In my opinion, this is for two main reasons. First, they share some characteristics, so it’s easy to confuse them. Second, Oracle, in its documentation and in the implementation of its software, has never been very consistent in distinguishing them. So, it’s essential to understand the differences between the two.

When rows are inserted into a block, the database engine reserves some free space for future updates. You define the amount of free space reserved for updates by using the PCTFREE parameter. To illustrate, I inserted six rows in the block depicted in the next figure. Since the limit set through PCTFREE has been reached, this block is no longer available for inserts.

Inserts leave some free space for future updates

When a row is updated and its size increases, the database engine tries to find enough free space in the block where it’s stored. When not enough free space is available, the row is split into two pieces. The first piece (containing only control information, such as a rowid pointing to the second piece) remains in the original block. This is necessary to avoid changing the rowid. Note that this is crucial because rowids might not only be permanently stored in indexes by the database engine, but also be temporarily stored in memory by client applications. The second piece, containing all the data, goes into another block. This kind of row is called a migrated row. For example, in the following figure, row 4 has been migrated.

Updated rows that can no longer be stored in the original block are migrated to another one

When a row is too big to fit into a single block, it’s split into two or more pieces. Then, each piece is stored in a different block, and a chain between the pieces is built. This type of row is called a chained row. To illustrate, the next figure shows a row that is chained over three blocks.

A chained row is split into two or more pieces

There’s a second situation that causes row chaining: tables with more than 255 columns. In fact, the database engine isn’t able to store more than 255 columns in a single row piece. Consequently, whenever more than 255 columns have to be stored, the row is split into several pieces. This situation is particular, in that several pieces belonging to the same row can also be stored in a single block. This is called intra-block row chaining. The following figure shows a row with three pieces (since it has 654 columns).

Tables with more than 255 columns might cause intra-block row chaining

Note that migrated rows are caused by updates, while chained rows are caused by either inserts or updates. When chained rows are caused by updates, the rows might be migrated and chained at the same time.