Row chaining or row migration)

Row chain: When a row is too large to fit into any block, row chaining occurs. in this case, the Oracle devide the row into smaller chunks. each Chunk is stored in a block along with the necessary poiters to retrive and assemble the entire row.

Row migration: When a row is to be updated and it cannot find the necessary free space in its block, the Oracle will move the entire row into a new block and leave a pointer from the orginal block to the new location. this process is called row migration.

Row chaining and row migration)
When a row of data is too long to be inserted into a single data block, two possible events may occur: Row chaining or row migration ).

Line Link
When the row is inserted for the first time, because the row is too long to be contained in a data block, a row link occurs. In this case, Oracle uses one or more data blocks linked to the block to accommodate the data of the 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.

Row migration
When the modified row is not a row link, when the modified row length is greater than the length of the row before the modification, and the free space in the data block is small, it cannot fully accommodate the data of the row, row migration will occur. In this case, 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. In fact, row migration is a special form of row link, but its cause and behavior are very different from that of the row link. Therefore, it is generally independent from the row link and processed separately.
Reasons for database performance degradation caused by row link and row migration:
The main cause of performance degradation is the excess I/O. When accessing existing row migration through an index, the database must scan more than one data block to retrieve the row-changing data. There are two forms:
1) Row migration or row chaining causes poor performance of the insert or update statements because they need to be executed for additional processing.
2) The performance of select statements that use indexes to query connected or migrated rows is poor because they need to execute additional I/O

How can I detect row migration and row link:
The migrated or linked rows in the table can be identified by the analyze statement with the list chained rows option. This command collects the information of each row to be migrated or linked and places the information in the specified output table. To create this output table, run the script utlchain. SQL.
SQL> analyze table Scott. EMP list chained rows;
SQL> select * From chained_rows;

Of course, you can also check the migrated or linked rows by checking the 'table fetch continued row' in the V $ sysstat view.

SQL> select name, value from V $ sysstat where name = 'table fetch continued row ';
NAME value
-------------------------------------------------------------------------
Table fetch continued row 308
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.
Solution
O in most cases, row links cannot be overcome, especially when a table contains columns such as longs and lobs. When there are a large number of linked rows in different tables and the row length of which tables is not very long, you can use a larger block size to reconstruct the database.

For example, the data block size of your database is 4 K, but the average length of your row is 6 K, then you can use 8 K data blocks to reconstruct the database to solve the row link problem.

Row O migration is mainly caused by the small pctfree parameter, which does not leave sufficient free space for the update operation. To avoid row migration, appropriate pctfree values should be set for all modified tables to reserve sufficient space for data modification within each data block. We can avoid row migration by adding the pctfree value, but this solution is at the cost of sacrificing more space, which is what we usually call space-for-efficiency. In addition, the increase of the pctfree value can only alleviate the phenomenon of row migration, but cannot completely solve the row migration problem. Therefore, a better solution is to set the appropriate pctfree value, if row migration is serious, reorganize the table data. The following is the procedure for reorganizing row migration data (this method has also become CTAs ):
-- Get the name of the table with migrated rows:
Accept table_name prompt 'Enter the name of the table with migrated rows :'

-- Clean up from last execution
Set echo off
Drop table migrated_rows;
Drop table chained_rows;

-- Create the chained_rows table
@.../Rdbms/admin/utlchain. SQL
Set echo on
Spool fix_mig
-- List the chained and migrated rows
Analyze table & table_name list chained rows;

-- Copy the chained/migrated rows to another table
Create Table migrated_rows
Select orig .*
From & table_name orig, chained_rows cr
Where orig. rowid = Cr. head_rowid
And Cr. table_name = upper ('& table_name ');

-- Delete the chained/migrated rows from the original table
Delete from & table_name where rowid in (select head_rowid from chained_rows );

-- Copy the chained/migrated rows back into the original table
Insert into & table_name select * From migrated_rows;

Spool off

When a full table scan is performed on a table, we actually ignore the pointers pointing to other rows in the row migration, because we know that the full table scan will traverse the whole table, the row data of the row to be migrated will be read, and the row data will be processed at this time. Therefore, row migration does not cause additional work during full table scan.
When reading data from a table through an index, the migrated rows cause additional I/O operations. This is because we will read the rowid of the Data row from the reference, which tells the database to find the required data on the specified slot of the specified data block of the specified file, but because of row migration, only one pointer pointing to the data is stored here, rather than the real data. Therefore, the database needs to follow this pointer (similar to rowid) go to the specified slot of the specified data block of the specified file to find the real data. Repeat the above process to find the real data. We can see that this will introduce additional I/O operations.

How does one migrate a table with severe row links?

The chained_cnt column in The dba_tables view, which contains the link row count of the table.

For reprint, please indicate the source and original article links;

Http://blog.csdn.net/xiangsir/article/details/8573653