Re: Row Migration/Rowchaining

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.

Jared

On Fri, Apr 11, 2008 at 11:40 AM, <maheswara.rao_at_ubs.com> 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
>
>
>

--
http://www.freelists.org/webpage/oracle-l