[20130307]clob字段的简单探究2.txt

lfreeali 2013-03-07

SQL dump

[20130307]clob字段的简单探究2.txt

前段时间研究一下clob字段.看看函数empty_clob()与null的区别.今天测试有信息插入的情况.

http://space.itpub.net/267265/viewspace-755269
参考文档:
http://www.juliandyke.com/Presentations/LOBInternals.ppt

从上次的测试可以看出:
1.clob字段NULL与empty_clob()是不同的。
2.如果clob字段有信息,除了保存信息外额外要消耗36字节来保存相关信息。


继续上次的测试:

在保存的clob信息有两个选项,缺省ENABLE STORAGE IN ROW (default),在这种情况下:
1.如果保存的长度小于等于3964,lob的信息保存在块内.
2.如果保存的长度大于3964,lob的信息保存在块外的lob segment内.

如果建表时选择DISABLE STORAGE IN ROW,这种情况下,无论什么情况:
lob的信息都保存在lob dsegemnt内.
--3964+36正好等于4000.

1.建立测试环境:

SQL> select * from v$version where rownum
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

SQL> select * from v$nls_parameters where parameter='NLS_CHARACTERSET';
PARAMETER           VALUE     
------------------- ---------
NLS_CHARACTERSET    ZHS16GBK

--安装的语言选择NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK

drop table t purge ;
create table t ( id number,c clob);
insert into t values (1,lpad('a',12,'a'));
insert into t values (2,lpad('b',4000,'b')||lpad('c',4000,'c'));

SQL> insert into t values (2,lpad('b',4000,'b')||lpad('c',4000,'c'));
insert into t values (2,lpad('b',4000,'b')||lpad('c',4000,'c'))
            *
ERROR at line 1:
ORA-01489: result of string concatenation is too long
--不能这样插入,超长了.

declare
    v1 clob;
begin
    for idx in 1 .. 2 loop
        v1 := v1 || rpad('x', 4000, 'x');
    end loop;
    insert into t values (2,v1);
end;
/

PL/SQL procedure successfully completed.
--奇怪,这样插入与拼接在PL/sql中并不报错.
commit ;

SQL> column c format a30
SQL> select rowid,t.* from t ;
SQL> select rowid,t.* from t ;

ROWID                      ID C
------------------ ---------- ------------------------------
AABB2VAAEAAAAWfAAA          1 aaaaaaaaaaaa
AABB2VAAEAAAAWfAAB          2 xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
                              xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
                              xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
                              ...
                              xxxxxxxxxxxxxxxxxxxx

                         
SQL> @lookup_rowid AABB2VAAEAAAAWfAAA

    OBJECT       FILE      BLOCK        ROW
---------- ---------- ---------- ----------
    269717          4       1439          0

--我插入一个长度12,另外一个长度8000的字符在clob字段中.

--保证写到磁盘.
SQL> alter system checkpoint;
System altered.


2.使用bbed观察如下:
BBED> p *kdbr[1]
rowdata[0]
----------
ub1 rowdata[0]                              @8070     0x2c

BBED> x /2rnc
rowdata[0]                                  @8070
----------
flag@8070: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8071: 0x01
cols@8072:    2

col    0[2] @8073: 2
col   1[44] @8076: .T..........................................

rowdata[51]                                 @8121
-----------
flag@8121: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8122: 0x01
cols@8123:    2

