[20181020]lob字段的索引段.txt

[20181020]lob字段的索引段.txt

--//链接:http://www.itpub.net/thread-2105833-1-1.html的讨论.
--//在讨论前先看看lob字段的索引段.

1.环境:
SCOTT@test01p> @ver1
PORT_STRING                    VERSION        BANNER                                                                               CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0           12.2.0.1.0     Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production              0

CREATE TABLE Tx
( ID NUMBER,
  IMAGE cLOB
)
LOB (IMAGE) STORE AS  basicfile  (   ENABLE STORAGE IN ROW CHUNK 8192 RETENTION   NOCACHE  ) ;

SCOTT@test01p> select table_name,index_name,tablespace_name from user_indexes where table_name ='TX' ;
TABLE_NAME           INDEX_NAME                     TABLESPACE_NAME
-------------------- ------------------------------ --------------------
TX                   SYS_IL0000022836C00002$$       USERS

SCOTT@test01p> select table_name,column_name,segment_name,tablespace_name from USER_LOBS where  table_name ='TX' ;
TABLE_NAME           COLUMN_NAME          SEGMENT_NAME                   TABLESPACE_NAME
-------------------- -------------------- ------------------------------ --------------------
TX                   IMAGE                SYS_LOB0000022836C00002$$      USERS

SCOTT@test01p> select * from dba_extents where owner=user and segment_name in ('SYS_IL0000022836C00002$$', 'SYS_LOB0000022836C00002$$');
no rows selected
--//延迟段建立，导致没有段的分配。

2.插入数据看看:

SCOTT@test01p> insert into tx values (1,lpad('a',4000,'a'));
1 row created.

SCOTT@test01p> commit ;
Commit complete.

SCOTT@test01p> column PARTITION_NAME noprint
SCOTT@test01p> select * from dba_extents where owner=user and segment_name in ('SYS_IL0000022836C00002$$', 'SYS_LOB0000022836C00002$$');
OWNER                SEGMENT_NAME                   SEGMENT_TYPE       TABLESPACE_NAME       EXTENT_ID    FILE_ID   BLOCK_ID      BYTES     BLOCKS RELATIVE_FNO
-------------------- ------------------------------ ------------------ -------------------- ---------- ---------- ---------- ---------- ---------- ------------
SCOTT                SYS_IL0000022836C00002$$       LOBINDEX           USERS                         0         11        192      65536          8           11
SCOTT                SYS_LOB0000022836C00002$$      LOBSEGMENT         USERS                         0         11        184      65536          8           11

SCOTT@test01p> select segment_type,segment_name,header_file,header_block from dba_segments
               where owner=user and segment_name in ('SYS_IL0000022836C00002$$', 'SYS_LOB0000022836C00002$$');
SEGMENT_TYPE       SEGMENT_NAME                   HEADER_FILE HEADER_BLOCK
------------------ ------------------------------ ----------- ------------
LOBINDEX           SYS_IL0000022836C00002$$                11          194
LOBSEGMENT         SYS_LOB0000022836C00002$$               11          186

SCOTT@test01p> alter system checkpoint;
System altered.

--//转储lob索引段的root节点看看.
SCOTT@test01p> alter system dump datafile 11 block 195;
System altered.

--//你可以发现转储索引没有信息：
Block header dump:  0x02c000c3
 Object id on Block? Y
 seg/obj: 0x5936  csc:  0x00000000002c93e1  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x2c000c0 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn  0x00000000002c93e1
Leaf block dump
===============
header address 32313444=0x1ed1064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 0
kdxcofbo 36=0x24
kdxcofeo 8036=0x1f64
kdxcoavs 8000
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 32
kdxlebksz 8036
*** dummy key ***
row#0[8000] flag: -------, lock: 0, len=36, data:(32):
 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
 00 00 00 00 00 00 00
col 0; len 0; (0):
col 1; len 0; (0):
----- end of leaf block Logical dump -----
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 11 minblk 195 maxblk 195
--//看来即使lob段有数据,lob索引段未必有信息.插入大的lob看看.

3.插入大一些lob:

CREATE OR REPLACE DIRECTORY TMP_EXPDP AS 'D:\tmp\expdp\';
GRANT EXECUTE, READ, WRITE ON DIRECTORY TMP_EXPDP TO SCOTT WITH GRANT OPTION;

D:\tmp\expdp>ls -l 1.txt
-rw-rw-rw-   1 user     group      418209 Oct 20 20:08 1.txt

