In his articles Compression in Oracle – Part 2: Read-Only Data and Compression in Oracle – Part 3: OLTP Compression, Jonathan Lewis has shown that block (re)compression is never attempted on updates - it is attempted only on inserts (and, of course, only if the used space crosses the PCTFREE threshold).

Now, since a row migration could be considered a row re-insertion - does it trigger a compression attempt of the block that the row is migrated into? The answer is yes, as I will show you in a while.

It is worth remembering that row migration can be quite dramatic in an OLTP-compressed table, because (as shown by Jonathan above) when a tokenized column of a compressed row is updated to a new value, the whole token is expanded, modified and left uncompressed, even if the new value could be substituted by a token already present in the block. Hence any kind of update, even the tiniest, has the potential to inflate the row considerably, and of course, if the row can’t fit into the free space, the row has to be migrated to a new block.

Compressing migrated rows has the benefit that at least the whole size of the table is not probably going to grow much, assuming a reference scenario where rows were inserted randomly in the first place (i.e. without cleverly colocating similar rows in the same block, e.g. by inserting them in bulk using a well-designed order by), and assuming that the updating process that causes migration is random as well (which is probably almost always true). The “only” overhead is the additional block get necessary to follow the rowid that acts as the “forwarding address” from the original row position (where only the row header is stored) to the new (where the row piece is now stored).

Side note: it’s interesting to note that this overhead is not present when doing a full table scan, since full scans simply do not follow the “forwarding address” since they are going to find the row piece anyway (a well-known fact that is also checked in my test case for completeness). Since, as reasoned about above for our reference scenario, the table size probably does not change much, a frequently-full-scanning DWH is probably going to enjoy similar performance even after updates (and this is a bit ironic for a feature named “OLTP compression”); not so for OLTP systems or DWHs that use single row access a lot (e.g. by following rowids from bitmap indexes), that have to pay the migration penalty.

But let’s leave speculations alone, and get back to hacking …

test case: migrated rows are compressed

Note: the test case (main.sql), its spool (main.lst) and block dumps (main.trc) are available in oltp_compress_migrated_rows.zip, alongside other test cases mentioned in the following.

For simplicity, we are going to use a tablespace with segment space managed manually, i.e. where free blocks are linked together by one freelist, and with a block size of 4K; Oracle version is 11.2.0.3.

The test table is (note the PCTFREE set to 50%):

create table t (x varchar2(400 char))
pctfree 50
compress for oltp
;

The initial row set is generated by:

insert /*+ append */ into t
select rpad( 'A', 400, 'A') from dual connect by level <= 10000;

That is, all identical rows, all chars set to ‘A’ (coded as 0x41 in the WE8ISO8859P1 single-byte charset of my test database). Note that each row is sized 400 bytes and hence, when not compressed, only 10 rows can fit in a 4K block, and only 5 rows would be inserted given the PCTFREE=50 setting.

The block dump of the first block after the segment header shows that we have 2017 bytes free (tosp=0x7e1), as expected. In the row dump we find, in order, first the (lonely) token:

tab 0, row 0, @0xdf3
tl: 405 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [400] {repeat 41, 400 times}
bindmp: 00 8a fa 01 90 {repeat 41, 400 times}

and then 137 highly-compressed rows, all with the same structure, referencing the token:

tab 1, row 0, @0xdee
tl: 5 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [400] {repeat 41, 400 times}
bindmp: 2c 00 01 01 00

Note: check the above mentioned articles by Jonathan for a detailed explanation of the compression format. The bindmp line shows what’s actually stored in the block: “2c 00 01” are the flag byte, the lock byte, and the column count; “01” means “1 token follows” and “00” is the pointer to token zero.

Let’s update every row to lowercase. i.e. to a string of 400 ‘a’ (0x61), thus causing massive row migration:

update t set x = lower(x);

After the update, in our block the token is gone; the first 5 rows were uncompressed but were kept in the block since they fit in the free space:

tab 1, row 0, @0xc5c
tl: 407 fb: --H-FL-- lb: 0x2  cc: 1
col  0: [400] {repeat 61, 400 times}
bindmp: 2c 02 01 00 fa 01 90 {repeat 61, 400 times}

but , indeed, all the other 132 (=137-5) ones were migrated:

tab 1, row 5, @0x5f7
tl: 9 fb: --H----- lb: 0x2  cc: 0
nrid:  0x04000148.0
bindmp: 20 02 00 04 00 01 48 00 00

The flag byte “–H—–” means “this is just the row header” (check this great note by Steve Adams) and nrid is the “forwarding address” rowid we spoke about previously.

Now, the interesting part - migrated rows got compressed.

Indeed, walking down the blocks containing the migrated rows, we see

(a) - token 0, holding 400 bytes set to 0x61 (same as above, not shown)

(b) - a certain number (usually a dozen) of compressed rows:

tab 1, row 0, @0xce0
tl: 11 fb: ----FL-- lb: 0x1  cc: 1
hrid: 0x04000101.5
col  0: [400] {repeat 61, 400 times}
bindmp: 0c 01 01 04 00 01 01 00 05 01 00

note that the row is migrated: the flag byte “—-FL–” means “this is the First and Last row piece, the Header is not here” and hrid is the pointer to the header (i.e. the original row position). Of course, note that the row is compressed: the bindmp shows the usual row header triplet “0c 01 01”, then the hrid “04 00 01 01 00 05” and then the usual “01” meaning “1 token follows” plus “00”, the pointer to token zero.

(c) - one or two uncompressed rows:

tab 1, row 10, @0xae0
tl: 413 fb: ----
FL-- lb: 0x1 cc: 1 hrid: 0x04000101.f col 0: [400] {repeat 61, 400 times} bindmp: 0c 01 01 04 00 01 01 00 0f 00 fa 01 90 {repeat 61, 400 times}

note the bindmp, and the flag byte telling us, again, that this row is indeed migrated.
In addition, tosp is usually set to 0x907 (2311 bytes), about half of the block, honoring the PCTFREE setting to 50%.

This layout is typical of a series of row insertions in a empty block: the first inserts get simply stored uncompressed, until one that would make the used space cross the pctfree threshold triggers a potential compression before being inserted uncompressed. Note the order (already noted by Jonathan Lewis): first the compression is potentially attempted, then the row is stored - hence at least one uncompressed row is always present. There can be more because the compression is not always attempted, depending, possibly, on some cost consideration by the kernel. Check thr.sql and thr.trc if interested.

uncompressed un-migration

As a side investigation, I have also checked what happens when a row is “un-migrated”; I was expecting that un-migration could trigger a compression, but this does not seem to be the case, at least in my scenario.

I have prepared the first block (check the last part of main.trc) with only 12 migrated rows (leaving a very big free space of tosp=0xde6=3558 bytes), and then I have updated them to the original value in uppercase using “update t set x = upper(x)” . These rows, being identical, could be comfortably be un-migrated and compressed all together in the original block (just remember that we observed a block with 137 such rows at the start of the main investigation), but that has not happened. Instead, the first 8 rows have been un-migrated indeed but left uncompressed, and the last 4 has been simply left migrated; also, the block free space has dropped to a mere tosp=0x176=374 bytes, thus severely hitting the PCTFREE reserve.
Un-migration is a potential source of table size increase and/or pctfree space consumption, if this happens all the times in all scenarios - but I have not checked this extensively, though.