Shulou(Shulou.com)05/31 Report--

This article will explain in detail how Oracle modifies compressed data. Xiaobian thinks it is quite practical, so share it with you for reference. I hope you can gain something after reading this article.

We'll see that the underlying table compression mechanism works only with direct path loads, CTAS(create table as select), and "alter table move." At the same time, when compression is enabled for a table, Oracle will set pctfree to 0 by default, which also implies that our basic compression should be used as a compression policy for read-only data.

When we look at the dump file for a block, Oracle does not "compress" the data; what he does is create a list of duplicate values (i.e., a dictionary table) on each block, and then replace those duplicate values with flags to achieve block-level deduplication. Also, Oracle can rearrange the order of fields in a block, increasing the chances of replacing multiple fields with one flag. This tells us that Oracle doesn't need to "decompress" the data when reading blocks, all it needs to do is reconstruct the data from pointers, which is of course a CPU-intensive operation.

In this article, we will discuss what happens if the read-only principle is not followed. We will then explore OLTP compression that requires additional authorization in the third article. As mentioned earlier, all of the following examples are from instances of Oracle 11.2.0.3.

deduplication and deletion

You may recall from the previous article that I dumped a row of a block of data containing a combination flag, and then Oracle recursively looked up the meaning of the flag and finally determined that the combination flag consists of two separate flags and two additional field values. Here is the row we tested:

tab 1, row 0, @0x1b28

tl: 5 fb: --H-FL-- lb: 0x0 cc: 4

col 0: [ 4] 41 41 41 41

col 1: [10] 41 41 41 41 41 41 41 41 41 41

col 2: [ 2] c1 02

col 3: [10] 20 20 20 20 20 20 20 20 20 31

bindmp: 2c 00 01 04 31

This is the symbol **49 we found when looking up the value of the single symbol referenced **:

Tab 0, row 49, @0x1ed0

tl: 19 fb: --H-FL-- lb: 0x0 cc: 4

col 0: [ 4] 41 41 41 41

col 1: [10] 41 41 41 41 41 41 41 41 41 41

col 2: [ 2] c1 02

col 3: [10] 20 20 20 20 20 20 20 20 20 31

bindmp: 00 08 04 36 40 ca c1 02 d2 20 20 20 20 20 20 20 20 20 31

The first 5 bytes in bindmp tell us that this flag is used 8 times (00 08) in this block, consisting of 4 columns, then we look at flags 54(0x36) and 64(0x40):

tab 0, row 54, @0x1f74

tl: 7 fb: --H-FL-- lb: 0x0 cc: 1

col 0: [ 4] 41 41 41 41

bindmp: 00 0a cc 41 41 41 41

tab 0, row 64, @0x1f7b

tl: 13 fb: --H-FL-- lb: 0x0 cc: 1

col 0: [10] 41 41 41 41 41 41 41 41 41 41

bindmp: 00 05 d2 41 41 41 41 41 41 41 41 41 41

From the dump data above we can guess that extra work must be done if we want to delete the original rows. \

Two things are bound to happen:

1. The row must be marked deleted (in the normal way),

2. ** The "Use Count" for symbol number **49 must also be reduced by 1.

After deleting a line, here's a small fragment, starting with the line entry itself:

tab 1, row 0, @0x1b28

tl: 2 fb: --HDFL-- lb: 0x2

bindmp: 3c 02

The following is a binary dump of the **49 symbol **, note the second byte:

bindmp: 00 07 04 36 40 ca c1 02 d2 20 20 20 20 20 20 20 20 20 31

So we can realize that deleting even a simple row increases the effort of maintaining block data. But this flag is also used in the other 7 rows of the block, so what happens if I delete those rows? The answer depends on the number of concurrent sessions deleted. If I use a process to delete all 8 rows, Oracle deletes flags when deleting row 8, and flags 63 and 64 must be updated to show that they are missing a dependency. If I repeat the test using multiple sessions to delete rows and don't commit after each deletion, I can see a scenario where the flag shows zero but doesn't disappear. (It's also possible that some subsequent block cleanup operation that I haven't observed will clear the flag for this state.)

I didn't mention anything about commit or rollback until I mentioned concurrency testing. The flag change occurs on the delete action and is not committed afterwards. What happens if I commit or rollback?

At commit time, the usual commit cleanup operation may occur, updating the ITL slot of the transaction with the SCN at commit time (in other words, nothing new or special happens). On rollback, the data is restored based on undo information, any deleted flags are recreated, and the number of uses of any associated flags is increased.

But the point is that compression remains after rollback. Although these rows are written into the free space of the block after rollback, there are some differences between the original block and the block after rollback. Because such an operation requires an integration operation of blocks through free space debris. So if you dump the block out again, you can see that the contents of the block have been moved. In my example (deleting the eight lines referencing symbol 49 and rolling back), I see the following difference:

tab 0, row 49, @0x1ed0 -- original position of token 0

tab 0, row 49, @0x134a -- position of token 0 after rollback

tab 1, row 0, @0x1b28 -- original position of row 0

tab 1, row 0, @0x1322 -- position of row 0 after rollback

Compression and free space

When you delete and roll back data, rows move, which brings up a very interesting point about free space--when your table is base compressed, the default pctfree is 0. No free space, but room for me to move data after rollback?

I found that Oracle does reserve a bit of space (about a few dozen bytes, but definitely enough for two full lines in my test case). This small amount of space allows Oracle to restore deleted rows. In some cases, this extra space can even allow you to update.

Let me fine-tune my initial dataset so that each row looks like this:

(1000001, 'AAAA', 'AAAAAAAAAA',' 1')

The first column is a sequence, the second column cycles from AAAA to EEEE, the third column cycles from AAAAAA to JJ, and the last column is 10 characters, cycling from 1-50 (placeholders are indicated by "\ "). Then I generate 800 rows of data. Because of the way I created the data, the first block had 11 rows of data and the second and third columns were A's, so I needed to run sql and dump the first block of the table to see what happened.

update t1

set

vc_rep = 'BBBB'

where

vc_rep = 'AAAA'

and vc_cycle = 'AAAAAAAAAA'

and rownum

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.