Row count 2

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[0]	nrow=2	offs=0
0x12:pri[0]	offs=0xc0c
0x14:pri[1]	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: [2500]
 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[0]	nrow=1	offs=0
0x12:pri[0]	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: [2000]
 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.