【基礎篇索引】索引基礎（一）
最近有同事問了下索引的相關問題，這塊也是計劃要總結的，內容挺多的，先簡單開個頭，泛泛而談，然後再深入和擴充套件。
先建立一個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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 【基礎篇索引】索引基礎（四）索引
- 【基礎篇索引】索引基礎（三）索引
- 【基礎篇索引】索引基礎（二）索引
- MySQL索引基礎--菜鳥篇MySql索引
- mysql索引基礎MySql索引
- MySQL——索引基礎MySql索引
- mysql基礎_索引MySql索引
- Oracle 基礎--索引Oracle索引
- 【基礎知識】索引--點陣圖索引索引
- Mysql基礎 --- 索引+事務MySql索引
- Sql Server系列：索引基礎SQLServer索引
- SQL Server基礎之索引SQLServer索引
- Css基礎學習—索引CSS索引
- 索引基礎知識總結索引
- mysql 索引的基礎操作彙總MySql索引
- lucene第一步，lucene基礎，索引建立索引
- CSS基礎篇（一）CSS
- Kafka訊息系統基礎知識索引Kafka索引
- iOS 基礎知識學習目錄索引iOS索引
- 【學習】SQL基礎-018-索引SQL索引
- 索引成本計算的基礎知識索引
- UITableView基礎[ 5 ] 右側索引的實現UIView索引
- Elasticsearch 7.x 之文件、索引和 REST API 【基礎入門篇】Elasticsearch索引RESTAPI
- 基礎篇
- 【預研】搜尋引擎基礎——inverted index(倒排索引)Index索引
- vue系列基礎篇（一）Vue
- mysql優化篇（基於索引）MySql優化索引
- iOS逆向之旅（基礎篇） — 彙編（一）— 彙編基礎iOS
- Oracle學習總結--基礎部分（儲存與索引）Oracle索引
- oracle spatial之基礎知識之四空間索引Oracle索引
- vuex - 基礎篇Vue
- Docker|基礎篇Docker
- Maven——基礎篇Maven
- Git——基礎篇Git
- Hbase基礎篇
- Java基礎篇Java
- redis基礎篇Redis
- Websocket 基礎篇Web