The Oracle database knows three distinct processes that are easily mixed up: Row Chaining, Row Migration and Row Movement.

Luckily all three are well described in excellent articles: The Secrets of Oracle Row Chaining and Migration and Row Movement in Oracle.

For the impatient, I provide some very short definitions:

Row Chaining

Distribution of a single table row across multiple data blocks.

Row Migration

Relocation of an entire table row to a new place, without updating the indexes.

Row Movement

Relocation of an entire table row to a new place and updating the indexes.

This article was inspired by the question if Oracle 11r2 performs Row Movement instead of Row Migration for ordinary UPDATE statements—that is, in absence of partitions. The short answer is: no, it doesn’t. The long answer is the rest of this article.

The difference between Row Chaining and Row Migration is somehow understandable: If the row doesn’t fit into a single data block, it must be chained; otherwise it can be migrated as a whole. The limitation of the Row Migration is that it does not update the indexes on the table. That means, the ROWID that is stored in the index still refers to the old location of the row. An additional block, the new location of the row, must be read to fetch the required data.

The more modern Row Movement is different as it updates the corresponding indexes—the ROWID actually changes. This has benefits on the long run, because the additional block read can be avoided in the TABLE ACCESS BY INDEX ROWID operation. On the short run, the actual UPDATE operation is much more complex. I suppose this is the reason why a regular update does not (yet) trigger a Row Movement.

The Row Movement was introduced to support an UPDATE on a partition key—so that a table row is moved from one table partition to another one. In the meanwhile it is also used for flashback and space management—as described in the above-mentioned article.

As of Oracle Release 11g2, Row Movement is optional and disabled per default. It can be enabled per table with a very trivial ALTER TABLE statement. The usual reason to enable it is that one of the features which require Row Movement is used; partition key update, table shrink and flashback. There is hardly any reason not to enable row movement—the only side effect is that ROWID‘s might change; that should not affect well designed applications.

Row Migration has some considerable problems, as pointed out in the later in this article. On the other hand, Row Movement has also a drawback; that is, the update of all the affected indexes can be very expensive. It’s a trade off between read and write performance. While Row Movement is more expensive for UPDATES, it maintains best index performance. In contrast, the Row Migration favors UPDATE performance over index speed. Although not true for all cases, I believe that most data is read more often than it is written—especially in our modern society where nobody every deletes data—so that Row Movement is generally the better choice.

The article examines how to get 100% migrated rows with the “insert empty, update everything” anti-pattern, why DBMS_STATS isn’t a perfect substitute for ANALYZE TABLE, the immunity of migrated rows to alter table ... shrink space and why PCTFREE is still the only rescue from DBAs perspective.

Just to show what I mean, I repeat a modified version of the test originally created by Tom Kyte and reused by Martin Zahn:

CREATE TABLE row_migration_demo (
  a CHAR(2000),
  b CHAR(2000),
  c CHAR(2000),
  d CHAR(2000),
  e CHAR(2000),
  x int,
  constraint row_migration_pk primary key (x)
) enable row movement
/

I have changed the size of the CHAR field to 2k because my block size is 8k whereas it was 4k in the original example. I have also put the x column to the end for a better verification of Row Migration versus Row Chaining. Finally I enable Row Movement, which does actually not change anything but allows me to perform an alter table ... shrink space.

The next step is a classical insert empty, update everything sequence:

INSERT INTO row_migration_demo (x) values (1);
UPDATE row_migration_demo set a = 'a', b='b', c='c' where x=1;
COMMIT; 

Up till now everything is perfectly fine and the entire row is in a single block:

SQL> select * from row_migration_demo where x=1;

         X
----------
         1


SQL> SELECT a.name, b.value
       FROM v$statname a, v$mystat b
      WHERE a.statistic# = b.statistic#
        AND lower(a.name) = 'table fetch continued row';

NAME                             VALUE
--------------------------- ----------
table fetch continued row            0

So, lets do it again and insert a second row:

INSERT INTO row_migration_demo (x) values (2);
UPDATE row_migration_demo set a = 'a', b='b', c='c' where x=2;
COMMIT;

And select it again:

SQL> select * from row_migration_demo where x=2;

         X
----------
         1


SQL> SELECT a.name, b.value
       FROM v$statname a, v$mystat b
      WHERE a.statistic# = b.statistic#
        AND lower(a.name) = 'table fetch continued row';

NAME                             VALUE
--------------------------- ----------
table fetch continued row            1

Hooray, the row is migrated:

SQL> ANALYZE TABLE row_migration_demo COMPUTE STATISTICS;

