At one of the presentations I attended at RMOUG this year the presenter claimed that if a row kept increasing in size and had to migrate from block to block as a consequence then each migration of that row would leave a pointer in the previous block so that an indexed access to the row would start at the original table block and have to follow an ever growing chain of pointers to reach the data.
This is not correct, and it’s worth making a little fuss about the error since it’s the sort of thing that can easily become an urban legend that results in people rebuilding tables “for performance” when they don’t need to.
Oracle behaves quite intelligently with migrated rows. First, the migrated row has a pointer back to the original location and if the row has to migrate a second time the first place that Oracle checks for space is the original block, so the row might “de-migrate” itself; however, even if it can’t migrate back to the original block, it will still revisit the original block to change the pointer in that block to refer to the block it has moved on to – so the row is never more than one step away from its original location. As a quick demonstration, here’s some code to generate and manipulate some data:
create table t1 ( id number(6,0), v1 varchar2(1200) ) pctfree 0 ; prompt ========================================== prompt The following code fits 74 rows to a block prompt ========================================== insert into t1 select rownum - 1, rpad('x',100) from all_objects where rownum <= 75; commit; prompt ====================================== prompt Make the first row migrate and dump it prompt ====================================== update t1 set v1 = rpad('x',400) where id = 0; commit; alter system flush buffer_cache; execute dump_seg('t1',2) prompt =========================================================== prompt Fill the block the long row is now in, force it to migrate, prompt then dump it again. prompt =========================================================== insert into t1 select rownum + 75, rpad('x',100) from all_objects where rownum <= 75; commit; update t1 set v1 = rpad('x',800) where id = 0; commit; alter system flush buffer_cache; execute dump_seg('t1',3) prompt ======================================================== prompt Fill the block the long row is now in and shrink the row prompt to see if it returns to its original block. (It won't.) prompt ======================================================== insert into t1 select rownum + 150, rpad('x',100) from all_objects where rownum <= 75; commit; update t1 set v1 = rpad('x',50) where id = 0; commit; alter system flush buffer_cache; execute dump_seg('t1',3) prompt ======================================================== prompt Make a lot of space in the first block and force the row prompt to migrate again to see if it migrates back. (It does.) prompt ======================================================== delete from t1 where id between 1 and 20; commit; update t1 set v1 = rpad('x',1200) where id = 0; commit; alter system flush buffer_cache; execute dump_seg('t1',3)
My test database was using 8KB blocks (hence the 74 rows per block), and 1MB uniform extents with freelist management. The procedure dump_seg() takes a segment name as its first parameter and a number of blocks as the second (then the segment type and starting block as the third and fourth) and dumps the first N data blocks of the segment. To demonstrate what goes on, I've extracted the content of the first row (id = 0) after each of the four dumps:
After the first update - the column count (cc) is zero and the "next rowid" (nrid) is row 1 of block 0x0140000b tab 0, row 0, @0xb3 tl: 9 fb: --H----- lb: 0x2 cc: 0 nrid: 0x0140000b.1 After the second update - the next rowid is row 7 of block 0x0140000c tab 0, row 0, @0xb3 tl: 9 fb: --H----- lb: 0x1 cc: 0 nrid: 0x0140000c.7 After the third update (shrinking the row) the row hasn't moved from block 0x0140000c tab 0, row 0, @0xb3 tl: 9 fb: --H----- lb: 0x2 cc: 0 nrid: 0x0140000c.7 After the fourth update (making space, and growing the row too much) the row moves back home tab 0, row 0, @0x4c1 tl: 1208 fb: --H-FL-- lb: 0x2 cc: 2 col 0: [ 1] 80 col 1:  78 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
My calls to dump blocks included the blocks where the row migrated to, so we'll have a look at the target locations (as given by the original row location's nrid) in those blocks over time. First we check block 0x0140000b, row 1 after the first two migrations:
tab 0, row 1, @0x1d7f tl: 414 fb: ----FL-- lb: 0x2 cc: 2 hrid: 0x0140000a.0 col 0: [ 1] 80 col 1:  tab 0, row 1, @0x1d7f tl: 2 fb: ---DFL-- lb: 0x1
After the first migration (the row arrives here) we have a "head rowid" (hrid) pointer telling us where the row came from. After the second migration, when the row has moved on, we simply have a typical "deleted stub" - two bytes reserving the row directory entry until the commit has been done and cleaned out.
Then we can examine the second target (0x140000c, row 7) on the second and third and fourth updates:
tab 0, row 7, @0x1966 tl: 814 fb: ----FL-- lb: 0x2 cc: 2 hrid: 0x0140000a.0 col 0: [ 1] 80 col 1:  tab 0, row 7, @0xb1 tl: 62 fb: ----FL-- lb: 0x1 cc: 2 hrid: 0x0140000a.0 col 0: [ 1] 80 col 1:  tab 0, row 7, @0xb1 tl: 2 fb: ---DFL-- lb: 0x2
As you can see, on arrival this location gets the original rowid as its "head rowid" (hrid), and it knows nothing about the intermediate block where the row was briefly in transit. I've copied the length byte (in square brackets) of column 1 in the dumps so that you can see that the row stayed put as it shrank. We can then see on the last update that we are left with a deleted stub in this block as the row migrates back to its original location when we try to extend it beyond the free space in this block.
Migrated rows are only ever one step away from home. It's not nice to have too many of them, but it's not necessarily a disaster.