col    0[2] @8124: 1
col   1[60] @8127: .T...................(...............a.a.a.a.a.a.a.a.a.a.a.a

--第1次插入在块底,clob占用60字节,而第2次插入仅仅占用44字节(不算前面的长度指示器).

3.看数据块的转储:
SQL> alter system dump datafile 4 block 1439;

Block header dump:  0x0100059f
 Object id on Block? Y
 seg/obj: 0x41d95  csc: 0x00.c015c422  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1000598 ver: 0x01 opc: 0
     inc: 0  exflg: 0

 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0005.01a.0000351f  0x00c0387e.169d.1b  --U-    2  fsc 0x0000.c015c606
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x0100059f
data_block_dump,data header at 0x2a973b5264
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0x2a973b5264
     76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f22
avsp=0x1f0c
tosp=0x1f0c
0xe:pti[0]  nrow=2  ffs=0
0x12:pri[0] ffs=0x1f55
0x14:pri[1] ffs=0x1f22
block_row_dump:
tab 0, row 0, @0x1f55
tl: 67 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 02
col  1: [60]            
 00 54 00 01 02 0c 80 00 00 02 
 00 00 00 01 00 00 01 e1 ca 17 
 00 28 09 00 00 00 00 00 00 18
 00 00 00 00 00 01 00 61 00 61
 00 61 00 61 00 61 00 61 00 61
 00 61 00 61 00 61 00 61 00 61
LOB
Locator:
  Length:        84(60)
  Version:        1
  Byte Length:    2
  LobID: 00.00.00.01.00.00.01.e1.ca.17
  Flags[ 0x02 0x0c 0x80 0x00 ]:
    Type: CLOB
    Storage: BasicFile
    Enable Storage in Row
    Characterset Format: IMPLICIT
    Partitioned Table: No
    Options: VaringWidthReadWrite
  Inode:
    Size:     40
    Flag:     0x09 [ Valid DataInRow ]
    Future:   0x00 (should be '0x00')
    Blocks:   0
    Bytes:    24
    Version:  00000.0000000001
    Inline data[24]
Dump of memory from 0x0000002A973B71E4 to 0x0000002A973B71FC
2A973B71E0          61006100 61006100 61006100      [.a.a.a.a.a.a]
2A973B71F0 61006100 61006100 61006100           [.a.a.a.a.a.a]
tab 0, row 1, @0x1f22
tl: 51 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 03
col  1: [44]            
 00 54 00 01 02 0c 80 00 00 02 
 00 00 00 01 00 00 01 e1 ca 18
 00 18 05 00 00 00 00 01 1e bc 
 00 00 00 00 00 02 01 00 05 a7 
 01 00 05 a3
LOB
Locator:
  Length:        84(44)
  Version:        1
  Byte Length:    2
  LobID: 00.00.00.01.00.00.01.e1.ca.18
  Flags[ 0x02 0x0c 0x80 0x00 ]:
    Type: CLOB
    Storage: BasicFile
    Enable Storage in Row
    Characterset Format: IMPLICIT
    Partitioned Table: No
    Options: VaringWidthReadWrite
  Inode:
    Size:     24
    Flag:     0x05 [ Valid InodeInRow(ESIR) ]
    Future:   0x00 (should be '0x00')
    Blocks:   1
    Bytes:    7868
    Version:  00000.0000000002
    DBA Array[2]:
      0x010005a7 0x010005a3
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 1439 maxblk 1439


4.看看lpad('a',12,'a')

col  1: [60]            
 00 54 00 01 02 0c 80 00 00 02   
 00 00 00 01 00 00 01 e1 ca 17   
 00 28 09 00 00 00 00 00 00 18   
 00 00 00 00 00 01 
                   00 61 00 61   
 00 61 00 61 00 61 00 61 00 61
 00 61 00 61 00 61 00 61 00 61

Inode 占用16bytes
Body Length占2bytes: 00 28                  ==> 等于0x28=40,也就是60-前面的20字节(header的长度+lobid的长度)=40(0x28),正好一致.
Flags      占2bytes: 09 00                  ==> [ Valid DataInRow ]
LOB Length 占6bytes: 00 00 00 00 00 18      ==> 0x18=24,虽然输入12个字符的'a',而实际上保存的信息是unicode变量,字符'a'占用2
                                                个字节,这样12个字符'a'占用24个字节.
Version    占6bytes: 00 00 00 00 00 01

5.看看第2条记录的情况:
col  1: [44]            
 00 54 00 01 02 0c 80 00 00 02   
 00 00 00 01 00 00 01 e1 ca 18   
 00 18 05 00 00 00 00 01 1e bc   
 00 00 00 00 00 02
                   01 00 05 a7 
 01 00 05 a3

Inode 占用16bytes
Body Length占2bytes: 00 18                 ==> 等于0x18=24,也就是44-前面的20字节(header的长度+lobid的长度)=24(0x18),正好一致.         
Flags      占2bytes: 05 00                 ==> [ Valid InodeInRow(ESIR) ] ,说明节点信息在行内,而不是数据本身.
LOB Length 占6bytes: 00 00 00 01 1e bc     ==> 01 表示块数量,为什么是1?难道从0开始计数吗?
                                               0x1ebc => 7868,这个又表示什么呢?而且我插入信息是8000个'x',应该占用16000字
                                               节.
Version    占6bytes: 00 00 00 00 00 02

blob占的块地址: 01 00 05 a7  01 00 05 a3  ==> 与这个DBA Array[2]: 0x010005a7 0x010005a3相对应.

SQL> host cat dfb.sql
select
dbms_utility.data_block_address_file(to_number('&1','xxxxxxxxxxxxxxxx')) rfile#,
dbms_utility.data_block_address_block(to_number('&&1','xxxxxxxxxxxxxxxx')) block#
from dual;

SQL> @dfb 010005a7

    RFILE#     BLOCK#
---------- ----------
         4       1447

SQL> @dfb 010005a3

    RFILE#     BLOCK#
---------- ----------
         4       1443


--占用2个数据块,数据块大小8k.

6.再来转储这两块就能很清楚的知道lob如何存储的了.
SQL> alter system dump datafile 4 block 1447;
System altered.

SQL> alter system dump datafile 4 block 1443;
System altered.

Start dump data blocks tsn: 4 file#:4 minblk 1447 maxblk 1447
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4, rdba=16778663
Block dump from disk:
buffer tsn: 4 rdba: 0x010005a7 (4/1447)
scn: 0x0000.c015c603 seq: 0x02 flg: 0x04 tail: 0xc6032802
frmt: 0x02 chkval: 0xab62 type: 0x28=PAGETABLE MANAGED LOB BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000002A96FCCA00 to 0x0000002A96FCEA00
2A96FCCA00 0000A228 010005A7 C015C603 04020000  [(...............]
2A96FCCA10 0000AB62 00041D96 01000000 E1010000  [b...............]
2A96FCCA20 000018CA 00000001 00000000 00000000  [................]
2A96FCCA30 010005A0 00000000 78007800 78007800  [.........x.x.x.x]
2A96FCCA40 78007800 78007800 78007800 78007800  [.x.x.x.x.x.x.x.x]
        Repeat 506 times
2A96FCE9F0 78007800 78007800 78007800 C6032802  [.x.x.x.x.x.x.(..]
Long field block dump:
Object Id   269718
LobId: 0001001E1CA18 PageNo        0
Version: 0x0000.00000001  pdba: 16778656
00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78
00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78
00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78
...
    00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78
End dump data blocks tsn: 4 file#: 4 minblk 1447 maxblk 1447

Start dump data blocks tsn: 4 file#:4 minblk 1443 maxblk 1443
Block dump from cache:
Dump of buffer cache at level 4 for tsn=4, rdba=16778659
Block dump from disk:
buffer tsn: 4 rdba: 0x010005a3 (4/1443)
scn: 0x0000.c015c603 seq: 0x02 flg: 0x04 tail: 0xc6032802
frmt: 0x02 chkval: 0xab67 type: 0x28=PAGETABLE MANAGED LOB BLOCK
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0000002A96FCCA00 to 0x0000002A96FCEA00
2A96FCCA00 0000A228 010005A3 C015C603 04020000  [(...............]
2A96FCCA10 0000AB67 00041D96 01000000 E1010000  [g...............]
2A96FCCA20 000018CA 00000001 00000000 00000001  [................]
2A96FCCA30 010005A0 00000000 78007800 78007800  [.........x.x.x.x]
2A96FCCA40 78007800 78007800 78007800 78007800  [.x.x.x.x.x.x.x.x]
        Repeat 490 times
2A96FCE8F0 78007800 20002000 20002000 20002000  [.x.x. . . . . . ]
2A96FCE900 20002000 20002000 20002000 20002000  [. . . . . . . . ]
        Repeat 14 times
2A96FCE9F0 20002000 20002000 20002000 C6032802  [. . . . . . .(..]
Long field block dump:
Object Id   269718
LobId: 0001001E1CA18 PageNo        1
Version: 0x0000.00000001  pdba: 16778656
00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78
...
00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 78 00 20 00 20
00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20
.....
00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20 00 20
End dump data blocks tsn: 4 file#: 4 minblk 1443 maxblk 1443

--
Start dump data blocks tsn: 4 file#:4 minblk 1447 maxblk 1447
2A96FCCA00 0000A228 010005A7 C015C603 04020000  [(...............]
2A96FCCA10 0000AB62 00041D96 01000000 E1010000  [b...............]
2A96FCCA20 000018CA 00000001 00000000 00000000  [................]
2A96FCCA30 010005A0 00000000 78007800 78007800  [.........x.x.x.x]

--前面header:14*4=56个字节,里面的信息(我乱猜一些)
010005A7:块地址
scn: 0x0000.c015c603 seq: 0x02 flg: 0x04 
00041D96=>object_id  269718=>0x41D96
--后面结尾tailer:4个字节.应该是校验信息

--这样实际的lob 1块最多占用 8192-56-4=8132字节 .如果不足后面补0020应该是空格的编码.
--16000-8132=7868,正好说明前面的7868是保存最后一个块数据剩余信息的长度.

总结:
在写之前,我参考了www.juliandyke.com/Presentations/LOBInternals.ppt ,幸亏有这个文档,里面的许多的信息我都是胡乱猜测的.

在NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK情况下,英文信息或者一些数字在clob中1个字符占用2个字节,这样可能会导致空间
的浪费,3964/2=1982,这样如果保存像'a','1'这样的信息仅仅在块内保存1982个字符.当然如果是汉字的情况可能问题不大,我个人的
建议如果clob保存的信息不超过4000个byte,没有必要选择clob类型,而选择varchar2更好一些,如果超过4000个byte,选择blob可能要
好过clob,因为blob是原样保存的.

如果你的数据块不是8k,而是16k或者32k,为lob字段指定chunk指定的块大小如果不特别指定的情况下,默认是建表空间的块大小,这样
如果你保存的clob信息都是上面的英文的话,譬如你clob的平均长度都是2000的情况,这样clob信息保存在块外,占用一个8k的信息,导
致空间的大量浪费.

我写的clob的一些链接如下:
http://space.itpub.net/267265/viewspace-752202  [20130106]关于不同字符集下clob字段的存储问题.txt
http://space.itpub.net/267265/viewspace-755269  [20130301]clob字段的empty_clob与NULL.txt

登录 后评论
下一篇
云栖号资讯小编
20036人浏览
2020-07-13
相关推荐
[20130307]clob与varchar2.txt
571人浏览
2013-03-07 17:21:42
ORACLE clob字段整理
882人浏览
2014-07-09 15:20:20
Oracle 如何处理CLOB字段
947人浏览
2017-11-14 01:08:00
[20180416]clob的插入.txt
894人浏览
2018-04-16 10:23:46
0
0
0
715