PL/SQL procedure successfully completed.

SQL> select num_rows, chain_cnt 
       from user_tables 
      where table_name='ROW_MIGRATION_DEMO';

  NUM_ROWS  CHAIN_CNT
---------- ----------
         2          1

Note: ANALYZE is deprecated. Yup, I know, but DMBS_STATS.GATHER_TABLE_STATS does not propagate the CHAIN_CNT column. Ask Tom!

It’s hard to figure out if the row is chained or migrated—I am actually not sure is there any difference in the data structure. Because the additional table fetch continued row occurs for the very first column, I believe the row is migrated:

SQL>  select length(a) from row_migration_demo where x=2;

 LENGTH(A)
----------
      2000

SQL> SELECT a.name, b.value
       FROM v$statname a, v$mystat b
      WHERE a.statistic# = b.statistic#
        AND lower(a.name) = 'table fetch continued row';

NAME                             VALUE
--------------------------- ----------
table fetch continued row            2

To be on the safe side, I will double verify:

INSERT INTO row_migration_demo (x, a) values (3, 'a');
UPDATE row_migration_demo 
   SET b = 'b', c = 'c', d = 'd', e = 'e'
 WHERE x=3; 

Indeed, the a column is at the place where the row was inserted while the e column is somewhere else:

SQL>  select length(a) from row_migration_demo where x=3;

 LENGTH(A)
----------
      2000

SQL> SELECT a.name, b.value
       FROM v$statname a, v$mystat b
      WHERE a.statistic# = b.statistic#
        AND lower(a.name) = 'table fetch continued row';

NAME                             VALUE
--------------------------- ----------
table fetch continued row            2

SQL>  select length(e) from row_migration_demo where x=3;

 LENGTH(A)
----------
      2000

SQL> SELECT a.name, b.value
       FROM v$statname a, v$mystat b
      WHERE a.statistic# = b.statistic#
        AND lower(a.name) = 'table fetch continued row';

NAME                             VALUE
--------------------------- ----------
table fetch continued row            3

To make things even worse, single rows can be migrated and chained. If we insert two more records, it becomes visible:

INSERT INTO row_migration_demo (x) values (4);
UPDATE row_migration_demo 
   SET a='a', b='b', c='c', d='d', e='e' 
 WHERE x=4;

INSERT INTO row_migration_demo (x) values (5);
UPDATE row_migration_demo 
   SET a='a', b='b', c='c', d='d', e='e' 
 WHERE x=5;

COMMIT;

Each of those rows must be chained, because they need more space than a single block has. However, due to migration, the row is actually distributed to three blocks:

SQL>  select length(a) from row_migration_demo where x=5;

 LENGTH(A)
----------
      2000

SQL> SELECT a.name, b.value
       FROM v$statname a, v$mystat b
      WHERE a.statistic# = b.statistic#
        AND lower(a.name) = 'table fetch continued row';

NAME                             VALUE
--------------------------- ----------
table fetch continued row            4

SQL>  select length(b) from row_migration_demo where x=5;

 LENGTH(B)
----------
      2000

SQL> SELECT a.name, b.value
       FROM v$statname a, v$mystat b
      WHERE a.statistic# = b.statistic#
        AND lower(a.name) = 'table fetch continued row';

NAME                             VALUE
--------------------------- ----------
table fetch continued row            5

SQL>  select length(c) from row_migration_demo where x=5;

 LENGTH(C)
----------
      2000

SQL> SELECT a.name, b.value
       FROM v$statname a, v$mystat b
      WHERE a.statistic# = b.statistic#
        AND lower(a.name) = 'table fetch continued row';

NAME                             VALUE
--------------------------- ----------
table fetch continued row            7

Please note that the “continued fetch” increased by two for the select on column c . The statistics show that all but one row is chained (the very first row):

SQL> ANALYZE TABLE row_migration_demo COMPUTE STATISTICS;

PL/SQL procedure successfully completed.

SQL> select num_rows, chain_cnt 
       from user_tables 
      where table_name='ROW_MIGRATION_DEMO';

  NUM_ROWS  CHAIN_CNT
---------- ----------
         5          4

If we continue the game and continue to insert data in this way, we get an astonishing chain count:

begin
   for i in 10..1000 loop
      INSERT INTO row_migration_demo (x) values (i);
      UPDATE row_migration_demo set a ='a', b = 'b', c = 'c' where x=i;
   end loop;
end;
/


SQL> ANALYZE TABLE row_migration_demo COMPUTE STATISTICS;

PL/SQL procedure successfully completed.

