Re: Row Migration/Rowchaining

From: Jared Still <>
Date: Wed, 16 Apr 2008 09:22:34 -0700
Message-ID: <>

Just to to clarify for everyone reading this thread, chaining and migration are similar, but distinctly different.

From the concepts manual:

Row Chaining and Migrating
> In two circumstances, the data for a row in a table may be too large to
> fit into a single data block. In the first case, 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 or LONG RAW. Row chaining in these
> cases is unavoidable.
> However, in the second case, a row that originally fit 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.

Migrated rows can be made to fit in a single block by re-organizing the table, or by just deleting and re-inserting the row that was migrated when an update made it to big to fit in the block.

If the rows are actually chained across blocks, no amount of re-organizing can correct it.
You can

  1. make the blocks bigger
  2. make the data smaller
  3. use compression

There's a number of caveats with compression, but it's one way to make the data smaller.


On Fri, Apr 11, 2008 at 11:40 AM, <> wrote:

> In my database when I run the following query against v$sysstat, I find
> huge number of migrated/chained rows.
> SQL> SELECT name, value
> FROM v$sysstat
> WHERE name = 'table fetch continued row';
> ---------------------------------- --------
> table fetch continued row 2327441

Received on Wed Apr 16 2008 - 11:22:34 CDT

Original text of this message