[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.
--//標識為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內容看連結：
--//以前看過，現在應該重新再看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.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.
--//標識為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內容看連結：
--//以前看過，現在應該重新再看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時,會在索引段記錄修改前後的塊.具體一些細節看
--// ,裡面有演示.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2217009/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- LOB欄位相關概念(自動建立LOB索引段和重建索引方法)索引
- [20181022]lob欄位的lobid來之那裡.txt
- [20210208]lob欄位與查詢的問題.txt
- [20181031]lob欄位與布隆過濾.txt
- [20231008]bbed探究lob段.txt
- ORACLE LOB大欄位維護Oracle
- [20190810]如何索引一個超長欄位.txt索引
- [20180408]那些函式索引適合欄位的查詢.txt函式索引
- [20181021]臨時表lob段建立在哪裡.txt
- [20231020]增加欄位的問題.txt
- [重慶思莊每日技術分享]-重建LOB欄位上的IndexIndex
- oracle複合索引介紹(多欄位索引)Oracle索引
- PG裡常見的欄位有索引但未使用索引的原因索引
- 【ORA-01555】Oracle LOB欄位匯出 報錯 ORA-01555Oracle
- 怎麼給字串欄位加索引？字串索引
- [20180613]縮短欄位長度.txt
- Oracle資料庫高水位釋放——LOB欄位空間釋放Oracle資料庫
- SQLServer索引優化（1）：對於有order by欄位的建索引策略SQLServer索引優化
- MySQL null值欄位是否使用索引的總結MySqlNull索引
- [20190227]簡單探究tab$的bojb#欄位.txt
- [20180905]lob與direct path read.txt
- [20210423]建立檢視以及欄位長度.txt
- 外來鍵欄位未建索引引發的死鎖索引
- [20190531]lob型別pctversion 和 retention.txt型別
- ORACLE 資料匯出LOB欄位報錯ORA-31693,ORA-02354,ORA-22924Oracle
- C#學習筆記-欄位、屬性、索引器C#筆記索引
- [20201109]11.2.0.4增加欄位與預設值問題.txt
- Oracle 計算欄位選擇性 判別列的索引潛力Oracle索引
- [20200211]檢視v$db_object_cache的CHILD_LATCH欄位.txtObject
- pydantic 欄位欄位校驗
- 獲取評論相關的欄位值一段php程式碼PHP
- -206 錯誤. 在表中找不到對應的資料欄位txt
- 【Mongo】mongo更新欄位為另一欄位的值Go
- 用Elasticsearch做大規模資料的多欄位、多型別索引檢索Elasticsearch多型型別索引
- PostgreSQL資料庫多列複合索引的欄位順序選擇原理SQL資料庫索引
- [20190312]檢視v$datafile欄位OFFLINE_CHANGE#, ONLINE_CHANGE#.txt
- [20210421]12c以上版本增加欄位與預設值.txt
- fastadmin 新增欄位記圖片欄位AST