在前一篇 IOT, Secondary Index and Mapping Table 中提到IOT的secondary index中应该包含了IOT的primary key, 但是不知道怎么去证明一下。网上看到可以通过如下方式dump数据文件中block的内容,
To dump single block use following command:alter system dump datafile <file number> block <block number> ;
To dump multiple block use following command:
alter system dump datafile <file number> block min <first block number> block max <last block number> ;
于是想到是否可以搞搞看看。此文就对这个探索过程简单记录一下....
SQL> desc TEST_IOT;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
ID NOT NULL NUMBER
NAME VARCHAR2(128)
SQL> select * from dba_extents where segment_name='TEST_IOT';
no rows selected
SQL> select index_name, index_type from user_indexes where table_name='TEST_IOT';
INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
IDX_TEST_IOT_1 NORMAL
SYS_IOT_TOP_262816 IOT - TOP
由于在创建TEST_IOT的时候没有显示指出primary key的名字,因此系统是自动生成,也就是SYS_IOT_TOP_262816.
知道TEST_IOT对应的segment的名字,就可以通过查询dba_extents来获取TEST_IOT对应的数据文件和起始block id了,
SQL> select file_id, block_id from dba_extents where segment_name='SYS_IOT_TOP_262816';
FILE_ID BLOCK_ID
---------- ----------
4 9561
因为我是想看看TEST_IOT上的secondary index的,因此现在来分析下TEST_IOT上的二级索引 -- IDX_TEST_IOT_1,
SQL> select file_id, block_id, blocks from dba_extents where owner='FRANK' and segment_name='IDX_TEST_IOT_1';
FILE_ID BLOCK_ID BLOCKS
---------- ---------- ----------
4 9569 8
可以看到索引IDX_TEST_IOT_1位于datafile 4, 起始block id为9569, 占用了8个block. 那么把这8个block都dump出来看看....
SQL> alter system dump datafile 4 block min 9569 block max 9576;
System altered.
SET LINESIZE 100
COLUMN trace_file FORMAT A60SELECT s.sid,
s.serial#,
pa.value || '\' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||
'_ora_' || p.spid || '.trc' AS trace_file
FROM v$session s,
v$process p,
v$parameter pa
WHERE pa.name = 'user_dump_dest'
AND s.paddr = p.addr
AND s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');
SID SERIAL# TRACE_FILE
---------- ---------- ------------------------------------------------------------
107 12595 E:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCL\UDUMP/orcl_ora_2832.trc
Leaf block dump
===============
header address 161882724=0x9a62264
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x90: opcode=0: iot flags=I-- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 32
kdxcofbo 100=0x64
kdxcofeo 6988=0x1b4c
kdxcoavs 6937
kdxlespl 0
kdxlende 1
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[8003] flag: K-----, lock: 0, len=33
col 0; len 19; (19): 41 51 24 54 45 53 54 5f 51 55 45 55 45 5f 54 41 42 4c 45
col 1; len 2; (2): c1 1a
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 25 5c
row#1[7968] flag: K-----, lock: 0, len=35
col 0; len 21; (21):
41 51 24 54 45 53 54 5f 51 55 45 55 45 5f 54 41 42 4c 45 5f 52
col 1; len 2; (2): c1 1d
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 25 5c
row#2[7933] flag: K-----, lock: 0, len=35
col 0; len 21; (21):
41 51 24 54 45 53 54 5f 51 55 45 55 45 5f 54 41 42 4c 45 5f 53
col 1; len 2; (2): c1 0d
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 25 5c
row#3[7897] flag: K-----, lock: 0, len=36
col 0; len 22; (22):
41 51 24 5f 54 45 53 54 5f 51 55 45 55 45 5f 54 41 42 4c 45 5f 45
col 1; len 2; (2): c1 18
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 25 5c
row#4[7861] flag: K-----, lock: 0, len=36
col 0; len 22; (22):
41 51 24 5f 54 45 53 54 5f 51 55 45 55 45 5f 54 41 42 4c 45 5f 46
col 1; len 2; (2): c1 19
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 25 5c
row#5[7825] flag: K-----, lock: 0, len=36
col 0; len 22; (22):
41 51 24 5f 54 45 53 54 5f 51 55 45 55 45 5f 54 41 42 4c 45 5f 47
col 1; len 2; (2): c1 14
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 25 5c
row#6[7789] flag: K-----, lock: 0, len=36
col 0; len 22; (22):
41 51 24 5f 54 45 53 54 5f 51 55 45 55 45 5f 54 41 42 4c 45 5f 48
col 1; len 2; (2): c1 11
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 25 5c
row#7[7753] flag: K-----, lock: 0, len=36
col 0; len 22; (22):
41 51 24 5f 54 45 53 54 5f 51 55 45 55 45 5f 54 41 42 4c 45 5f 49
col 1; len 2; (2): c1 16
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 25 5c
row#8[7717] flag: K-----, lock: 0, len=36
col 0; len 22; (22):
41 51 24 5f 54 45 53 54 5f 51 55 45 55 45 5f 54 41 42 4c 45 5f 4e
col 1; len 2; (2): c1 0e
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 25 5c
row#9[7681] flag: K-----, lock: 0, len=36
col 0; len 22; (22):
41 51 24 5f 54 45 53 54 5f 51 55 45 55 45 5f 54 41 42 4c 45 5f 53
col 1; len 2; (2): c1 09
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 25 5c
row#10[7645] flag: K-----, lock: 0, len=36
col 0; len 22; (22):
41 51 24 5f 54 45 53 54 5f 51 55 45 55 45 5f 54 41 42 4c 45 5f 54
col 1; len 2; (2): c1 0c
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 25 5c
row#11[7609] flag: K-----, lock: 0, len=36
col 0; len 22; (22):
41 51 24 5f 54 45 53 54 5f 51 55 45 55 45 5f 54 41 42 4c 45 5f 56
col 1; len 2; (2): c1 0b
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 25 5c
row#12[7584] flag: K-----, lock: 0, len=25
col 0; len 11; (11): 4d 45 53 53 41 47 45 5f 41 50 49
col 1; len 2; (2): c1 04
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 25 5c
row#13[7559] flag: K-----, lock: 0, len=25
col 0; len 11; (11): 4d 45 53 53 41 47 45 5f 41 50 49
col 1; len 2; (2): c1 05
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 25 5c
row#14[7533] flag: K-----, lock: 0, len=26
col 0; len 12; (12): 53 59 53 5f 43 30 30 35 34 36 37 32
col 1; len 2; (2): c1 06
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 25 5c
row#15[7507] flag: K-----, lock: 0, len=26
col 0; len 12; (12): 53 59 53 5f 43 30 30 35 34 36 37 35
col 1; len 2; (2): c1 0a
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 25 5c
row#16[7474] flag: K-----, lock: 0, len=33
col 0; len 19; (19): 53 59 53 5f 49 4f 54 5f 4f 56 45 52 5f 32 36 32 34 37 32
col 1; len 2; (2): c1 13
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 25 5c
row#17[7442] flag: K-----, lock: 0, len=32
col 0; len 18; (18): 53 59 53 5f 49 4f 54 5f 54 4f 50 5f 32 36 32 34 36 38
col 1; len 2; (2): c1 0f
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 25 5c
row#18[7410] flag: K-----, lock: 0, len=32
col 0; len 18; (18): 53 59 53 5f 49 4f 54 5f 54 4f 50 5f 32 36 32 34 37 30
col 1; len 2; (2): c1 12
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 25 5c
row#19[7378] flag: K-----, lock: 0, len=32
col 0; len 18; (18): 53 59 53 5f 49 4f 54 5f 54 4f 50 5f 32 36 32 34 37 32
col 1; len 2; (2): c1 15
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 25 5c
row#20[7346] flag: K-----, lock: 0, len=32
col 0; len 18; (18): 53 59 53 5f 49 4f 54 5f 54 4f 50 5f 32 36 32 34 37 35
col 1; len 2; (2): c1 17
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 25 5c
row#21[7307] flag: K-----, lock: 0, len=39
col 0; len 25; (25):
53 59 53 5f 4c 4f 42 30 30 30 30 32 36 32 34 35 39 43 30 30 30 33 30 24 24
col 1; len 2; (2): c1 08
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 25 5c
row#22[7007] flag: K-----, lock: 2, len=21
col 0; len 8; (8): 54 45 53 54 5f 49 4f 54
col 1; len 1; (1): 80
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 25 5c
row#23[7285] flag: K--D--, lock: 2, len=22
col 0; len 8; (8): 54 45 53 54 5f 49 4f 54
col 1; len 2; (2): c1 02
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 25 5c
row#24[7253] flag: K-----, lock: 0, len=32
col 0; len 18; (18): 54 45 53 54 5f 4d 45 53 53 41 47 45 5f 54 41 42 4c 45
col 1; len 2; (2): c1 1f
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 25 5c
row#25[7226] flag: K-----, lock: 0, len=27
col 0; len 13; (13): 54 45 53 54 5f 4d 53 47 5f 54 59 50 45
col 1; len 2; (2): c1 07
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 25 5c
row#26[7202] flag: K-----, lock: 0, len=24
col 0; len 10; (10): 54 45 53 54 5f 51 55 45 55 45
col 1; len 2; (2): c1 1e
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 25 5c
row#27[7159] flag: K-----, lock: 0, len=43
col 0; len 29; (29):
54 45 53 54 5f 51 55 45 55 45 5f 43 41 4c 4c 42 41 43 4b 5f 50 52 4f 43 45
44 55 52 45
col 1; len 2; (2): c1 03
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 25 5c
row#28[7133] flag: K-----, lock: 0, len=26
col 0; len 12; (12): 54 45 53 54 5f 51 55 45 55 45 5f 4e
col 1; len 2; (2): c1 1c
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 25 5c
row#29[7107] flag: K-----, lock: 0, len=26
col 0; len 12; (12): 54 45 53 54 5f 51 55 45 55 45 5f 52
col 1; len 2; (2): c1 1b
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 25 5c
row#30[7077] flag: K-----, lock: 0, len=30
col 0; len 16; (16): 54 45 53 54 5f 51 55 45 55 45 5f 54 41 42 4c 45
col 1; len 2; (2): c1 10
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 25 5c
row#31[6988] flag: K-----, lock: 2, len=19
col 0; len 5; (5): 68 65 6c 6c 6f
col 1; len 2; (2): c1 02
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 25 5c
----- end of leaf block dump -----
现在从中随便摘取第26行(row#26)来分析,片段如下,
row#26[7202] flag: K-----, lock: 0, len=24
col 0; len 10; (10): 54 45 53 54 5f 51 55 45 55 45
col 1; len 2; (2): c1 1e
tl: 8 fb: --H-FL-- lb: 0x0 cc: 1
col 0: [ 4] 01 00 25 5c
SQL> select id, name from (select row_number()over(order by name) rn, id, name from test_iot) where rn=26;
ID NAME
---------- ------------------------------
29 TEST_QUEUE
现在用dump函数检测下是否正确,
SQL> select dump('TEST_QUEUE', 16) "col 0", dump(29, 16) "col 1" from dual;
col 0 col 1
-------------------------------------------- ------------------
Typ=96 Len=10: 54,45,53,54,5f,51,55,45,55,45 Typ=2 Len=2: c1,1e
仔细瞅瞅,完全一样,这就说明了IOT的secondary index中确实包含了primary key!
至于最后一行的 col 0: [ 4] 01 00 25 5c
不是很清楚,个人猜测应该是所谓的"physical guess", 指向IOT表的"physical rowid", [4] 表示长度, 但是很奇怪地发现上面dump中的每一个row的最后一行都一样!!这个待验证!!!