ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 等值更新时索引维护成本

等值更新时索引维护成本

原创 Linux操作系统 作者:redhouser 时间:2011-05-26 13:49:58 0 删除 编辑

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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: awk分析日志
请登录后发表评论 登录
全部评论

注册时间:2011-05-26

  • 博文量
    211
  • 访问量
    809920