update partition key is slower than insert and delete (2)

The update on partition key are actually one update followed by delete and insert.

 

 

SQL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from part_tab where d=10;
 
DBMS_ROWID.ROWID_RELATIVE_FNO( DBMS_ROWID.ROWID_BLOCK_NUMBER(
------------------------------ ------------------------------
                             6                           4624
SQL> select dbms_utility.make_data_block_address(6,4624) from dual;
 
DBMS_UTILITY.MAKE_DATA_BLOCK_A
------------------------------
                      25170448
SQL> --Get the original DBA
SQL> SELECT trim(TO_CHAR(25170448,'XXXXXXXXXXX')) FROM dual;
 
TRIM(TO_CHAR(25170448,'XXXXXXX
------------------------------
1801210
 
SQL> update part_tab set a= 1 where d=10;
 
1 row updated
 
SQL> commit;
 
Commit complete
 
SQL> select dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid) from part_tab where d=10;
 
DBMS_ROWID.ROWID_RELATIVE_FNO( DBMS_ROWID.ROWID_BLOCK_NUMBER(
------------------------------ ------------------------------
                             6                           4496
 
SQL> select dbms_utility.make_data_block_address(6,4496) from dual;
 
DBMS_UTILITY.MAKE_DATA_BLOCK_A
------------------------------
                      25170320

 

SQL> --Get the final DBA
SQL> SELECT trim(TO_CHAR(25170320,'XXXXXXXXXXX')) FROM dual;
 
TRIM(TO_CHAR(25170320,'XXXXXXX
------------------------------
1801190

 

 

 

dump the redo log for the update.

 

 

 

REDO RECORD - Thread:1 RBA: 0x000078.00000004.0080 LEN: 0x03d4 VLD: 0x09
SCN: 0x0000.0040f5e7 SUBSCN:  1 07/04/2008 11:51:45
CHANGE #1 TYP:2 CLS: 1 AFN:6 DBA:0x01801210 OBJ:53757 SCN:0x0000.0040f10d SEQ:  2 OP:11.5    ==> update the row
KTB Redo
op: 0x11  ver: 0x01 
op: F  xid:  0x0005.012.00000d8b    uba: 0x008087bd.053f.09
Block cleanout record, scn:  0x0000.0040f5e6 ver: 0x01 opt: 0x02, entries follow...
  itli: 1  flg: 2  scn: 0x0000.0040ef84
  itli: 2  flg: 2  scn: 0x0000.0040f10d
KDO Op code: URP row dependencies Disabled
  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x01801210  hdba: 0x01801209
itli: 1  ispac: 0  maxfr: 4863
tabn: 0 slot: 319(0x13f) flag: 0x2c lock: 1 ckix: 14
ncol: 4 nnew: 1 size: 0
Vector content:
col  0: [ 2]  c1 02
CHANGE #2 TYP:0 CLS:25 AFN:2 DBA:0x00800049 OBJ:4294967295 SCN:0x0000.0040f5c3 SEQ:  2 OP:5.2
ktudh redo: slt: 0x0012 sqn: 0x00000d8b flg: 0x0012 siz: 148 fbi: 0
            uba: 0x008087bd.053f.09    pxid:  0x0000.000.00000000
CHANGE #3 TYP:0 CLS: 1 AFN:6 DBA:0x01801210 OBJ:53757 SCN:0x0000.0040f5e7 SEQ:  1 OP:11.3     ==> delete the updated row
KTB Redo
op: 0x02  ver: 0x01 
op: C  uba: 0x008087bd.053f.0a
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01801210  hdba: 0x01801209
itli: 1  ispac: 0  maxfr: 4863
tabn: 0 slot: 319(0x13f)
CHANGE #4 TYP:2 CLS: 1 AFN:6 DBA:0x01801190 OBJ:53756 SCN:0x0000.0040ef7a SEQ:  1 OP:11.2    ===> insert the row to the new partition
KTB Redo
op: 0x01  ver: 0x01 
op: F  xid:  0x0005.012.00000d8b    uba: 0x008087bd.053f.0b
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01801190  hdba: 0x01801189
itli: 1  ispac: 0  maxfr: 4863
tabn: 0 slot: 318(0x13e) size/delt: 15
fb: --H-FL-- lb: 0x1  cc: 4
null: ----
col  0: [ 2]  c1 02
col  1: [ 2]  c1 0b
col  2: [ 2]  c1 0b
col  3: [ 2]  c1 0b
CHANGE #5 TYP:0 CLS:25 AFN:2 DBA:0x00800049 OBJ:4294967295 SCN:0x0000.0040f5e7 SEQ:  1 OP:5.4
ktucm redo: slt: 0x0012 sqn: 0x00000d8b srt: 0 sta: 9 flg: 0x2
ktucf redo: uba: 0x008087bd.053f.0b ext: 83 spc: 6796 fbi: 0
CHANGE #6 TYP:0 CLS:26 AFN:2 DBA:0x008087bd OBJ:4294967295 SCN:0x0000.0040f5c3 SEQ:  2 OP:5.1
ktudb redo: siz: 148 spc: 7150 flg: 0x0012 seq: 0x053f rec: 0x09
            xid:  0x0005.012.00000d8b 
ktubl redo: slt: 18 rci: 0 opc: 11.1 objn: 53619 objd: 53757 tsn: 7
Undo type:  Regular undo        Begin trans    Last buffer split:  No
Temp Object:  No
Tablespace Undo:  No
             0x00000000  prev ctl uba: 0x008087bd.053f.07
prev ctl max cmt scn:  0x0000.0040f09b  prev tx cmt scn:  0x0000.0040f0ac
txn start scn:  0x0000.0040f5d8  logon user: 61  prev brb: 8423353  prev bcl: 0 KDO undo record:
KTB Redo
op: 0x04  ver: 0x01 
op: L  itl: xid:  0x0003.01c.00000d7c uba: 0x00800acd.0361.0d
                      flg: C---    lkc:  0     scn: 0x0000.0040ef84
KDO Op code: URP row dependencies Disabled
  xtype: XAxtype KDO_KDOM2 flags: 0x00000080  bdba: 0x01801210  hdba: 0x01801209
itli: 1  ispac: 0  maxfr: 4863
tabn: 0 slot: 319(0x13f) flag: 0x2c lock: 0 ckix: 14
ncol: 4 nnew: 1 size: 0
Vector content:
col  0: [ 2]  c1 03
CHANGE #7 TYP:0 CLS:26 AFN:2 DBA:0x008087bd OBJ:4294967295 SCN:0x0000.0040f5e7 SEQ:  1 OP:5.1
ktudb redo: siz: 120 spc: 7000 flg: 0x0022 seq: 0x053f rec: 0x0a
            xid:  0x0005.012.00000d8b 
ktubu redo: slt: 18 rci: 9 opc: 11.1 objn: 53619 objd: 53757 tsn: 7
Undo type:  Regular undo       Undo type:  Last buffer split:  No
Tablespace Undo:  No
             0x00000000
KDO undo record:
KTB Redo
op: 0x02  ver: 0x01 
op: C  uba: 0x008087bd.053f.09
KDO Op code: IRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01801210  hdba: 0x01801209
itli: 1  ispac: 0  maxfr: 4863
tabn: 0 slot: 319(0x13f) size/delt: 15
fb: --H-FL-- lb: 0x1  cc: 4
null: ----
col  0: [ 2]  c1 02
col  1: [ 2]  c1 0b
col  2: [ 2]  c1 0b
col  3: [ 2]  c1 0b
CHANGE #8 TYP:0 CLS:26 AFN:2 DBA:0x008087bd OBJ:4294967295 SCN:0x0000.0040f5e7 SEQ:  2 OP:5.1
ktudb redo: siz: 80 spc: 6878 flg: 0x0022 seq: 0x053f rec: 0x0b
            xid:  0x0005.012.00000d8b 
ktubu redo: slt: 18 rci: 10 opc: 11.1 objn: 53618 objd: 53756 tsn: 7
Undo type:  Regular undo       Undo type:  Last buffer split:  No
Tablespace Undo:  No
             0x00000000
KDO undo record:
KTB Redo
op: 0x04  ver: 0x01 
op: L  itl: xid:  0x000a.01d.00000d81 uba: 0x00800bde.04b1.14
                      flg: C---    lkc:  0     scn: 0x0000.0040ee74
KDO Op code: DRP row dependencies Disabled
  xtype: XA flags: 0x00000000  bdba: 0x01801190  hdba: 0x01801189
itli: 1  ispac: 0  maxfr: 4863
tabn: 0 slot: 318(0x13e)