Oracle row link (row chaining) and row migration (row migration)

Scenario: If varchar and VarChar2 are modified more often, and the modified data length is different each time, this can cause a "row migration" phenomenon

Concept:

Row chaining and row migration (row migration)
When a row of data is too long to be inserted into a single block of data, there are two things that can happen: row chaining or row migration (row migration).

< Span lang= "en-US" > When the first time a row is inserted, a row link occurs because the row is too long to fit in a block of data. In this case, Oracle uses one or more blocks of data that are linked to the block to hold the data for that row. Row joins often occur when large rows are inserted, such as data containing a long, long row, lob, and so on. In these cases the downlink link is unavoidable.

Row Migration
When you modify a line that is not a row link, a row migration occurs when the modified line is greater than the length of the line before the modification, and the free space in the block is small enough to fit the row's data completely. In this case, Oracle migrates the entire row of data to a new block of data, placing only one pointer to the original space of the row, pointing to the new location of the row, and the remaining space in the row's original space is no longer used by the database, which we call the void, which is the main reason for creating the table fragment , table fragments are fundamentally unavoidable, but we can reduce them to a level that we can accept. Note that even if a row migration occurs, the rowid of rows that have rows migrated will still not change, which is why row migrations can cause database I/O performance to degrade. The migration is a special form of the line link, but its cause and behavior are very different from the line link, so it is generally separated from the line link and processed separately.

< Span lang= "en-US" > row migration (row Migration) : Because pctfree  in table  are set too small, update  this table and make its president grow, it is possible that the remaining space of the block is not enough to store the row, The row migration occurs when Oracle migrates the row data to another block with enough free space, and the row rowid unchanged when a row migration occurs. A new pointer to the migrated block ID is added where the row was originally stored, so we will read the pre-and post-migration block, a table if there are a large number of rows occurred row migration, then it is necessary to view the table storage  set up to see if pctpree can be adjusted to a larger point.

Reasons for database performance degradation due to row link and row migration:
The cause of performance degradation is mainly caused by the extra I/O caused. When a row is accessed through an index, the database must scan more than one block of data to retrieve the row's data. Here are two main forms of expression:
1) Row migration or row chaining results in poor performance of INSERT or UPDATE statements because they require additional processing
2) SELECT statements that use indexes to query rows that have been linked or migrated perform poorly because they are performing additional I/O

cause: When update is updated with data that is larger than the data block's Pctfree value, you need to request a second block to form a row migration.

Prevention Methods: 1. Increase the pctfree of the data block; 2. Enlarge chunk size for table space

row link, row migration check:analyze table table name validate structure cascade into chained_rows;

row connection, row migration processing : You can reorganize the table in the EM manager.

Oracle Row link (row chaining) and row migration (row migration)