【基礎篇索引】索引基礎（一）

最近有同事問了下索引的相關問題，這塊也是計劃要總結的，內容挺多的，先簡單開個頭，泛泛而談，然後再深入和擴充套件。

先建立一個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/，如需轉載，請註明出處，否則將追究法律責任。

【基礎篇索引】索引基礎（一）

相關文章