Chinaunix首页 | 论坛 | 博客
  • 博客访问: 2880789
  • 博文数量: 599
  • 博客积分: 16398
  • 博客等级: 上将
  • 技术积分: 6875
  • 用 户 组: 普通用户
  • 注册时间: 2009-11-30 12:04
个人简介

WINDOWS下的程序员出身,偶尔也写一些linux平台下小程序, 后转行数据库行业,专注于ORACLE和DB2的运维和优化。 同时也是ios移动开发者。欢迎志同道合的朋友一起研究技术。 数据库技术交流群:58308065,23618606

文章分类

全部博文(599)

文章存档

2014年(12)

2013年(56)

2012年(199)

2011年(105)

2010年(128)

2009年(99)

分类: Oracle

2012-07-28 20:21:42

本文主要介绍UPDATE记录的时候导致块中如何变化:
 

点击(此处)折叠或打开

  1. SQL> create table t(id int,name varchar2(4000));
  2. Table created.
  3. SQL> insert into t values(1,'htyansp');
  4. 1 row created.
  5. SQL> insert into t values(2,'huateng');
  6. 1 row created.
  7. SQL> commit;
  8. Commit complete.
  9. SQL> alter system checkpoint;
  10. System altered.
  11. SQL> select dbms_rowid.rowid_relative_fno(rowid) file#,dbms_rowid.rowid_block_number(rowid) block# from t;
  12.      FILE# BLOCK#
  13. ---------- ----------

  14.          1 85425
  15.          1 85425

BBED> map /v
 File: /u01/app/oracle/oradata/huateng/system01.dbf (0)
 Block: 85425                                 Dba:0x00000000
------------------------------------------------------------
 KTB Data Block (Table/Cluster)
 struct kcbh, 20 bytes                      @0      
    ub1 type_kcbh                           @0      
    ub1 frmt_kcbh                           @1      
    ub1 spare1_kcbh                         @2      
    ub1 spare2_kcbh                         @3      
    ub4 rdba_kcbh                           @4      
    ub4 bas_kcbh                            @8      
    ub2 wrp_kcbh                            @12     
    ub1 seq_kcbh                            @14     
    ub1 flg_kcbh                            @15     
    ub2 chkval_kcbh                         @16     
    ub2 spare3_kcbh                         @18     
 struct ktbbh, 96 bytes                     @20     
    ub1 ktbbhtyp                            @20     
    union ktbbhsid, 4 bytes                 @24     
    struct ktbbhcsc, 8 bytes                @28     
    sb2 ktbbhict                            @36     
    ub1 ktbbhflg                            @38     
    ub1 ktbbhfsl                            @39     
    ub4 ktbbhfnx                            @40     
    struct ktbbhitl[3], 72 bytes            @44     
 struct kdbh, 14 bytes                      @116    
    ub1 kdbhflag                            @116    
    sb1 kdbhntab                            @117    
    sb2 kdbhnrow                            @118    
    sb2 kdbhfrre                            @120    
    sb2 kdbhfsbo                            @122    
    sb2 kdbhfseo                            @124    
    sb2 kdbhavsp                            @126    
    sb2 kdbhtosp                            @128    
 struct kdbt[1], 4 bytes                    @130    
    sb2 kdbtoffs                            @130    
    sb2 kdbtnrow                            @132    
 sb2 kdbr[2]                                @134    
 ub1 freespace[8022]                        @138    
 ub1 rowdata[28]                            @8160   
 ub4 tailchk                                @8188  

BBED> p rowdata
ub1 rowdata[0]                              @8160     0x2c  --记录2的开始位置
ub1 rowdata[1]                              @8161     0x00
ub1 rowdata[2]                              @8162     0x02
ub1 rowdata[3]                              @8163     0x02
ub1 rowdata[4]                              @8164     0xc1
ub1 rowdata[5]                              @8165     0x03
ub1 rowdata[6]                              @8166     0x07
ub1 rowdata[7]                              @8167     0x68
ub1 rowdata[8]                              @8168     0x75
ub1 rowdata[9]                              @8169     0x61
ub1 rowdata[10]                             @8170     0x74
ub1 rowdata[11]                             @8171     0x65
ub1 rowdata[12]                             @8172     0x6e
ub1 rowdata[13]                             @8173     0x67
ub1 rowdata[14]                             @8174     0x2c --记录1的开始位置
ub1 rowdata[15]                             @8175     0x00
ub1 rowdata[16]                             @8176     0x02
ub1 rowdata[17]                             @8177     0x02
ub1 rowdata[18]                             @8178     0xc1
ub1 rowdata[19]                             @8179     0x02
ub1 rowdata[20]                             @8180     0x07 -7 记录列的实际字符数
ub1 rowdata[21]                             @8181     0x68 -y
ub1 rowdata[22]                             @8182     0x74 -t
ub1 rowdata[23]                             @8183     0x79 -y
ub1 rowdata[24]                             @8184     0x61 -a
ub1 rowdata[25]                             @8185     0x6e -n
ub1 rowdata[26]                             @8186     0x73 -s
ub1 rowdata[27]                             @8187     0x70 -p
BBED> p kdbr
sb2 kdbr[0]                                 @134      8058 --第一行开始相对偏移量
sb2 kdbr[1]                                 @136      8044 --第二行开始相对偏移量
 

BBED> x /rnc 8160
rowdata[0]                                  @8160   
----------
: 0x2c (KDRHFL, KDRHFF, KDRHFH)
: 0x00
:    2
col    0[2] @8163: 2
col    1[7] @8166: huateng

