Following my notes about counting rows in blocks, someone emailed me to ask how the query I’d published would deal with migrated rows. Remember, a migrated row is one that has been moved in its entirety to a different block because an update to the row resulted in the row needing more space than was currently available in its original block.
The answer is simple – my query doesn’t see that the content of the row is in a new location, it only sees the ‘head piece’ row directory entry. To clarify this point I’ve set up some data and then extracted a few details from a pair of block dumps. I created a table with just two (big) rows in the first block, then updated the second row to make it migrate to the second block in the table (I was using freelist management, not ASSM, so the behaviour was predictable).
The first extract is from the block dump of the first block after the update and shows the row directory and the start of each row in the “row heap” (that’s a term I’ve just invented – I think). Notice how the flag byte (fb) on row 1 has just the H bit (head piece) set but its F bit (first row piece) and L bit (last row piece) not set. Compare this with row 0 which has the H, F and L bits set. We also see that the row itself carries the nrid (next rowid ) value that points to the next row piece for this row.
0xe:pti nrow=2 offs=0 0x12:pri offs=0xc0c 0x14:pri offs=0x3ce block_row_dump: tab 0, row 0, @0xc0c tl: 5012 fb: --H-FL-- lb: 0x0 cc: 3 col 0: [ 2] c1 02 col 1:  41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 41 ... tab 0, row 1, @0x3ce tl: 9 fb: --H----- lb: 0x2 cc: 0 nrid: 0x0140120b.0 end_of_block_dump
The second extract is from the dump of the second block in the table. The row directory shows just one row in the block, but when we check the flag byte for that row we see that the H bit is not set while the F and L bits are – this tells us that the row piece is the whole row but it has been migrated from another location. The row entry itself holds the hrid (head rowid) telling us where the row came from.
0xe:pti nrow=1 offs=0 0x12:pri offs=0xde2 block_row_dump: tab 0, row 0, @0xde2 tl: 4518 fb: ----FL-- lb: 0x2 cc: 3 hrid: 0x0140120a.1 col 0: [ 2] c1 03 col 1:  42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42 42
The flag information tells Oracle what type of row piece is identified by each row directory entry – so when I run my row counting query against this table it can (and will) report two rows in the first block and no rows in the second block.
This complication is a detail I didn’t think of when I was on the client site; and this omission is the type of “blinkered vision” that can appear at all levels of Oracle skill: if there’s no hint that you need to check for special cases it’s very easy to forget that such special cases might be relevant.