Ver este artículo en EspañolAt first glance, this topic may look trivial, but may be the source for severe performance issues, unnecessary maintenance tasks and/or storage waste.
Yes, you can say "update is so basic, why I should care about it?!", well it may be some cases when using update without care, things may get ugly. I found it playing with my old friend ALTER SYSTEM DUMP BLOCK and a few inserts and updates, over a table with a few columns, two of them VARCHAR2 type.
This was done on an Oracle 10.2.0.4 release database, let's see how it goes
create table table5 (col1 number, col2 varchar(10), col3 varchar(46));
insert into table5 (col1,col2,col3) values (1,'row one','This is the first row');
commit;
insert into table5 (col1,col2,col3) values (2,'row two','This is the 2nd row,updates will be Committed!');
commit;
alter session set tracefile_identifier='table5_04_b103';
alter system dump datafile 6 block 103;
I strongly recommend using parameter TRACEFILE_IDENTIFIER, because helps you differentiate your trace quickly.
Initial state dump for block where rows were inserted:
CF8F390 C1023320 01FFFF02 C102FF80 0203022C [ 3..........,...]
CF8F3A0 720703C1 7420776F 542D6F77 20736968 [...row two-This ]
CF8F3B0 74207369 32206568 7220646E 752C776F [is the 2nd row,u]
CF8F3C0 74616470 77207365 206C6C69 43206562 [pdates will be C]
CF8F3D0 696D6D6F 21646574 0203012C 720702C1 [ommitted!,.....r]
CF8F3E0 6F20776F 5415656E 20736968 74207369 [ow one.This is t]
CF8F3F0 66206568 74737269 776F7220 14A30601 [he first row....]
Block header dump: 0x01800067
...
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f74
0x14:pri[1] offs=0x1f38
block_row_dump:
tab 0, row 0, @0x1f74
tl: 36 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 02
col 1: [ 7] 72 6f 77 20 6f 6e 65
col 2: [21]
54 68 69 73 20 69 73 20 74 68 65 20 66 69 72 73 74 20 72 6f 77
tab 0, row 1, @0x1f38
tl: 60 fb: --H-FL-- lb: 0x2 cc: 3
col 0: [ 2] c1 03
col 1: [ 7] 72 6f 77 20 74 77 6f
col 2: [45]
54 68 69 73 20 69 73 20 74 68 65 20 32 6e 64 20 72 6f 77 2c 75 70 64 61 74
65 73 20 77 69 6c 6c 20 62 65 20 43 6f 6d 6d 69 74 65 64 21
end_of_block_dump
End dump data blocks tsn: 7 file#: 6 minblk 103 maxblk 103
It shows our two records: starting bottom with first row; on top of it, second row (that's why a 'regular' Oracle table is properly named HEAP ORGANIZED TABLE)
Now let's issue some updates on row #2, column 2 (col2) will change value from 'row two' and 'row two-02' to 'row two-25'
update table5b set col2 = 'row two-02' where col1 = 2;
...
update table5b set col2 = 'row two-25' where col1 = 2;
CF8F350 C3041100 FF373005 1702C102 03012C2F [.....07...../,..]
CF8F360 0A03C102 20776F72 2D6F7774 542D3532 [....row two-25-T]
CF8F370 20736968 74207369 32206568 7220646E [his is the 2nd r]
CF8F380 752C776F 74616470 77207365 206C6C69 [ow,updates will ]
CF8F390 43206562 696D6D6F 21646574 0203002C [be Commited!,...]
CF8F3A0 720703C1 7420776F 542D6F77 20736968 [...row two-This ]
CF8F3B0 74207369 32206568 7220646E 752C776F [is the 2nd row,u]
CF8F3C0 74616470 77207365 206C6C69 43206562 [pdates will be C]
CF8F3D0 696D6D6F 21646574 0203002C 720702C1 [ommited!,......r]
CF8F3E0 6F20776F 5415656E 20736968 74207369 [ow one.This is t]
CF8F3F0 66206568 74737269 776F7220 14F10602 [he first row....]
Block header dump: 0x01800067
...
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f74
0x14:pri[1] offs=0x1ef9
block_row_dump:
tab 0, row 0, @0x1f74
tl: 36 fb: --H-FL-- lb: 0x0 cc: 3
col 0: [ 2] c1 02
col 1: [ 7] 72 6f 77 20 6f 6e 65
col 2: [21]
54 68 69 73 20 69 73 20 74 68 65 20 66 69 72 73 74 20 72 6f 77
tab 0, row 1, @0x1ef9
tl: 63 fb: --H-FL-- lb: 0x1 cc: 3
col 0: [ 2] c1 03
col 1: [10] 72 6f 77 20 74 77 6f 2d 32 35
col 2: [45]
54 68 69 73 20 69 73 20 74 68 65 20 32 6e 64 20 72 6f 77 2c 75 70 64 61 74
65 73 20 77 69 6c 6c 20 62 65 20 43 6f 6d 6d 69 74 65 64 21
end_of_block_dump
Changing col2 from '
row two' to 'row two-02', required to duplicate the whole row... of course, the register with col2='row two' was logically deleted, remaining in the block.
Is this a dead record? yes, it's a dead record...
The 24 consecutive updates, didn't duplicate any information ... sorry to disappoint you if expected to see duplication in this case. But what happened? Seems that Oracle update algorithm has intelligence to identify that old and new values have the same length, then simply updating col2 on the spot. Update on CHAR columns (fixed length datatypes) behave the same way.
Ok, now let's try modifying length of updated value between transactions. First alternate updates on col2 and col3:
update table5 set col2 = 'row two-38' where col1 = 2;
commit;
update table5 set col3 = 'Now update the second row, shorter text!' where col1 = 2;
commit;
update table5 set col2 = 'row 2-39' where col1 = 2;
commit;
update table5 set col3 = 'Now re-update the second row, another text!' where col1 = 2;
commit;
update table5 set col2 = 'row two-40' where col1 = 2;
commit;
update table5 set col3 = 'Now re-re-update the second row, another text!' where col1 = 2;
commit;
update table5 set col2 = 'row 2-41' where col1 = 2;
commit;
update table5 set col3 = 'Now re-re-re-update the second row, will fit?' where col1 = 2;
commit;
update table5 set col2 = 'row two-42' where col1 = 2;
commit;
update table5 set col3 = 'Now re-re-re-re-update the 2nd row, changed?' where col1 = 2;
commit;
update table5 set col2 = 'row two-43' where col1 = 2;
and later, updates only on col2...
update table5 set col2 = 'row two-44' where col1 = 2;
commit;
update table5 set col2 = 'row 2-45' where col1 = 2;
commit;
update table5 set col2 = 'row two-46' where col1 = 2;
commit;
update table5 set col2 = 'row 2-47' where col1 = 2;
commit;
update table5 set col2 = 'row two-48' where col1 = 2;
commit;
update table5 set col2 = 'row 2-49' where col1 = 2;
And see how looks now the block dump, suddenly the block is filled like we issued inserts instead of updates, strange to say the least
D84D000 61623763 4E5F3163 2C6F6765 C1020302 [c7bac1_Nego,....]
D84D010 6F720803 2D322077 4E2C3934 7220776F [..row 2-49,Now r]
D84D020 65722D65 2D65722D 752D6572 74616470 [e-re-re-re-updat]
D84D030 68742065 6E322065 6F722064 63202C77 [e the 2nd row, c]
D84D040 676E6168 2C3F6465 C1020300 6F720A03 [hanged?,......ro]
D84D050 77742077 38342D6F 776F4E2C 2D657220 [w two-48,Now re-]
D84D060 722D6572 65722D65 6470752D 20657461 [re-re-re-update ]
D84D070 20656874 20646E32 2C776F72 61686320 [the 2nd row, cha]
...... 46 rows not shown .....
D84D360 0A03C102 20776F72 2D6F7774 542D3833 [....row two-25-T]
D84D370 20736968 74207369 32206568 7220646E [his is the 2nd r]
D84D380 752C776F 74616470 77207365 206C6C69 [ow,updates will ]
D84D390 43206562 696D6D6F 21646574 0203002C [be Commited!,...]
D84D3A0 720703C1 7420776F 542D6F77 20736968 [...row two-This ]
D84D3B0 74207369 32206568 7220646E 752C776F [is the 2nd row,u]
D84D3C0 74616470 77207365 206C6C69 43206562 [pdates will be C]
D84D3D0 696D6D6F 21646574 0203002C 720702C1 [ommited!,......r]
D84D3E0 6F20776F 5415656E 20736968 74207369 [ow one.This is t]
D84D3F0 66206568 74737269 776F7220 61470602 [he first row..Ga]
Block header dump: 0x01800067
Seems that the block contains a lot of records, but look at the block header
nrow figures: block contains 2 live rows, and everything else is logically deleted, dead... space wasted.
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1ba7
avsp=0x1f20
tosp=0x1f22
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f74
0x14:pri[1] offs=0x1ba7
Yep, this is something you must take care of... conclusion: you cannot take lightly update operations on variable length fields.
Remember there is always a trade-off, in this case update algorithm for variable length update is optimized for performance, which is one of Oracle's virtues & pillar.
It's desirable to prevent this behavior at the design or programing stages, avoiding repeating updates over same record or making a weighted decision over CHAR, meaning a fixed and constant waste of space versus VARCHAR with this corner case.
If preventive solution is not possible, then Oracle provides ALTER TABLE instructions to cope with this and give maintenance to table, removing this "dead rows".
Quiz: ALTER TABLE {table} SHRINK SPACE or ALTER TABLE {table} SHRINK SPACE COMPACT are our saving sentences?
Which pre-requisite have in order to use any of them?
Are they interchangeable or what is the behavior distinction?
Thanks for your time reading today, I'll engage next time with updates on tables having INDEXES...
Subscribe to Oracle Database Dissected by Email
Bookmark this on Delicious
Go to OraPub.com
