Row Chaining and Row Migration

Row Chaining

Occurs when 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, LONG RAW, LOB, etc. Row chaining in these cases is unavoidable.

Row Migration

Occurs when a row that originally fitted 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.

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

INSERT and UPDATE statements that cause migration and chaining perform poorly, because they perform additional processing.
SELECT(s) that use an index to select migrated or chained rows must perform additional I/Os.

Detection


Migrated and chained rows in a table or cluster can be identified by using the ANALYZE command with the LIST CHAINED ROWS option. This command collects information about each migrated or chained row and places this information into a specified output table. To create the table that holds the chained rows, execute script UTLCHAIN.SQL.

SQL> ANALYZE TABLE <USER_NAME>.<TABLE_NAME> LIST CHAINED ROWS;

SQL> SELECT * FROM chained_rows;

You can also detect migrated and chained rows by checking the 'table fetch continued row' statistic 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

SQL Script to eliminate row migration:

-- 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
@$ORACLE_HOME/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 as
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

Alternative methods to eliminate the migrated rows are:

- alter table <table_name> move <tablespace>;
- table level export / import

Please note that these methods will rebuild the entire table rather then re-inserting the migrated rows.

List the Migrated or Chained rows.
--------------------------------------------

From SQL*Plus:

col owner_name format a10
col table_name format a20
col head_rowid format a20

select owner_name, table_name, head_rowid from chained_rows;