$ cat c3.txt
DECLARE
   b_file        BFILE;
   b_lob         CLOB;
   src_offset    INT := 1;
   dest_offset   INT := 1;
   csid          INT := 0;
   lc            INT := 0;
   warning       INT;
BEGIN
   INSERT INTO tx
        VALUES (2, EMPTY_CLOB ())
        RETURN image
          INTO b_lob;

   b_file := BFILENAME ('TMP_EXPDP', '1.txt');
   DBMS_LOB.open (b_file, DBMS_LOB.file_readonly);
   DBMS_LOB.loadclobfromfile
   (
      b_lob
     ,b_file
     ,DBMS_LOB.getlength (b_file)
     ,dest_offset
     ,src_offset
     ,csid
     ,lc
     ,warning
   );
   DBMS_LOB.close (b_file);
   COMMIT;
END;
/


SCOTT@test01p> @ c3.txt
PL/SQL procedure successfully completed.

SCOTT@test01p> alter system checkpoint ;
System altered.

SCOTT@test01p> alter system dump datafile 11 block 195;
System altered.

--//检查转储：
Block header dump:  0x02c000c3
 Object id on Block? Y
 seg/obj: 0x5936  csc:  0x00000000002c93e1  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x2c000c0 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0x0003.017.0000021e  0x01802279.0054.41  --U-   12  fsc 0x0000.002c9464
Leaf block dump
===============
header address 32313444=0x1ed1064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 12
kdxcofbo 60=0x3c
kdxcofeo 7436=0x1d0c
kdxcoavs 7376
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 32
--//参看链接：http://blog.itpub.net/267265/viewspace-2124744/=>[20160908]唯一索引与非唯一索引.txt
kdxlebksz 8036
row#0[7986] flag: -------, lock: 2, len=50, data:(32):
 02 c0 00 d1 02 c0 00 d5 02 c0 00 d6 02 c0 00 d2 02 c0 00 d3 02 c0 00 d4 02
 c0 00 df 02 c0 00 d9
col 0; len 10; (10):  00 00 00 01 00 00 00 34 a6 54
                      @@@@@@@@@@@@@@@@@@@@@@@@@@@@@
col 1; len 4; (4):  00 00 00 0c
                    ~~~~~~~~~~~
row#1[7936] flag: -------, lock: 2, len=50, data:(32):
 02 c0 00 dd 02 c0 00 de 02 c0 00 da 02 c0 00 db 02 c0 00 dc 02 c0 00 e7 02
 c0 00 e1 02 c0 00 e5
col 0; len 10; (10):  00 00 00 01 00 00 00 34 a6 54
col 1; len 4; (4):  00 00 00 14
row#2[7886] flag: -------, lock: 2, len=50, data:(32):
 02 c0 00 e6 02 c0 00 e2 02 c0 00 e3 02 c0 00 e4 02 c0 00 ef 02 c0 00 e9 02
 c0 00 ed 02 c0 00 ee
col 0; len 10; (10):  00 00 00 01 00 00 00 34 a6 54
col 1; len 4; (4):  00 00 00 1c
row#3[7836] flag: -------, lock: 2, len=50, data:(32):
 02 c0 00 ea 02 c0 00 eb 02 c0 00 ec 02 c0 00 f7 02 c0 00 f1 02 c0 00 f5 02
 c0 00 f6 02 c0 00 f2
col 0; len 10; (10):  00 00 00 01 00 00 00 34 a6 54
col 1; len 4; (4):  00 00 00 24
row#4[7786] flag: -------, lock: 2, len=50, data:(32):
 02 c0 00 f3 02 c0 00 f4 02 c0 00 ff 02 c0 00 f9 02 c0 00 fd 02 c0 00 fe 02
 c0 00 fa 02 c0 00 fb
col 0; len 10; (10):  00 00 00 01 00 00 00 34 a6 54
col 1; len 4; (4):  00 00 00 2c
row#5[7736] flag: -------, lock: 2, len=50, data:(32):
 02 c0 00 fc 02 c0 01 07 02 c0 01 01 02 c0 01 05 02 c0 01 06 02 c0 01 02 02
 c0 01 03 02 c0 01 04
col 0; len 10; (10):  00 00 00 01 00 00 00 34 a6 54
col 1; len 4; (4):  00 00 00 34
row#6[7686] flag: -------, lock: 2, len=50, data:(32):
 02 c0 01 0f 02 c0 01 09 02 c0 01 0d 02 c0 01 0e 02 c0 01 0a 02 c0 01 0b 02
 c0 01 0c 02 c0 01 17
