Detailed description of Oracle row migration and row Link

Oracle will migrate the entire row of data to a new data block, and put only one pointer to the original space of the row, pointing to the new location of the row, and the remaining space of the row is empty.

Oracle will migrate the entire row of data to a new data block, and put only one pointer to the original space of the row, pointing to the new location of the row, and the remaining space of the row is empty.

Definition:

Row migration: Because of update, the rows become longer, the original blocks cannot be placed, the rows are moved into the new blocks,

Line link: The line is too large at the beginning. For example, if you insert a row of bytes, you must store the row in two parts,

Therefore, the row length can be used as a judgment basis.

Scenario:

Row migration

Oracle will migrate the entire row of data to a new data block, and put only one pointer to the original space of the row, pointing to the new location of the row, in addition, the remaining space of the row is no longer used by the database. We call the remaining space empty. This is the main reason for table fragmentation. Table fragmentation is also inevitable, but we can reduce it to an acceptable level. Note: even if a row migration occurs, the rowid of the row to be migrated remains unchanged. This is also the reason why the row migration will cause database I/O performance degradation.

Line Link

Oracle uses one or more data blocks linked to this block to accommodate the data of this row. Row connections often occur when large rows are inserted, such as long, long row, and lob data. In these cases, downstream links are inevitable.

Forms of impact on DB performance

Read two blocks in a row, that is, two logical reads.

Insert or update has poor performance.

The performance of select statements that query linked or migrated rows using indexes is poor because they require additional I/O

How can I detect row migration and row link?

In addition, how can we know tables with severe row migration (row link) problems?

The CHAINED_CNT column in The DBA_TABLES view, which contains the link row count of the table.

Although row migration and row link are two different things, they are considered the same thing in oracle. Therefore, when detecting row migration and row link, you should carefully analyze whether row migration or row link is currently being processed.