When UPDATE becomes an INSERT

During a research for VOODOO, we came across a lot of interesting stuff inside REDO.
One of my favourites is an UPDATE, becoming an INSERT 🙂

So let’s see what has happened – I performed the following update on a sample table:

The regular dump of redo log with an UPDATE looks like this:

In this particular redo record, we can see 2 changes:

• CHANGE #1 – with OP:5.1 is an UNDO information
• CHANGE #2 – with OP:11.5 is an actual UPDATE

We can see here, that column 1 changes from "cieple kluchy, kostuchy @@!!" to "ola la la la la". You can check it with simple query:

The column 6 (CLOB) on the other hand changes from out of row storage to in-line storage. You can see it by obtaining bytes on position 22 and 23 – "0x4890″ means, that this CLOB is in-line and you can check the actual value by extracting bytes from position 30 to the end.

You can use Python to decode CLOB value. Since my database is AL32UTF8 I have to use utf-16-be coding which is actual coding for CLOBs in Oracle with AL32UTF8.

So this is how an UPDATE actually looks like. But the funny thing is, that I found also an INSERT in my REDO dump! It looks like this:

OP:11.2 is code for INSERT and we can see here all 8 columns of the table. The next REDO record which follows looks almost like a DELETE:

It looks almost like DELETE because the actual DELETE code is 11.3 and not 11.6. So it seems like instead of UPDATE, Oracle is doing an INSERT and then something like DELETE. But why? Everything clears when we ANALYZE a table in search for chained rows.

(Please check out this article by Tanel Poder on chained and migrated rows https://blog.tanelpoder.com/2009/11/04/detect-chained-and-migrated-rows-in-oracle/)

In the UNDO vector, you can see all the values necessary to build an INSERT, which is the actual undo operation for this statement.

Let’s check if the content of the UNDO INSERT matches with a chained row:

So we have a winner! Whenever the rows are being chained or migrated, the content of the REDO looks a little bit like INSERT and DELETE instead of just UPDATE.

So it means that chained and migrated rows are not only affecting SELECT statements but also can cause much more REDO to be generated.