Oracle索引碎片分析、空間重用和整理，對索引頻繁的update，delete操作會產生index Frag，影響索引效率，增加索引IO。

對索引頻繁的update，delete操作會產生index Frag，，影響索引效率，增加索引IO。

1、索引碎片分析
產生測試索引碎片：
SCOTT @devcedb>select count(*) from obj;

COUNT(*)
----------
124256

SCOTT @devcedb>create index ind_obj_id on obj(OBJECT_ID);

Index created.

SCOTT @devcedb>delete obj where rownum<50000;

49999 rows deleted.

SCOTT @devcedb>commit;

Commit complete.

索引碎片分析：
SCOTT @devcedb>analyze index ind_obj_id validate structure;

Index analyzed.

--注意一點，就是該命令有一個壞處，就是在運行過程中，會鎖定整個表，從而阻塞其他session對表進行插入、更新和刪除等操作。這是因為該命令的主要目的并不是用來填充index_stats視圖的，其主要作用在于校驗索引中的每個有效的索引條目都對應到表里的一行，同時表里的每一行數據在索引中都存在一個對應的索引條目。為了完成該目的，所以在運行過程中要鎖定整個表，同時對于很大的表來說，運行該命令需要耗費非常多的時間。

SCOTT @devcedb>select name,blocks,del_lf_rows_len,lf_rows_len,(del_lf_rows_len/lf_rows_len)*100,(DEL_LF_ROWS/LF_ROWS)*100 from index_stats;

