[20160908]唯一索引与非唯一索引.txt
--唯一索引与非唯一索引的区别在于rowid信息在索引的位置,唯一索引rowid在row header(数据部分).而非唯一索引在最后.
--但是具体的内部结构oracle如何识别呢？做一个简单探究，通过例子来说明:
1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
SCOTT@book> create table t as select rownum id1, rownum id2 ,'test' name from dual connect by level<=10;
Table created.
SCOTT@book> create unique index i_t_id1 on t(id1);
Index created.
SCOTT@book> create index i_t_id2 on t(id2);
Index created.
SCOTT@book> select segment_name,HEADER_FILE,HEADER_BLOCK from dba_segments where owner=user and segment_name in ('I_T_ID1','I_T_ID2');
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
-------------------- ----------- ------------
I_T_ID1 4 530
I_T_ID2 4 538
--// 索引很小,root节点就是叶子节点.HEADER_BLOCK的下一块就是root节点。
alter system checkpoint ;
SCOTT@book> alter system dump datafile 4 block 531 ;
System altered.
SCOTT@book> alter system dump datafile 4 block 538 ;
System altered.
2.检查:
SCOTT@book> select rowid from t where rownum=1;
ROWID
------------------
AAAVqiAAEAAAAILAAA
SCOTT@book> @ &r/rowid AAAVqiAAEAAAAILAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
88738 4 523 0 0x100020B 4,523 alter system dump datafile 4 block 523 ;
Block header dump: 0x01000213
Object id on Block? Y
seg/obj: 0x15aa3 csc: 0x03.16294fae itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1000210 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 0x0003.16294fae
Leaf block dump
===============
header address 140443144790628=0x7fbb77c1a264
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 10
kdxcofbo 56=0x38
kdxcofeo 7922=0x1ef2
kdxcoavs 7866
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 6
kdxlebksz 8032
row#0[8021] flag: ------, lock: 0, len=11, data:(6): 01 00 02 0b 00 00
col 0; len 2; (2): c1 02
row#1[8010] flag: ------, lock: 0, len=11, data:(6): 01 00 02 0b 00 01
col 0; len 2; (2): c1 03
row#2[7999] flag: ------, lock: 0, len=11, data:(6): 01 00 02 0b 00 02
col 0; len 2; (2): c1 04
row#3[7988] flag: ------, lock: 0, len=11, data:(6): 01 00 02 0b 00 03
col 0; len 2; (2): c1 05
row#4[7977] flag: ------, lock: 0, len=11, data:(6): 01 00 02 0b 00 04
col 0; len 2; (2): c1 06
row#5[7966] flag: ------, lock: 0, len=11, data:(6): 01 00 02 0b 00 05
col 0; len 2; (2): c1 07
row#6[7955] flag: ------, lock: 0, len=11, data:(6): 01 00 02 0b 00 06
col 0; len 2; (2): c1 08
row#7[7944] flag: ------, lock: 0, len=11, data:(6): 01 00 02 0b 00 07
col 0; len 2; (2): c1 09
row#8[7933] flag: ------, lock: 0, len=11, data:(6): 01 00 02 0b 00 08
col 0; len 2; (2): c1 0a
row#9[7922] flag: ------, lock: 0, len=11, data:(6): 01 00 02 0b 00 09
col 0; len 2; (2): c1 0b
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 531 maxblk 531
Block header dump: 0x0100021b
Object id on Block? Y
seg/obj: 0x15aa4 csc: 0x03.16294fbc itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1000218 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 0x0003.16294fbc
Leaf block dump
===============
header address 140443144790628=0x7fbb77c1a264
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 10
kdxcofbo 56=0x38
kdxcofeo 7912=0x1ee8
kdxcoavs 7856
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[8020] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 02
col 1; len 6; (6): 01 00 02 0b 00 00
row#1[8008] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 01 00 02 0b 00 01
row#2[7996] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 04
col 1; len 6; (6): 01 00 02 0b 00 02
row#3[7984] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 05
col 1; len 6; (6): 01 00 02 0b 00 03
row#4[7972] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 06
col 1; len 6; (6): 01 00 02 0b 00 04
row#5[7960] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 07
col 1; len 6; (6): 01 00 02 0b 00 05
row#6[7948] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 08
col 1; len 6; (6): 01 00 02 0b 00 06
row#7[7936] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 09
col 1; len 6; (6): 01 00 02 0b 00 07
row#8[7924] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 0a
col 1; len 6; (6): 01 00 02 0b 00 08
row#9[7912] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 0b
col 1; len 6; (6): 01 00 02 0b 00 09
----- end of leaf block dump -----
--//对比就很明显,结合前面查询看到的ROWID_DBA=0x100020B.唯一索引rowid在数据前，而非唯一索引在数据之后，两者结合起来一定唯一。
--//如果再仔细看，唯一索引rowid前面没有长度指示器，而非唯一索引有，这样这个索引总长度少1，这样唯一索引占用磁盘空间要少一些。
--//很容易产生疑问? oracle如何知道索引的格式呢？查询数据字典吗？
--//如果再仔细看,唯一索引存在如下内容
kdxledsz 6
--//kdxledsz=6,而非唯一索引kdxledsz 0。oracle是通过这个来识别索引数据块的信息。
3.验证自己的推测是否正确，可以建立分区表使用全局唯一索引，看看情况?要知道全局索引rowid可以包括data_object_id,长度可以10位.
--继续建立测试
CREATE TABLE tx
(
id NUMBER,
name VARCHAR2(20),
status VARCHAR2(1)
)
LOGGING
PARTITION BY LIST (status)
(
PARTITION P1 VALUES ('1'),
PARTITION P2 VALUES ('2'),
PARTITION POTHER VALUES (DEFAULT)
);
SCOTT@book> insert into tx select rownum ,'test',decode(mod(rownum,2),0,'1','2') from dual connect by level<=10;
10 rows created.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> create unique index i_tx_id on tx(id);
Index created.
SCOTT@book> select segment_name,HEADER_FILE,HEADER_BLOCK from dba_segments where owner=user and segment_name in ('I_T_ID1','I_T_ID2','I_TX_ID');
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
-------------------- ----------- ------------
I_T_ID1 4 530
I_T_ID2 4 538
I_TX_ID 4 546
SCOTT@book> alter system checkpoint ;
System altered.
SCOTT@book> alter system dump datafile 4 block 547 ;
System altered.
Block header dump: 0x01000223
Object id on Block? Y
seg/obj: 0x15aa9 csc: 0x03.162952de itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1000220 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 0x0003.162952de
Leaf block dump
===============
header address 140443144790628=0x7fbb77c1a264
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 10
kdxcofbo 56=0x38
kdxcofeo 7882=0x1eca
kdxcoavs 7826
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 10
~~~~~~~~~~~
kdxlebksz 8032
row#0[8017] flag: ------, lock: 0, len=15, data:(10): 00 01 5a a7 01 00 02 af 00 00
col 0; len 2; (2): c1 02
row#1[8002] flag: ------, lock: 0, len=15, data:(10): 00 01 5a a6 01 00 06 af 00 00
col 0; len 2; (2): c1 03
row#2[7987] flag: ------, lock: 0, len=15, data:(10): 00 01 5a a7 01 00 02 af 00 01
col 0; len 2; (2): c1 04
row#3[7972] flag: ------, lock: 0, len=15, data:(10): 00 01 5a a6 01 00 06 af 00 01
col 0; len 2; (2): c1 05
row#4[7957] flag: ------, lock: 0, len=15, data:(10): 00 01 5a a7 01 00 02 af 00 02
col 0; len 2; (2): c1 06
row#5[7942] flag: ------, lock: 0, len=15, data:(10): 00 01 5a a6 01 00 06 af 00 02
col 0; len 2; (2): c1 07
row#6[7927] flag: ------, lock: 0, len=15, data:(10): 00 01 5a a7 01 00 02 af 00 03
col 0; len 2; (2): c1 08
row#7[7912] flag: ------, lock: 0, len=15, data:(10): 00 01 5a a6 01 00 06 af 00 03
col 0; len 2; (2): c1 09
row#8[7897] flag: ------, lock: 0, len=15, data:(10): 00 01 5a a7 01 00 02 af 00 04
col 0; len 2; (2): c1 0a
row#9[7882] flag: ------, lock: 0, len=15, data:(10): 00 01 5a a6 01 00 06 af 00 04
col 0; len 2; (2): c1 0b
----- end of leaf block dump -----
--//注意看kdxledsz 10，oracle通过这个识别唯一与非唯一的结构的。
SCOTT@book> select rowid,tx.* from tx where rownum=1;
ROWID ID NAME S
------------------ ---------- -------------------- -
AAAVqmAAEAAAAavAAA 2 test 1
SCOTT@book> @ &r/rowid AAAVqmAAEAAAAavAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
88742 4 1711 0 0x10006AF 4,1711 alter system dump datafile 4 block 1711
SCOTT@book> @ &r/10to16 88742
10 to 16 HEX REVERSE16
-------------- ------------------
0000000015aa6 0xa65a0100
--iD=2,data:(10): 00 01 5a a6 01 00 06 af 00 00 ,前面4位00 01 5a a6就是data_object_id.
