Re: Row Migration/Rowchaining
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.
- make the blocks bigger
- make the data smaller
- 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, <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