最近有同事问了下索引的相关问题，这块也是计划要总结的，内容挺多的，先简单开个头，泛泛而谈，然后再深入和扩展。
先创建一个unique索引：
SQL> select count(*) from t_policy a;
COUNT(*)
----------
5025491
create tablespace testindex datafile '/tpdata/database/oradata/test1/index.dbf' size 1000M;
create unique index ind_policy_id on t_policy(policy_id) tablespace testindex;
创建完成后，可以在all_indexes中查询索引的相关信息：
SQL> select a.index_name,
2 a.index_type,
3 a.uniqueness,
4 a.blevel,
5 a.leaf_blocks,
6 a.distinct_keys,
7 a.avg_leaf_blocks_per_key,
8 a.avg_data_blocks_per_key,
9 a.buffer_pool
10 from all_indexes a
11 where a.index_name = upper('ind_policy_id');
INDEX_NAME INDEX_TYPE UNIQUENESS BLEVEL LEAF_BLOCKS DISTINCT_KEYS AVG_LEAF_BLOCKS_PER_KEY AVG_DATA_BLOCKS_PER_KEY BUFFER_POOL
------------------------------ --------------------------- ---------- ---------- ----------- ------------- ----------------------- ----------------------- -----------
IND_POLICY_ID NORMAL UNIQUE 2 11195 5025491 1 1 DEFAULT
SQL>
上面列举了索引的一些重要的信息，索引类型，唯一性，二元高度，叶块数，distinct键值，平均每个键值的叶块数量，平均每个键值的数据块数量，设置的buffer_pool缓冲池，这里暂时列出，后续将结合实验详细描述。
再来看看dba_segments中的段信息，主要看看段的header所在文件编号，header的block编号，段包含的block数量和extents数量，可以利用这里得到的信息转储header block，并能从header block中找到相应的信息。后面即有对header block和leaf block的转储。
SQL> select a.header_file,
2 a.header_block,
3 a.bytes,
4 a.blocks,
5 a.extents,
6 a.segment_name
7 from dba_segments a
8 where a.segment_name = upper('ind_policy_id');
HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS SEGMENT_NAME
----------- ------------ ---------- ---------- ---------- ---------------
10 11 100663296 12288 83 IND_POLICY_ID
SQL>
从上面的查询中已经看到这个索引段一共包含了83的extents，选择性的来看其中的一些extent_id对应的block_id，可以看出每个extent分配的block数量是不同的，但是还是有一些规律，后面的dump file中可以详细的看到。
SQL> select segment_name, file_id, extent_id, block_id
2 from dba_extents
3 where segment_name = 'IND_POLICY_ID'
4 and extent_id + 1 in (1, 16, 17, 18, 79, 80, 81, 83);
SEGMENT_NAME FILE_ID EXTENT_ID BLOCK_ID
------------------------ ---------- ----------
IND_POLICY_ID 10 0 9
IND_POLICY_ID 10 15 129
IND_POLICY_ID 10 16 137
IND_POLICY_ID 10 17 265
IND_POLICY_ID 10 78 8073
IND_POLICY_ID 10 79 8201
IND_POLICY_ID 10 80 9225
IND_POLICY_ID 10 82 11273
8 rows selected
SQL> alter system dump datafile 10 block 11;
System altered
[oracle@limmTest admin]$ vi /tpdata/database/admin/test1/udump/test1_ora_1763.trc
/tpdata/database/admin/test1/udump/test1_ora_1763.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /tpsys/oracle/product/10.2.0/db_1
System name: Linux
Node name: limmTest
Release: 2.6.18-53.el5
Version: #1 SMP Wed Oct 10 16:34:02 EDT 2007
Machine: i686
Instance name: test1
Redo thread mounted by this instance: 1
Oracle process number: 21
Unix process pid: 1763, image: oracle@limmTest
*** 2010-07-08 03:26:30.194
*** ACTION NAME:(SQL Window - 驴驴.sql) 2010-07-08 03:26:30.191
*** MODULE NAME:(PL/SQL Developer) 2010-07-08 03:26:30.191
*** SERVICE NAME:(test1) 2010-07-08 03:26:30.191
*** SESSION ID:(136.208) 2010-07-08 03:26:30.191
Start dump data blocks tsn: 10 file#: 10 minblk 11 maxblk 11
buffer tsn: 10 rdba: 0x0280000b (10/11)
scn: 0x05d9.8cd2c199 seq: 0x02 flg: 0x04 tail: 0xc1992302
frmt: 0x02 chkval: 0xc599 type: 0x23=PAGETABLE SEGMENT HEADER
Hex dump of block: st=0, typ_found=1
Dump of memory from 0xB77EAC00 to 0xB77ECC00
B77EAC00 0000A223 0280000B 8CD2C199 040205D9 [#...............]
…….
B77ECBF0 00000000 00000000 00000000 C1992302 [.............#..]
Extent Control Header
-----------------------------------------------------------------
Extent Header:: spare1: 0 spare2: 0 #extents: 83 #blocks: 12288
last map 0x00000000 #maps: 0 offset: 2716
Highwater:: 0x02802c6d ext#: 82 blk#: 100 ext size: 1024
#blocks in seg. hdr's freelists: 0
#blocks below: 11364
mapblk 0x00000000 offset: 82
Unlocked
--------------------------------------------------------
Low HighWater Mark :
Highwater:: 0x02802c6d ext#: 82 blk#: 100 ext size: 1024
#blocks in seg. hdr's freelists: 0
#blocks below: 11364
mapblk 0x00000000 offset: 82
Level 1 BMB for High HWM block: 0x02802c09
Level 1 BMB for Low HWM block: 0x02802c09
--------------------------------------------------------
Segment Type: 2 nl2: 1 blksz: 8192 fbsz: 0
L2 Array start offset: 0x00001434
First Level 3 BMB: 0x00000000
L2 Hint for inserts: 0x0280000a
Last Level 1 BMB: 0x02802c0c
Last Level II BMB: 0x0280000a
Last Level III BMB: 0x00000000
Map Header:: next 0x00000000 #extents: 83 obj#: 64411 flag: 0x10000000
Inc # 0
Extent Map
-----------------------------------------------------------------
0x02800009 length: 8
…..
0x02802c09 length: 1024
Auxillary Map
--------------------------------------------------------
Extent 0 : L1 dba: 0x02800009 Data dba: 0x0280000c
…..
Extent 82 : L1 dba: 0x02802c09 Data dba: 0x02802c0d
--------------------------------------------------------
Second Level Bitmap block DBAs
--------------------------------------------------------
DBA 1: 0x0280000a
End dump data blocks tsn: 10 file#: 10 minblk 11 maxblk 11
从extent map中可以看出来总共有83个segments，所包含的blocks分别是
SQL> select 16*8+1024*4+63*128 from dual;
16*8+1024*4+63*128
------------------
12288
这与dba_segments中的相关字段信息相对应：
SQL> select a.blocks,a.extents from dba_segments a where a.segment_name = 'IND_POLICY_ID';
BLOCKS EXTENTS
---------- ----------
12288 83
下面是对一个leaf block的转储，此外还有branch block。
/tpdata/database/admin/test1/udump/test1_ora_4568.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME = /tpsys/oracle/product/10.2.0/db_1
System name: Linux
Node name: limmTest
Release: 2.6.18-53.el5
Version: #1 SMP Wed Oct 10 16:34:02 EDT 2007
Machine: i686
Instance name: test1
Redo thread mounted by this instance: 1
Oracle process number: 25
Unix process pid: 4568, image: oracle@limmTest
*** ACTION NAME:(Command Window - New) 2010-07-08 03:36:40.315
*** MODULE NAME:(PL/SQL Developer) 2010-07-08 03:36:40.315
*** SERVICE NAME:(test1) 2010-07-08 03:36:40.315
*** SESSION ID:(138.5152) 2010-07-08 03:36:40.315
Start dump data blocks tsn: 10 file#: 10 minblk 14 maxblk 14
buffer tsn: 10 rdba: 0x0280000e (10/14)
scn: 0x05d9.8cd2c049 seq: 0x02 flg: 0x04 tail: 0xc0490602
frmt: 0x02 chkval: 0x571b type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0DAF1C00 to 0x0DAF3C00
DAF1C00 0000A206 0280000E 8CD2C049 040205D9 [........I.......]
…
...
…
DAF3BF0 00000000 00000000 00000000 C0490602 [..............I.]
Block header dump: 0x0280000e
Object id on Block? Y
seg/obj: 0xfb9b csc: 0x5d9.8cd2c047 itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x2800009 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 0x05d9.8cd2c047
Leaf block dump
===============
header address 229579876=0xdaf1c64
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 449
kdxcofbo 934=0x3a6
kdxcofeo 1750=0x6d6
kdxcoavs 816
kdxlespl 0
kdxlende 0
kdxlenxt 41943055=0x280000f
kdxleprv 41943053=0x280000d
kdxledsz 6
kdxlebksz 8032
row#0[8018] flag: ------, lock: 0, len=14, data:(6): 01 03 21 71 00 01
col 0; len 5; (5): c4 18 2a 47 1b
row#1[8004] flag: ------, lock: 0, len=14, data:(6): 01 03 21 71 00 04
col 0; len 5; (5): c4 18 2a 47 1c
…
…
…
…
row#446[1778] flag: ------, lock: 0, len=14, data:(6): 01 40 00 4f 00 0b
col 0; len 5; (5): c4 18 2d 05 09
row#447[1764] flag: ------, lock: 0, len=14, data:(6): 01 40 00 50 00 00
col 0; len 5; (5): c4 18 2d 05 0a
row#448[1750] flag: ------, lock: 0, len=14, data:(6): 01 40 00 50 00 01
col 0; len 5; (5): c4 18 2d 05 0b
----- end of leaf block dump -----
End dump data blocks tsn: 10 file#: 10 minblk 14 maxblk 14
上述黑体部分的部分释义如下：
kdxcolev: index level (0 represents leaf blocks)
索引级别，0代表叶块
kdxcolok: denotes whether structural block transaction is occurring
标注，structural块事务是否发生
kdxcoopc: internal operation code
内部操作代码
kdxconco: index column count
索引列的数量
kdxcosdc: count of index structural changes involving block
块中索引结构改变的数量
kdxconro: number of index entries (does not include kdxbrlmc pointer)
索引实体的数量，不包括kdxbrlmc指针
kdxcofbo: offset to beginning of free space within block
块中空闲空间的开始位置
kdxcofeo: offset to the end of free space (ie. first portion of block containing index data)
块中空闲空间的结束位置
kdxcoavs: available space in block (effectively area between the two fields above)
块中可用空间数量（kdxcofbo至kdxcofeo）
kdxlespl：块拆分时被清除的未提交数据的字节数
kdxlende：被删除的条目数
kdxlenxt：下一个叶块的RBA
kdxleprv：上一个叶块的RBA
kdxlebksz：可使用的块空间（默认小于分支的可用空间）
row#0[8018]:行号[块中起始位置]
flag:标志
lock:锁信息
data:(6): 01 03 21 71 00 01 rowid
转换为二进制数据：
00000001 00000011 00100001 00111000 0000000 00000001
4* file = (hex) 01 0
File#=16/4=4
block = (hex) 3 21 71
row number = (hex) 00 01
SQL> select power(16,0)+power(16,1)*7+power(16,2)++power(16,3)*2+power(16,4)*3 block# from dual;
BLOCK#
----------
205169
SQL> select name from v$datafile a where a.file#= 4;
NAME
--------------------------------------------------------------------------------
/tpdata/database/oradata/test1/users01.dbf
这是文件号
col 0; len 5; (5): c4 18 2a 47 1b key值
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16179598/viewspace-667398/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/16179598/viewspace-667398/
扫一扫
1403
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
