Row Chaining and Migrating in Oracle

Back to: Oracle DBA Tutorials

Row Chaining and Migrating in Oracle

In this article, I will discuss Row Chaining and Migrating in Oracle with Examples. Please read our previous article discussing the Availability and Optimization of Free Space in Oracle. In two circumstances, the data for a row in a table may be too large to fit into a single data block.

Case 1: The row is too large to fit into one data block when it is first inserted. In this case, Oracle stores the data for the row in a chain of data blocks (one or more) reserved for that segment. Row chaining most often occurs with large rows, such as rows that contain a column of datatype LONG or LONG RAW. Row chaining in these cases is unavoidable. Oracle sometimes cannot insert one record or one big record at a time.

Case 2: A row that originally fit into one data block is updated so that the overall row length increases, and the block’s free space is already completely filled. In this case, Oracle migrates the data for the entire row to a new data block, assuming the entire row can fit in a new block. Oracle preserves the original row piece of a migrated row to point to the new block containing the migrated row. The rowid of a migrated row does not change.

Note: When a row is chained or migrated, I/O performance associated with this row decreases because Oracle must scan more than one data block to retrieve the information for the row.

Example: If we perform an insert statement for one row, the row will have data, and the data is stored in three blocks. When we retrieve the data, Oracle needs to retrieve one row from three different blocks. So this will increase the workload and scan of blocks, which, in the end, decreases the performance of the database.

In the next article, I will discuss the Types of Segments in Oracle. In this article, I explain Row Chaining and Migrating in Oracle with Examples. I hope you enjoy this Row Chaining and Migrating in Oracle article.