NAME BLOCKS DEL_LF_ROWS_LEN LF_ROWS_LEN (DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 (DEL_LF_ROWS/LF_ROWS)*100
------------------------------ ---------- --------------- ----------- --------------------------------- -------------------------
IND_OBJ_ID 384 766085 1906952 40.1732713 40.2394062

索引碎片比率：(del_lf_rows_len/lf_rows_len)*100，如果百分比超過20%就說明索引碎片比率很高了。需要整理碎片。
索引和表數據是級聯關系的，當刪除表數據的時候，索引條目不會被自動刪除，而是在該條目上打上一個刪除(D)的標識位，具體后面會說明，索引的block數量是不會改變的，空葉塊不會被刪除。所以當INDEX FAST FULL SCAN和INDEX FULL SCAN的時候，這些空索引塊也會被加載到內存中，增加了IO。索引空葉塊多，極大影響了索引掃描的效率。否則索引碎片對效率的影響不是很大。如下：

SCOTT @devcedb>select object_id from obj where object_id < 9000;

41377 rows selected.

Elapsed: 00:00:01.13

Execution Plan
----------------------------------------------------------
Plan hash value: 2777403740

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 55341 | 702K| 79 (2)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| IND_OBJ_ID | 55341 | 702K| 79 (2)| 00:00:01 |
-----------------------------------------------------------------------------------

SYS AS SYSDBA@devcedb>select count(*) from x$bh where obj='102822';

COUNT(*)
---------- ---從x$bh查詢緩存blocks，要用DBA_OBJECTS.DATA_OBJECT_ID
268 ---該索引加載到buffer pool的數據塊數，該索引分配有384 blocks，266 LEAF_BLOCKS

那么索引空塊會不會被重用呢？下面測試說明：
SCOTT @devcedb>select count(*) from obj2;

COUNT(*)
----------
62144

SCOTT @devcedb>create unique index uni_ind_obj2_id on obj2(id);

Index created. --該索引分配有256 blocks，130 LEAF_BLOCKS

SCOTT @devcedb>delete obj2 where rownum<15537;

15536 rows deleted.

SCOTT @devcedb>commit;

Commit complete.

SCOTT @devcedb>analyze index uni_ind_obj2_id validate structure;

Index analyzed.

SCOTT @devcedb>select name,blocks,del_lf_rows_len,lf_rows_len,(del_lf_rows_len/lf_rows_len)*100,(DEL_LF_ROWS/LF_ROWS)*100 from index_stats;

NAME BLOCKS DEL_LF_ROWS_LEN LF_ROWS_LEN (DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 (DEL_LF_ROWS/LF_ROWS)*100
------------------------------ ---------- --------------- ----------- --------------------------------- -------------------------
UNI_IND_OBJ2_ID 256 240063 938727 25.5732497 25.5732626

SCOTT @devcedb>insert into obj2 select seq_obj2.nextval id,a.* from dba_objects a;

15537 rows created.

SCOTT @devcedb>commit;

Commit complete.

SCOTT @devcedb>analyze index uni_ind_obj2_id validate structure;

Index analyzed.

SCOTT @devcedb>select name,blocks,del_lf_rows_len,lf_rows_len,(del_lf_rows_len/lf_rows_len)*100,(DEL_LF_ROWS/LF_ROWS)*100 from index_stats;

NAME BLOCKS DEL_LF_ROWS_LEN LF_ROWS_LEN (DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 (DEL_LF_ROWS/LF_ROWS)*100
------------------------------ ---------- --------------- ----------- --------------------------------- -------------------------
UNI_IND_OBJ2_ID 256 7180 938727 .764865611 .764882473

SYS AS SYSDBA@devcedb>select INDEX_NAME,LEAF_BLOCKS,BLEVEL from dba_indexes where INDEX_NAME=upper('uni_ind_obj2_id');

INDEX_NAME LEAF_BLOCKS BLEVEL
------------------------------ ----------- ----------
UNI_IND_OBJ2_ID 130 1

這里我們看到，索引的碎片降低了，而且LEAF_BLOCKS的數量沒有增加，說明空葉塊被重用了。

當刪除表里的一條記錄時，其對應于索引里的索引條目并不會被物理的刪除，只是做了一個刪除標記（這可以通過dump 索引數據塊alter system dump datafile # block #;可以看到類似”row#0[443] flag: ---D-, lock: 2“。)當一個空葉塊被重用的時候，當第一條數據插入該索引葉塊之前，Oracle清空該空葉塊上所有打上D標識位的索引條目，然后重用該索引塊。如下：
SCOTT @devcedb>select name,blocks,del_lf_rows_len,lf_rows_len,(del_lf_rows_len/lf_rows_len)*100,(DEL_LF_ROWS/LF_ROWS)*100,USED_SPACE,BTREE_SPACE,PCT_USED from index_stats;

NAME BLOCKS DEL_LF_ROWS_LEN LF_ROWS_LEN (DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 (DEL_LF_ROWS/LF_ROWS)*100 USED_SPACE BTREE_SPACE PCT_USED
------------------------------ ---------- --------------- ----------- --------------------------------- ------------------------- ---------- ----------- ----------
IND_OBJ_ID 256 307878 835601 36.8450971 36.8625788 837792 1279392 66

SCOTT @devcedb>insert into obj select a.* from dba_objects a where rownum<5; --該索引存在大量空索引塊，我們插入4條記錄

4 rows created.

SCOTT @devcedb>commit;

Commit complete.

SCOTT @devcedb>analyze index ind_obj_id validate structure;

Index analyzed.

SCOTT @devcedb>select name,blocks,del_lf_rows_len,lf_rows_len,(del_lf_rows_len/lf_rows_len)*100,(DEL_LF_ROWS/LF_ROWS)*100,USED_SPACE,BTREE_SPACE,PCT_USED from index_stats;

NAME BLOCKS DEL_LF_ROWS_LEN LF_ROWS_LEN (DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 (DEL_LF_ROWS/LF_ROWS)*100 USED_SPACE BTREE_SPACE PCT_USED
------------------------------ ---------- --------------- ----------- --------------------------------- ------------------------- ---------- ----------- ----------
IND_OBJ_ID 256 306312 834091 36.7240505 36.7303962 836282 1279392 66

我們關注下USED_SPACE和BTREE_SPACE
USED_SPACE--Total space that is currently being used in the B-Tree
BTREE_SPACE --Total space currently allocated in the B-Tree
重新分析該索引后我們注意到USED_SPACE反而降低了，BTREE_SPACE不變（當然我們也可以看到LF_ROWS減少了），在這4條數據重用一個空索引塊后，釋放的空間大于使用的空間，該空葉塊被重用了。

半空葉塊是如何重用的呢？
我們構想一下，一個有兩個葉塊的index，第一個葉塊包含1到10(不包含6），第二個葉塊11到20，這時候我們刪除表中2,4,11的數據，分析下索引后，分三種情況：1）插入鍵之前刪除的鍵值，插入2，索引葉塊1標識為D的兩條索引記錄會被清空，重新插入鍵值為2的記錄，索引空間被重用，BTREE_SPACE不變，USED_SPACE降低,LF_ROWS減1。2）插入一個屬于原來被刪除鍵值范圍內的值，插入6，我們會發現和情況1相同。3）插入比之前鍵值更大的值，假定葉塊2空間滿了，會新增一個葉塊。

所以說經常被刪除或更新index鍵值，以后幾乎不再會被插入時，空間的重用率很低，碎片產生的就越快。

聲明：本網頁內容旨在傳播知識，若有侵權等問題請及時與本網聯系，我們將在第一時間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com