col 0; len 10; (10):  00 00 00 01 00 00 00 34 a6 54
col 1; len 4; (4):  00 00 00 3c
row#7[7636] flag: -------, lock: 2, len=50, data:(32):
 02 c0 01 11 02 c0 01 15 02 c0 01 16 02 c0 01 12 02 c0 01 13 02 c0 01 14 02
 c0 01 1f 02 c0 01 19
col 0; len 10; (10):  00 00 00 01 00 00 00 34 a6 54
col 1; len 4; (4):  00 00 00 44
row#8[7586] flag: -------, lock: 2, len=50, data:(32):
 02 c0 01 1d 02 c0 01 1e 02 c0 01 1a 02 c0 01 1b 02 c0 01 1c 02 c0 01 27 02
 c0 01 21 02 c0 01 25
col 0; len 10; (10):  00 00 00 01 00 00 00 34 a6 54
col 1; len 4; (4):  00 00 00 4c
row#9[7536] flag: -------, lock: 2, len=50, data:(32):
 02 c0 01 26 02 c0 01 22 02 c0 01 23 02 c0 01 24 02 c0 01 2f 02 c0 01 29 02
 c0 01 2d 02 c0 01 2e
col 0; len 10; (10):  00 00 00 01 00 00 00 34 a6 54
col 1; len 4; (4):  00 00 00 54
row#10[7486] flag: -------, lock: 2, len=50, data:(32):
 02 c0 01 2a 02 c0 01 2b 02 c0 01 2c 02 c0 01 37 02 c0 01 31 02 c0 01 35 02
 c0 01 36 02 c0 01 32
col 0; len 10; (10):  00 00 00 01 00 00 00 34 a6 54
col 1; len 4; (4):  00 00 00 5c
row#11[7436] flag: -------, lock: 2, len=50, data:(32):
 02 c0 01 33 02 c0 01 34 02 c0 01 3f 00 00 00 00 00 00 00 00 00 00 00 00 00
 00 00 00 00 00 00 00
col 0; len 10; (10):  00 00 00 01 00 00 00 34 a6 54
col 1; len 4; (4):  00 00 00 64
----- end of leaf block Logical dump -----
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 11 minblk 195 maxblk 195

--//可以发现索引插入12条键值。
--//索引键值col 0; len 10; (10):  00 00 00 01 00 00 00 34 a6 54 都是一样的.标识lobid.
--//http://www.juliandyke.com/Presentations/Presentations.php#LOBInternals标识为lobid,如何得来呢?
Basic Files LOB ID

. 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 <X><Y> where
. <X> is a currently unknown 4-byte number (always 1)
. <Y> is a 6-byte number generated from sequence SYS.IDGEN$
. For example:

SELECT sequence_owner, sequence_name, nextvalue, increment_by, cache_size FROM v$_sequences where sequence_name='IDGEN1$' ;

Sequence Owner Sequence Name Next Value Increment By Cache Size
SYS IDGEN1$ 37401 50 20
--//索引键值col 1表示first chunk number.00 00 00 0c表示12,也就是第12chunk(从0开始记数,块内已经保存12个chunk了)

3.转储lob段看看：
SCOTT@test01p> select rowid,tx.id from tx;
ROWID                      ID
------------------ ----------
AAAFk0AALAAAAC1AAA          1
AAAFk0AALAAAAC1AAB          2

SCOTT@test01p> @ rowid AAAFk0AALAAAAC1AAA
    OBJECT       FILE      BLOCK        ROW ROWID_DBA            DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
     22836         11        181          0  0x2C000B5           11,181               alter system dump datafile 11 block 181

SCOTT@test01p> alter system dump datafile 11 block 181;
System altered.

--//检查转储：
Block header dump:  0x02c000b5
 Object id on Block? Y
 seg/obj: 0x5934  csc:  0x00000000002c93e8  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x2c000b0 ver: 0x01 opc: 0
     inc: 0  exflg: 0
 
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0009.002.000002cc  0x01800698.005b.22  --U-    1  fsc 0x0000.002c93ea
0x02   0x0003.017.0000021e  0x01802279.0054.42  --U-    1  fsc 0x0000.002c9464
bdba: 0x02c000b5
data_block_dump,data header at 0x1ed1064
===============
tsiz: 0x1f98
hsiz: 0x16
pbl: 0x01ed1064
     76543210
