Re: Row Migration/Rowchaining

  • From: "Jared Still" <jkstill@xxxxxxxxx>
  • To: "Oracle-L Freelists" <oracle-l@xxxxxxxxxxxxx>
  • Date: Wed, 16 Apr 2008 09:22:34 -0700

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
 a) make the blocks bigger
 b) make the data smaller
 c) use compression

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

Jared


On Fri, Apr 11, 2008 at 11:40 AM, <maheswara.rao@xxxxxxx> 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';
>
> NAME                               VALUE
> ---------------------------------- --------
> table fetch continued row          2327441
>
>
>

Other related posts: