等值更新时索引维护成本

1,问题：
    发现应用程序代码中存在如下情况：在只需更新部分列时，同时更新其他列为原有值。
虽然从功能实现角度看没有问题，但从数据库角度看，引入很多额外成本，例如：更多的undo，redo，不必要的索引维护(?)。

     问题是，索引列更新时，如果值不变化，更改索引块吗？

2,结论
结论：在列值更新为原值时，索引块没有维护操作。
验证思路：通过比较索引块上的scn信息，判断在更改索引列为原值时，是否更新索引。
但不能判断这种优化在何处发生（维护索引前，还是维护索引时？）

3,验证
3.1测试表准备
drop table test;

create table test(x,y,z)
as
select rownum,rownum,rownum
from dual connect by level<10;

alter table test
add constraint pk_test primary key(x);

create index idx_test_y on test(y);

select t.*,rowid from test t;
X Y Z ROWID
1 1 1 AAAV7pAATAACDW8AAA
2 2 2 AAAV7pAATAACDW8AAB
3 3 3 AAAV7pAATAACDW8AAC
4 4 4 AAAV7pAATAACDW8AAD
5 5 5 AAAV7pAATAACDW8AAE
6 6 6 AAAV7pAATAACDW8AAF
7 7 7 AAAV7pAATAACDW8AAG
8 8 8 AAAV7pAATAACDW8AAH
9 9 9 AAAV7pAATAACDW8AAI

--查找数据块地址(本地管理表空间，每个段前两个数据块保存位图)
SELECT segment_name, header_file, header_block, blocks, extents
  FROM dba_segments
 WHERE wner = USER
   AND segment_name in('TEST','PK_TEST','IDX_TEST_Y');
SEGMENT_NAME HEADER_FILE HEADER_BLOCK BLOCKS EXTENTS
TEST 19 538043 8 1
PK_TEST 19 538051 8 1
IDX_TEST_Y 19 538059 8 1

SELECT segment_name, extent_id, file_id,block_id,blocks
  FROM dba_extents
 WHERE wner = USER
   AND segment_name in('TEST','PK_TEST','IDX_TEST_Y');
SEGMENT_NAME EXTENT_ID FILE_ID BLOCK_ID BLOCKS
TEST 0 19 538041 8
PK_TEST 0 19 538049 8
IDX_TEST_Y 0 19 538057 8  

3.2原值更新情况

alter system checkpoint;

alter system dump datafile 19 block 538044;
alter system dump datafile 19 block 538052;
alter system dump datafile 19 block 538060;

==>bocnet_ora_5483_dump_blocks1.trc

update test set x=x,y=y,z=z where x=3;
commit;

alter system checkpoint;

alter system dump datafile 19 block 538044;
alter system dump datafile 19 block 538052;
alter system dump datafile 19 block 538060;

==>bocnet_ora_6494_dump_blocks2.trc

==>scn:
                   dump1             dump2
数据块scn： 0x0006.c3c83c93   0x0006.c3c848b4
主键索引块scn： 0x0006.c3c83cc5   0x0006.c3c83cc5
Y列索引块scn： 0x0006.c3c83ce9   0x0006.c3c83ce9

结论:等值更新时,数据块有更新，索引块没有维护操作.

 

3.3更新为新值情况

alter system checkpoint;

alter system dump datafile 19 block 538044;
alter system dump datafile 19 block 538052;
alter system dump datafile 19 block 538060;

==>bocnet_ora_6922_dump_blocks3.trc

update test set x=x+10,y=y+10,z=z+10 where x=4;
commit;

alter system checkpoint;

alter system dump datafile 19 block 538044;
alter system dump datafile 19 block 538052;
alter system dump datafile 19 block 538060;

==>bocnet_ora_7041_dump_blocks4.trc

==>scn:
                   dump1             dump2
数据块scn： 0x0006.c3c848b4   0x0006.c3c84b13
主键索引块scn： 0x0006.c3c83cc5   0x0006.c3c84b13
Y列索引块scn： 0x0006.c3c83ce9   0x0006.c3c84b13

结论:更新为新值时,数据块有更新，索引块有维护操作（删除原来entry，插入新entry）.

3.4常量等值更新并dump

alter system checkpoint;

alter system dump datafile 19 block 538044;
alter system dump datafile 19 block 538052;
alter system dump datafile 19 block 538060;
==>bocnet_ora_19833_dump_blocks5.trc

--当前x=3的列y,z值都为3
update test set x=3,y=3,z=3 where x=3;
commit;

alter system checkpoint;

alter system dump datafile 19 block 538044;
alter system dump datafile 19 block 538052;
alter system dump datafile 19 block 538060;
==>bocnet_ora_7041_dump_blocks6.trc

==>scn:
                   dump1             dump2
数据块scn： 0x0006.c3c8a5de   0x0006.c3c8aadc
主键索引块scn： 0x0006.c3c8a5de   0x0006.c3c8a5de
Y列索引块scn： 0x0006.c3c8a5de   0x0006.c3c8a5de

结论:常量等值更新时,数据块有更新，索引块没有维护操作.

3.5能否通过sql跟踪判断是否对索引有维护操作?
==>结论:跟踪信息中无相关信息.是否有其他事件能提供索引维护操作?
alter session set events '10046 trace name context forever, level 12';

update bocnet.test set x=x+10,y=y+10,z=z+10 where x=9;
commit;

alter session set events '10046 trace name context off' ;
==>bocnet_ora_18640_10046.trc

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/18922393/viewspace-696342/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/18922393/viewspace-696342/