flag=--------
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1ee3
avsp=0x1ef8
tosp=0x1ef8
0xe:pti[0]    nrow=2    offs=0
0x12:pri[0]    offs=0x1f69
0x14:pri[1]    offs=0x1ee3
block_row_dump:
tab 0, row 0, @0x1f69
tl: 47 fb: --H-FL-- lb: 0x1  cc: 2
col  0: [ 2]  c1 02
col  1: [40]
 00 70 00 01 02 0c 80 00 00 02 00 00 00 01 00 00 00 34 a6 53 00 14 05 00 00
 00 00 00 1f 40 00 00 00 00 00 02 02 c0 00 bd
                                  ~~~~~~~~~~~

tab 0, row 1, @0x1ee3
tl: 91 fb: --H-FL-- lb: 0x2  cc: 2
col  0: [ 2]  c1 03
col  1: [84]
 00 70 00 01 02 0c 80 00 00 02 00 00 00 01 00 00 00 34 a6 54 00 40 05 00 00
                               @@@@@@@@@@@@@@@@@@@@@@@@@@@@@
 00 00 66 1b 2a 00 00 00 00 00 68 02 c0 00 be 02 c0 00 bf 02 c0 00 bb 02 c0
                                  ~~~~~~~~~~~ ~~~~~~~~
 00 bc 02 c0 00 cf 02 c0 00 c9 02 c0 00 cd 02 c0 00 ce 02 c0 00 ca 02 c0 00
 cb 02 c0 00 cc 02 c0 00 d7
end_of_block_dump
End dump data blocks tsn: 4 file#: 11 minblk 181 maxblk 181

--//注意看下划线内容明显表示dba地址。@@@@@@下的内容表示键值之类的信息(看看前面索引段的转储字段col 0)

--//0x2c000bd=46137533

SCOTT@test01p> @ dfb16 0x2c000bd
    RFILE#     BLOCK# TEXT
---------- ---------- -----------------------------------------------------
        11        189 alter system dump datafile 11 block 189 ;

SCOTT@test01p> select segment_type,segment_name,header_file,header_block from dba_segments where owner=user and segment_name in ('SYS_IL0000022836C00002$$', 'SYS_LOB0000022836C00002$$','TX');
SEGMENT_TYPE       SEGMENT_NAME                   HEADER_FILE HEADER_BLOCK
------------------ ------------------------------ ----------- ------------
TABLE              TX                                      11          178
LOBINDEX           SYS_IL0000022836C00002$$                11          194
LOBSEGMENT         SYS_LOB0000022836C00002$$               11          186
        
--//第2条数据,(84-36)/4 = 12,12块根本放不下1.txt内容(1.txt大小418209)。418209*2/(8192-56-4) = 102.85
--//注：字符集影响，1个英文字符占2个字节(中文1个字占2个字节)。另外1个lob段的数据块有1个头占56字节，tail占4个字节。
--//我个人建议不要使用clob类型,最好使用blob类型,保持原样存储.
--//如果你注意前面的索引条目，可以发现1个键值最多保存8个块地址，这样
--//8*11+3 = 91,加上表段看到12。 91+12 = 103，与猜测一致。
--//如果想了解更多lob内容看链接：
http://www.juliandyke.com/Presentations/Presentations.php#LOBInternals
--//以前看过，现在应该重新再看1遍^_^。

总结：
--//1.如果这样ZALBB兄如果保存的lob字段，使用临时表，lob 索引段即使保存在system表空间，如果lob很小估计问题也不是太大。
--//8192-56-4 = 8132,8132*12 = 97584,97584/2 = 48792,也就是小于48792英文字节(48792/1024 = 47.6484375K)，不会使用lob段索引。
--//当然如果多个用户都建立lob索引段(lob很大的情况下)，这样消耗也是很可观的，毕竟放在system表空间不是很好。
--//2.另外我个人建议不要使用clob类型,而是使用blob类型,也许在一定程度节约磁盘空间.
--//3.chunk大小是定义表是可以指定的,最大32K,比如像我们应用一个lob字段一般平均lob占用2XXK,这样采用32K chunk,一定程度减少索引段使用.
--//4.lob的索引段还有维持读一致的作用,当修改lob时,会在索引段记录修改前后的块.具体一些细节看
--//  http://www.juliandyke.com/Presentations/Presentations.php#LOBInternals,里面有演示.

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

转载于:http://blog.itpub.net/267265/viewspace-2217009/