[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,里面有演示.