BBED> x /rnc 8174
rowdata[14]                                 @8174   
-----------
: 0x2c (KDRHFL, KDRHFF, KDRHFH)
: 0x00
:    2
col    0[2] @8177: 1
col    1[7] @8180: htyansp

数据从块的底部插入,而且2条记录是紧挨着的,只存储了实际的字符,并没有预留4000个字符空间。
如果将第一条记录的name列变大,那么原来行的位置没有空间存储了,如果一定要放入原来的位置,
那么势必第一条记录的第一个字段和第二条记录都要往上挪动,显然这个成本是比较高的。
实际oracle没有这么多,oracle只是把更新后的记录挪到了freespace的这块空间中了(
 ub1 freespace[8022]                        @138   )。
同时更改 row directory的指针。
注意原来第一条的指针指向8058。
BBED> p kdbr
sb2 kdbr[0]                                 @134      8058 --第一行开始相对偏移量
sb2 kdbr[1]                                 @136      8044 --第二行开始相对偏移量
下面看看更新后的结果:

点击(此处)折叠或打开

  1. SQL> select * from t;
  2.         ID NAME
  3. ---------- --------------------

  4.          1 htyansp
  5.          2 huateng
  6. SQL> update t set name='aaaaaaaaaaaaaaaaaaaa' where id=1;
  7. 1 row updated.
  8. SQL> commit;
  9. Commit complete.
  10. SQL> alter system checkpoint;
  11. System altered.
  12. SQL> select * from t;
  13.         ID NAME
  14. ---------- --------------------

  15.          1 aaaaaaaaaaaaaaaaaaaa
  16.          2 huateng
  17. SQL>


BBED> set block 85425
        BLOCK#          85425
BBED> p rowdata
ub1 rowdata[0]                              @8133     0x2c --第一条记录的新纪录
ub1 rowdata[1]                              @8134     0x02
ub1 rowdata[2]                              @8135     0x02
ub1 rowdata[3]                              @8136     0x02
ub1 rowdata[4]                              @8137     0xc1
ub1 rowdata[5]                              @8138     0x02
ub1 rowdata[6]                              @8139     0x14
ub1 rowdata[7]                              @8140     0x61
ub1 rowdata[8]                              @8141     0x61
ub1 rowdata[9]                              @8142     0x61
ub1 rowdata[10]                             @8143     0x61
ub1 rowdata[11]                             @8144     0x61
ub1 rowdata[12]                             @8145     0x61
ub1 rowdata[13]                             @8146     0x61
ub1 rowdata[14]                             @8147     0x61
ub1 rowdata[15]                             @8148     0x61
ub1 rowdata[16]                             @8149     0x61
ub1 rowdata[17]                             @8150     0x61
ub1 rowdata[18]                             @8151     0x61
ub1 rowdata[19]                             @8152     0x61
ub1 rowdata[20]                             @8153     0x61
ub1 rowdata[21]                             @8154     0x61
ub1 rowdata[22]                             @8155     0x61
ub1 rowdata[23]                             @8156     0x61
ub1 rowdata[24]                             @8157     0x61
ub1 rowdata[25]                             @8158     0x61
ub1 rowdata[26]                             @8159     0x61
ub1 rowdata[27]                             @8160     0x2c --第二天记录
ub1 rowdata[28]                             @8161     0x00
ub1 rowdata[29]                             @8162     0x02
ub1 rowdata[30]                             @8163     0x02
ub1 rowdata[31]                             @8164     0xc1
ub1 rowdata[32]                             @8165     0x03
ub1 rowdata[33]                             @8166     0x07
ub1 rowdata[34]                             @8167     0x68
ub1 rowdata[35]                             @8168     0x75
ub1 rowdata[36]                             @8169     0x61
ub1 rowdata[37]                             @8170     0x74
ub1 rowdata[38]                             @8171     0x65
ub1 rowdata[39]                             @8172     0x6e
ub1 rowdata[40]                             @8173     0x67
ub1 rowdata[41]                             @8174     0x2c --第一条记录老记录
ub1 rowdata[42]                             @8175     0x00
ub1 rowdata[43]                             @8176     0x02
ub1 rowdata[44]                             @8177     0x02
ub1 rowdata[45]                             @8178     0xc1
ub1 rowdata[46]                             @8179     0x02
ub1 rowdata[47]                             @8180     0x07
ub1 rowdata[48]                             @8181     0x68
ub1 rowdata[49]                             @8182     0x74
ub1 rowdata[50]                             @8183     0x79
ub1 rowdata[51]                             @8184     0x61
ub1 rowdata[52]                             @8185     0x6e
ub1 rowdata[53]                             @8186     0x73
ub1 rowdata[54]                             @8187     0x70
BBED> p kdbr
sb2 kdbr[0]                                 @134      8017  --从8058变为8017
sb2 kdbr[1]                                 @136      8044

原来的老记录还可以看到:

BBED> x /rnc 8174
rowdata[41]                                 @8174     --第一条老记录
-----------
: 0x2c (KDRHFL, KDRHFF, KDRHFH)
: 0x00
:    2
col    0[2] @8177: 1
col    1[7] @8180: htyansp

BBED> x /rnc 8160
rowdata[27]                                 @8160    --第二条记录
-----------
: 0x2c (KDRHFL, KDRHFF, KDRHFH)
: 0x00
:    2
col    0[2] @8163: 2
col    1[7] @8166: huateng

BBED> x /rnc 8133
rowdata[0]                                  @8133    --第一条新纪录
----------
: 0x2c (KDRHFL, KDRHFF, KDRHFH)
: 0x02
:    2
col    0[2] @8136: 1
col   1[20] @8139: aaaaaaaaaaaaaaaaaaaa

BBED>

 
阅读(736) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~