SQL> select num_rows, chain_cnt 
       from user_tables 
      where table_name='ROW_MIGRATION_DEMO';

  NUM_ROWS  CHAIN_CNT
---------- ----------
       996        995

All the inserted rows are chained.

The only way to clean up the mess is to move the table. Shrinking doesn’t help to much, especially not if there was no DELETE or UPDATE that has freed some space:

SQL> alter table row_migration_demo shrink space;

Table altered.

SQL> analyze table row_migration_demo compute statistics;

Table analyzed.

SQL> select num_rows, chain_cnt 
       from user_tables 
      where table_name='ROW_MIGRATION_DEMO';

  NUM_ROWS  CHAIN_CNT
---------- ----------
       996        995

Even if some space is freed and rows are moved, the chaining is not notably reduced:

SQL> delete from row_migration_demo where x < 200;

195 rows deleted.

SQL> analyze table row_migration_demo compute statistics;

Table analyzed.

SQL> select num_rows, chain_cnt 
       from user_tables 
      where table_name='ROW_MIGRATION_DEMO';

  NUM_ROWS  CHAIN_CNT
---------- ----------
       801        801

SQL> alter table row_migration_demo shrink space;

Table altered.

SQL> analyze table row_migration_demo compute statistics;

Table analyzed.

SQL> select num_rows, chain_cnt 
       from user_tables 
      where table_name='ROW_MIGRATION_DEMO';

  NUM_ROWS  CHAIN_CNT
---------- ----------
       801        800

Please note that the shrink has corrected one chained row. That proofs that a Row Movement assigns a new ROWID and updates the index. However, one “unchained” row isn’t really the correction I would like to see. The only way to correct all chains, is to mote the table:

SQL> alter table row_migration_demo move;

Table altered.

SQL> alter index row_migration_pk rebuild;

Index altered.

SQL> analyze table row_migration_demo compute statistics;

Table analyzed.

SQL> select num_rows, chain_cnt 
       from user_tables 
      where table_name='ROW_MIGRATION_DEMO';

  NUM_ROWS  CHAIN_CNT
---------- ----------
       801          0

No chaining anymore.

There is actually another way to correct row migration. The Oracle documentation suggest to delete and re-insert all affected rows. Watch out for your triggers.

From administrators‛ perspective, the only way to prevent row migration in the first place is to increase PCTFREE. The complete re-execution of this test with PCTFREE increased to 50% “solves” the problem and no chained rows occur anymore:

drop table row_migration_demo;
CREATE TABLE row_migration_demo (
  a CHAR(2000),
  b CHAR(2000),
  c CHAR(2000),
  d CHAR(2000),
  e CHAR(2000),
  x int,
  constraint row_migration_pk primary key (x)
) enable row movement pctfree 50
/
begin
   for i in 1..1000 loop
      INSERT INTO row_migration_demo (x) values (i);
      UPDATE row_migration_demo set a ='a', b = 'b', c = 'c' where x=i;
   end loop;
end;
/
commit; 
analyze table row_migration_demo compute statistics;
select num_rows, chain_cnt 
       from user_tables 
      where table_name='ROW_MIGRATION_DEMO';

  NUM_ROWS  CHAIN_CNT
---------- ----------
      1000          0

If this is not acceptable, there is one very last option: change the application.

However, it would be very nice if an operation—similar to shrink—would move migrated rows. It would be even more compelling if a table option would allow to disable Row Migration in favor of Row Movement.

UPDATE 2010-03-09: A follow up article describes the impact of Row Migration on the Clustering Factor.

Appendix

After some years of professional experience, I always wonder why a particular idea of mine should be unique. So I suspected that this feature might be there, somewhere hidden in Oracle. So I checked for hidden parameters (thanks to a little documentation on that topic):

SQL> select a.ksppinm parameter, b.kspftctxvl 
       from x$ksppi a join x$ksppcv2 b on (a.INDX+1= b.kspftctxpn) 
      where a.KSPPDESC like '%movement%';

PARAMETER                                KSPFTCTXVL
---------------------------------------- ----------
_disable_implicit_row_movement           FALSE

SQL> alter session set "_disable_implicit_row_movement" = true;

Session altered.

SQL> select a.ksppinm parameter, b.kspftctxvl 
       from x$ksppi a join x$ksppcv2 b on (a.INDX+1= b.kspftctxpn) 
      where a.KSPPDESC like '%movement%';

PARAMETER                                KSPFTCTXVL
---------------------------------------- ----------
_disable_implicit_row_movement           TRUE

SQL>

As suggested by the name of the parameter, this doesn’t change anything in the direction I would like.