Row Chaining and Row Migration

Introduction
------------

This article discusses the difference between row chaining and migration.
Guidelines are given on how to detect and resolve from this situation.


Concepts
--------

There are two circumstances when this can occur, the data for a row in a table
may be too large to fit into a single data block.  This can be caused by either
row chaining or row migration.

 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.

 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.

o INSERT and UPDATE statements that cause migration and chaining perform poorly,
  because they perform additional processing.

o SELECTs 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 scott.emp 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

Although migration and chaining are two different things, internally they are
represented by Oracle as one.  When detecting migration and chaining of rows
you should analyze carrefully what you are dealing with.


Resolving
---------

o In most cases chaining is unavoidable, especially when this involves tables
 with large columns such as LONGS, LOBs, etc.  When you have a lot of chained
 rows in different tables and the average row length of these tables is not
 that large, then you might consider rebuilding the database with a larger
 blocksize.

 e.g.: You have a database with a 2K block size. Different tables have multiple
       large varchar columns with an average row length of more than 2K.  Then this
       means that you will have a lot of chained rows because your block size is
       too small.  Rebuilding the database with a larger block size can give you
       a significant performance benefit.
      
o Migration is caused by PCTFREE being set too low, there is not enough room in
 the block for updates.  To avoid migration, all tables that are updated should
 have their PCTFREE set so that there is enough space within the block for updates.
 You need to increase PCTFREE to avoid migrated rows.  If you leave more free
 space available in the block for updates, then the row will have more room to
 grow.

 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 move command
 - table level export / import

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

Note
----

When you run the script in the document to clean up the chained rows if the
table contains long raw columns, the script will fail with ORA-997: Illegal use
of LONG datatype. This error is normal as you cannot do a CTAS when you have
long raws. Also, by the nature of the long raw datatype, chaining is
unavoidable.