[20130301]clob欄位的empty_clob與NULL.txt

lfree發表於2013-03-04
Null
[20130301]clob欄位的empty_clob與NULL.txt

工作需要,開始研究一下clob欄位.看看函式empty_clob()與null的區別.

1.建立測試環境:
SQL> select * from v$version where rownum<=1;

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

create table t ( id number,c clob);
insert into t values (1,empty_clob());
insert into t values (2,NULL);
commit ;

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

ROWID                      ID C
------------------ ---------- ------------------------------
AABB0EAAEAAAAWeAAA          1
AABB0EAAEAAAAWeAAB          2

SQL> @lookup_rowid AABB0EAAEAAAAWeAAA

    OBJECT       FILE      BLOCK        ROW
---------- ---------- ---------- ----------
    269572          4       1438          0

--保證寫到磁碟.
SQL> alter system checkpoint;
System altered.

2.使用bbed觀察:
BBED> set dba   4,1438
        DBA             0x0100059e (16778654 4,1438)

BBED> map /v
 File: /u01/app/oracle11g/oradata/test/users01.dbf (4)
 Block: 1438                                  Dba:0x0100059e
------------------------------------------------------------
 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, 72 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[2], 48 bytes            @44

 struct kdbh, 14 bytes                      @100
    ub1 kdbhflag                            @100
    sb1 kdbhntab                            @101
    sb2 kdbhnrow                            @102
    sb2 kdbhfrre                            @104
    sb2 kdbhfsbo                            @106
    sb2 kdbhfseo                            @108
    sb2 kdbhavsp                            @110
    sb2 kdbhtosp                            @112

 struct kdbt[1], 4 bytes                    @114
    sb2 kdbtoffs                            @114
    sb2 kdbtnrow                            @116

 sb2 kdbr[2]                                @118

 ub1 freespace[8017]                        @122

 ub1 rowdata[49]                            @8139

 ub4 tailchk                                @8188

BBED> p *kdbr[1]
rowdata[0]
----------
ub1 rowdata[0]                              @8139     0x2c

BBED> x /2rnc
rowdata[0]                                  @8139
----------
flag@8139: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8140: 0x01
cols@8141:    1

col    0[2] @8142: 2

rowdata[6]                                  @8145
----------
flag@8145: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8146: 0x01
cols@8147:    2

col    0[2] @8148: 1
col   1[36] @8151: .T.................s................

--可以發現兩者存在不同,empty_clob()並佔用36位元組[如果加上前面的長度指示器,佔用37位元組],而NULL不佔用空間.

BBED> p  *kdbr[0]
rowdata[6]
----------
ub1 rowdata[6]                              @8145     0x2c

BBED> x /r
rowdata[6]                                  @8145
----------
flag@8145: 0x2c (KDRHFL, KDRHFF, KDRHFH)
lock@8146: 0x01
cols@8147:    2

col    0[2] @8148:  0xc1  0x02
col   1[36] @8151:  0x00  0x54  0x00  0x01  0x02  0x0c  0x80  0x00  0x00  0x02
                    0x00  0x00  0x00  0x01  0x00  0x00  0x01  0xdd  0xf4  0x73 
                    0x00  0x10  0x09  0x00  0x00  0x00  0x00  0x00  0x00  0x00  
                    0x00  0x00  0x00  0x00  0x00  0x00

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

--看看轉儲檔案:
Block header dump:  0x0100059e
 Object id on Block? Y
 seg/obj: 0x41d04  csc: 0x00.c010a962  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.020.00003023  0x00c006e2.1675.0a  --U-    2  fsc 0x0000.c010a966
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x0100059e
data_block_dump,data header at 0x2a972c5264
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0x2a972c5264
     76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1f67
avsp=0x1f4e
tosp=0x1f4e
0xe:pti[0]  nrow=2  ffs=0
0x12:pri[0] ffs=0x1f6d
0x14:pri[1] ffs=0x1f67
block_row_dump:
tab 0, row 0, @0x1f6d
tl: 43 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 02
col  1: [36]                    <== 這裡我重新排了一下版.
 00 54 00 01 02 0c 80 00 00 02  <== Header 佔10bytes
 00 00 00 01 00 00 01 dd f4 73  <== LOBID  佔10bytes
 00 10 09 00 00 00 00 00 00 00  <== Inode  佔16bytes
 00 00 00 00 00 00
LOB
Locator:
  Length:        84(36)
  Version:        1
  Byte Length:    2
  LobID: 00.00.00.01.00.00.01.dd.f4.73
  Flags[ 0x02 0x0c 0x80 0x00 ]:
    Type: CLOB
    Storage: BasicFile
    Enable Storage in Row
    Characterset Format: IMPLICIT
    Partitioned Table: No
    Options: VaringWidthReadWrite
  Inode:
    Size:     16
    Flag:     0x09 [ Valid DataInRow ]
    Future:   0x00 (should be '0x00')
    Blocks:   0
    Bytes:    0
    Version:  00000.0000000000
    Inline data[0]
Dump of memory from 0x0000002A972C71FC to 0x0000002A972C71FC
tab 0, row 1, @0x1f67
tl: 6 fb: --H-FL-- lb: 0x1  cc: 1
col  0: [ 2]  c1 03
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 1438 maxblk 1438

--11G版本轉儲的顯示很清晰:

Header 佔10bytes   : 00 54 00 01 02 0c 80 00 00 02
LOBID  佔10bytes   : 00 00 00 01 00 00 01 dd f4 73
. LOB ID is a 10 byte number identifying individual instance of a LOB
. Allocated when LOB value is created including EMPTY_CLOB() etc
. Format is  where
      is a currently unknown 4-byte number (always 1)
      is a 6-byte number generated from sequence SYS.IDGEN1$

SQL> @16to10  01ddf473
16 to 10 DEC
------------
    31323251

SQL> select * from dba_sequences where sequence_owner='SYS' and sequence_name ='IDGEN1$';

SEQUENCE_OWNER                 SEQUENCE_NAME                   MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
------------------------------ ------------------------------ ---------- ---------- ------------ - - ---------- -----------
SYS                            IDGEN1$                                 1 1.0000E+28           50 N N       1000    31323651

Inode 佔用16bytes
Body Length佔2bytes: 00 10     => 等於16也就是36-前面的20位元組(header的長度+lobid的長度)=16.
Flags      佔2bytes: 09 00
LOB Length 佔6bytes: 00 00 00 00 00 00      ==>empty_clob() 長度為0，clob自身長度為0
Version    佔6bytes: 00 00 00 00 00 00


總結：
1.從以上可以看出，clob欄位NULL與empty_clob()是不同的。
2.如果clob欄位有資訊，除了儲存資訊外額外要消耗36位元組來儲存相關資訊。
3.其他問題看後續的帖子。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-755269/，如需轉載，請註明出處，否則將追究法律責任。

相關文章