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.
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.
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.
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).
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.