關於索引空間的重用的幾個場景
[i=s] 本帖最後由 wei-xh 於 2013-7-9 21:20 編輯
不知道你是否對這些有過疑問？
我隨機的刪除了表裡的一些記錄，我不知道我的索引空間是不是會被重用？
我刪除了表裡大部分記錄，我不知道表上一個自增的索引的空間會不會被重用？
我列舉了三種場景下索引空間的重用，希望對你有一點點的幫助和提高。
【場景一】
測試的思路：
1）刪除一個索引塊裡的某幾個entry，但是不要刪除完。之所以不刪除完，是考慮到，如果索引塊內的entry被完全刪除，那麼這個索引塊會被加入到索引段頭的空閒bitmap裡，這個場景是我們測試的第二個場景。
2）新開一個事物，往表裡新插入一條記錄，觀察新插記錄會不會導致ORACLE清除索引塊裡的deleted entry。
我們先準備下實驗的環境：建立測試表，共9條資料，在id列上建立索引
drop table test;
create table test as select rownum id,object_name from dba_objects where rownum<10;
create index i on test(id);
我們用ANALYZE對索引做一下分析，然後看一下我們本次實驗比較care的幾個統計值。
ANALYZE INDEX i VALIDATE STRUCTURE;
select LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_ROWS DEL_LF_ROWS
---------- -----------
9 0
LF_ROWS：代表索引上總共的entry數（包含deleted的entry）。
DEL_LF_ROWS：代表索引上總共的deleted的entry，這些entry還沒有被cleanout。
採用ANALYZE分析索引後，我們可以知道這個索引一共9個entry，還沒有被刪除的entry：DEL_LF_ROWS的值為0。
我們繼續實驗，刪除4個entry：
DELETE test WHERE id in (2,4,6,8);
commit;
ANALYZE INDEX i VALIDATE STRUCTURE;
select LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_ROWS DEL_LF_ROWS
---------- -----------
9 4
查詢的結果跟我們的預期相符。索引塊內共9個entry，其中4個是被刪除的。
我們可以透過ORACLE提供的索引tree dump工具來觀察下這個時候索引entry的樣子：
tree dump:
col object_name for a30
select object_name,object_id from dba_objects where object_name in ('I');
OBJECT_NAME OBJECT_ID
------------------------------ ----------
I 119015
alter session set events 'immediate trace name treedump level 119023';
tree dump 裡的內容：
----- begin tree dump
leaf: 0x119994c 18454860 (0: nrow: 9 rrow: 5)
----- end tree dump
從tree dump可以看到索引只有一個塊，這是由於實驗的資料量非常少，這個塊既充當了根塊也充當了分支塊還充當了葉子塊。
根據leaf: 0x119994c 18454860 (0: nrow: 9 rrow: 5)，我們知道這個索引塊的地址18454860，共有9個entry（nrow: 9）,實際存在的entry有5個（rrow: 5），說明有4個是delete的。
我們再來看看此時索引塊內的entry長什麼樣子：
根據索引塊地址18454860 ，我們來透過工具包轉換成我們需要的格式，然後dump這個索引塊。
SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(18454860),DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(18454860)FROM dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(18454860) DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(18454860)
---------------------------------------------- -----------------------------------------------
4 1677644
alter system dump datafile 4 block 1677644;
dump的結果：
row#0[8020] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 02
col 1; len 6; (6): 01 19 99 44 00 00
row#1[8008] flag: ---D--, lock: 2, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 01 19 99 44 00 01
row#2[7996] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 04
col 1; len 6; (6): 01 19 99 44 00 02
row#3[7984] flag: ---D--, lock: 2, len=12
col 0; len 2; (2): c1 05
col 1; len 6; (6): 01 19 99 44 00 03
row#4[7972] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 06
col 1; len 6; (6): 01 19 99 44 00 04
row#5[7960] flag: ---D--, lock: 2, len=12
col 0; len 2; (2): c1 07
col 1; len 6; (6): 01 19 99 44 00 05
row#6[7948] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 08
col 1; len 6; (6): 01 19 99 44 00 06
row#7[7936] flag: ---D--, lock: 2, len=12
col 0; len 2; (2): c1 09
col 1; len 6; (6): 01 19 99 44 00 07
row#8[7924] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 0a
col 1; len 6; (6): 01 19 99 44 00 08
標紅部分---D--，代表這個entry已經被刪除，ORACLE只是做了刪除標記，並未物理的釋放這個entry佔有的空間。
下面的步驟就比較重要了，我們往表裡插入一條記錄：
insert into test values(10,'weixh');
1 row created.
commit;
alter system checkpoint;(寫髒資料）
我們再次dump之前的索引塊：
alter system dump datafile 4 block 1677644;
row#0[8020] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 02
col 1; len 6; (6): 01 19 99 44 00 00
row#1[7996] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 04
col 1; len 6; (6): 01 19 99 44 00 02
row#2[7972] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 06
col 1; len 6; (6): 01 19 99 44 00 04
row#3[7948] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 08
col 1; len 6; (6): 01 19 99 44 00 06
row#4[7924] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 0a
col 1; len 6; (6): 01 19 99 44 00 08
row#5[7912] flag: ------, lock: 2, len=12------------新插入的entry
col 0; len 2; (2): c1 0b
col 1; len 6; (6): 01 19 99 46 00 00
----- end of leaf block dump -----
我們從dump的顯示中可以知道，之前的索引entry已經被cleanout，也就是說空間已經被重用了，索引的最後是我們新插入的資料。
透過分析索引的統計資訊，也說明了這一點：一共6個索引entry，不存在被刪除的entry。
ANALYZE INDEX i VALIDATE STRUCTURE;
select LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_ROWS DEL_LF_ROWS
---------- -----------
6 0
場景一總結：
1）任何一個新事務，往葉子塊插入一條記錄，ORACLE都會移除/釋放這個索引塊內所有的已被刪除的entry。
2）在一個隨機插入的索引列上，被刪除空間的重用往往不會存在什麼問題，就像我們看到的，只要一有新插入，空間就會釋放。
【場景二】
很多人對於這個場景有誤解：
一張表的欄位id是自增的，如果我們按照id的某個範圍，把記錄刪除。那麼這些記錄佔用的索引空間將不會被重用。
測試思路：
1）新建一張表，表上有一個自增的索引欄位，一萬記錄數，然後清空表。
2）往表裡再插入一萬記錄，但是這一萬記錄的值要比上面步驟一里的大。
3）觀察索引的葉子塊數有沒有變化
我們來試驗驗證一把。
drop table test;
create table test as select rownum id,object_name from dba_objects where rownum<10000;
create index i on test(id);
ANALYZE INDEX i VALIDATE STRUCTURE;
Index analyzed.
select lf_blks,LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------
21 9999 0
經過上面的步驟，我們建立了一張9999條記錄的一張表，id的值從1到9999。id上有一個普通的索引。
我們然後把表清空。
delete from test;
commit;
ANALYZE INDEX i VALIDATE STRUCTURE;
Index analyzed.
select lf_blks,LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------
21 9999 9999
表清空後，我們分析了索引，查詢INDEX_STATS檢視，也印證了表裡有9999條記錄，而且全部是已經被刪除的，這些記錄一共佔取了21個索引塊。
然後我們往表裡插入10000條記錄，id的值從20001開始遞增，保證不會跟上面的id有重複。如果說這些葉子塊不會被重用的話，我們插入資料後，葉子塊的數目應該是要變多的。
INSERT INTO test SELECT rownum+20000, 'weixh' FROM dba_objects where rownum<= 10000;
commit;
ANALYZE INDEX i VALIDATE STRUCTURE;
select lf_blks,LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------
21 10000 0
你發現了什麼？
索引葉子塊的數目沒發生任何的變化！而且，DEL_LF_ROWS的值為0，說明這些被刪除的entry，已經被cleanout，被oracle重用了。
場景二總結：
索引的葉子塊被清空後，會把這個葉子塊做為空閒塊加入到索引段的空閒bitmap裡（如果你使用的是ASSM），這樣後面的事務就可以重新使用這些空閒的索引塊了。
【場景三】
這個場景知道的人比較少，可以在Richard Foote部落格裡找到類似的博文。
延遲塊清除會對索引葉子塊做cleanout，釋放索引的空間。
測試思路：
1）新建一張一萬記錄的表，表上存在一個普通的索引
2）清空表，不要提交
3）發生檢查點
4）提交步驟2的事物
5）分析索引（分析索引會導致物理讀，發生延遲塊清除），檢視索引統計值
drop table test;
create table test as select rownum id,object_name from dba_objects where rownum<10000;
create index i on test(id);
ANALYZE INDEX i VALIDATE STRUCTURE;
select lf_blks,LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------
21 9999 0
我們經過上面的步驟，建立了一個有9999條記錄的表。在id上存在一個普通索引。
delete from test;
另開一個session 執行：alter system flush buffer_cache;
然後對delete的會話進行commit;
上面的步驟，在清空表資料後，把髒資料全部重新整理磁碟，這樣就能保證，ORACLE不能做任何的塊清除。
經過上面兩個場景的鍛鍊，我想你一定能知道，現在索引裡有9999個entry，其中9999個是被刪除的entry。
ANALYZE INDEX i VALIDATE STRUCTURE;
我們透過ANALYZE來分析一下索引，注意哦，這個命令會真實的去讀索引塊，讀取索引塊的過程中，發生延遲塊清除。並且這裡ORACLE會在延遲塊清除的過程中，把索引的被刪除的entry做cleanout，空間也會被釋放了。
select lf_blks,LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------
21 0 0
可以看到DEL_LF_ROWS，LF_ROWS的值都為0。索引空間已經在ANALYZE後完全釋放了。（並不是ANALYZE的功勞，而是這個操作遞迴導致的延遲塊清楚產生的效果）
來讓我們具體dump下，索引葉子塊現在長什麼樣子。如果像INDEX_STATS裡顯示的，那麼此時索引的葉子塊應該是沒有entry的。
col object_name for a30
select object_name,object_id from dba_objects where object_name in ('I');
OBJECT_NAME OBJECT_ID
------------------------------ ----------
I 119023
alter session set events 'immediate trace name treedump level 119025';
Session altered.
branch: 0x119b1ec 18461164 (0: nrow: 21, level: 1)
leaf: 0x119b1ed 18461165 (-1: nrow: 0 rrow: 0)
leaf: 0x119b1ee 18461166 (0: nrow: 0 rrow: 0)
leaf: 0x119b1ef 18461167 (1: nrow: 0 rrow: 0)
leaf: 0x119b1f0 18461168 (2: nrow: 0 rrow: 0)
leaf: 0x119b1f1 18461169 (3: nrow: 0 rrow: 0)
leaf: 0x119b1f2 18461170 (4: nrow: 0 rrow: 0)
leaf: 0x119b1f3 18461171 (5: nrow: 0 rrow: 0)
leaf: 0x119b1f4 18461172 (6: nrow: 0 rrow: 0)
leaf: 0x119b1f5 18461173 (7: nrow: 0 rrow: 0)
leaf: 0x119b1f6 18461174 (8: nrow: 0 rrow: 0)
leaf: 0x119b1f7 18461175 (9: nrow: 0 rrow: 0)
leaf: 0x119b1f8 18461176 (10: nrow: 0 rrow: 0)
leaf: 0x119b1fa 18461178 (11: nrow: 0 rrow: 0)
leaf: 0x119b1fb 18461179 (12: nrow: 0 rrow: 0)
leaf: 0x119b1fc 18461180 (13: nrow: 0 rrow: 0)
leaf: 0x119b1fd 18461181 (14: nrow: 0 rrow: 0)
leaf: 0x119b1fe 18461182 (15: nrow: 0 rrow: 0)
leaf: 0x119b1ff 18461183 (16: nrow: 0 rrow: 0)
leaf: 0x119b200 18461184 (17: nrow: 0 rrow: 0)
leaf: 0x119b201 18461185 (18: nrow: 0 rrow: 0)
leaf: 0x119b202 18461186 (19: nrow: 0 rrow: 0)
----- end tree dump
隨便找個葉子塊dump一下。
SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(18461165),DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(18461165)FROM dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(18461165) DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(18461165)
---------------------------------------------- -----------------------------------------------
4 1683949
alter system dump datafile 4 block 1683949;
Leaf block dump
===============
header address 133640292=0x7f73064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 0
kdxcofbo 36=0x24
kdxcofeo 8032=0x1f60
kdxcoavs 7996
kdxlespl 0
kdxlende 0
kdxlenxt 18461166=0x119b1ee
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
----- end of leaf block dump -----
發現索引葉子塊出了塊頭的一些資訊，沒有任何的entry，真的是空空如也！被刪除的索引entry都已經被ORACLE做了cleanout了。
上面的步驟裡，如果你省去了flush buffer cache這個步驟，查詢的INDEX_STATS的結果就會是下面這樣了。由於commit發生的時候資料都在記憶體中，因此ORACLE會對塊做快速塊清除，快速的塊清除不會去做索引entry的cleanout。：
select lf_blks,LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------
21 9999 9999
場景三的總結：
1）一些大的事物由於髒塊較多，非常可能會導致事物提交的時候，一些髒塊已經被刷出磁碟了
2）當這些沒有清除的塊再次被訪問的時候，會發生延遲塊清除。
3）延遲塊清除會把這個塊上所有的被刪除的索引entry做cleanout。
不知道你是否對這些有過疑問？
我隨機的刪除了表裡的一些記錄，我不知道我的索引空間是不是會被重用？
我刪除了表裡大部分記錄，我不知道表上一個自增的索引的空間會不會被重用？
我列舉了三種場景下索引空間的重用，希望對你有一點點的幫助和提高。
【場景一】
測試的思路：
1）刪除一個索引塊裡的某幾個entry，但是不要刪除完。之所以不刪除完，是考慮到，如果索引塊內的entry被完全刪除，那麼這個索引塊會被加入到索引段頭的空閒bitmap裡，這個場景是我們測試的第二個場景。
2）新開一個事物，往表裡新插入一條記錄，觀察新插記錄會不會導致ORACLE清除索引塊裡的deleted entry。
我們先準備下實驗的環境：建立測試表，共9條資料，在id列上建立索引
drop table test;
create table test as select rownum id,object_name from dba_objects where rownum<10;
create index i on test(id);
我們用ANALYZE對索引做一下分析，然後看一下我們本次實驗比較care的幾個統計值。
ANALYZE INDEX i VALIDATE STRUCTURE;
select LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_ROWS DEL_LF_ROWS
---------- -----------
9 0
LF_ROWS：代表索引上總共的entry數（包含deleted的entry）。
DEL_LF_ROWS：代表索引上總共的deleted的entry，這些entry還沒有被cleanout。
採用ANALYZE分析索引後，我們可以知道這個索引一共9個entry，還沒有被刪除的entry：DEL_LF_ROWS的值為0。
我們繼續實驗，刪除4個entry：
DELETE test WHERE id in (2,4,6,8);
commit;
ANALYZE INDEX i VALIDATE STRUCTURE;
select LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_ROWS DEL_LF_ROWS
---------- -----------
9 4
查詢的結果跟我們的預期相符。索引塊內共9個entry，其中4個是被刪除的。
我們可以透過ORACLE提供的索引tree dump工具來觀察下這個時候索引entry的樣子：
tree dump:
col object_name for a30
select object_name,object_id from dba_objects where object_name in ('I');
OBJECT_NAME OBJECT_ID
------------------------------ ----------
I 119015
alter session set events 'immediate trace name treedump level 119023';
tree dump 裡的內容：
----- begin tree dump
leaf: 0x119994c 18454860 (0: nrow: 9 rrow: 5)
----- end tree dump
從tree dump可以看到索引只有一個塊，這是由於實驗的資料量非常少，這個塊既充當了根塊也充當了分支塊還充當了葉子塊。
根據leaf: 0x119994c 18454860 (0: nrow: 9 rrow: 5)，我們知道這個索引塊的地址18454860，共有9個entry（nrow: 9）,實際存在的entry有5個（rrow: 5），說明有4個是delete的。
我們再來看看此時索引塊內的entry長什麼樣子：
根據索引塊地址18454860 ，我們來透過工具包轉換成我們需要的格式，然後dump這個索引塊。
SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(18454860),DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(18454860)FROM dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(18454860) DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(18454860)
---------------------------------------------- -----------------------------------------------
4 1677644
alter system dump datafile 4 block 1677644;
dump的結果：
row#0[8020] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 02
col 1; len 6; (6): 01 19 99 44 00 00
row#1[8008] flag: ---D--, lock: 2, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 01 19 99 44 00 01
row#2[7996] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 04
col 1; len 6; (6): 01 19 99 44 00 02
row#3[7984] flag: ---D--, lock: 2, len=12
col 0; len 2; (2): c1 05
col 1; len 6; (6): 01 19 99 44 00 03
row#4[7972] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 06
col 1; len 6; (6): 01 19 99 44 00 04
row#5[7960] flag: ---D--, lock: 2, len=12
col 0; len 2; (2): c1 07
col 1; len 6; (6): 01 19 99 44 00 05
row#6[7948] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 08
col 1; len 6; (6): 01 19 99 44 00 06
row#7[7936] flag: ---D--, lock: 2, len=12
col 0; len 2; (2): c1 09
col 1; len 6; (6): 01 19 99 44 00 07
row#8[7924] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 0a
col 1; len 6; (6): 01 19 99 44 00 08
標紅部分---D--，代表這個entry已經被刪除，ORACLE只是做了刪除標記，並未物理的釋放這個entry佔有的空間。
下面的步驟就比較重要了，我們往表裡插入一條記錄：
insert into test values(10,'weixh');
1 row created.
commit;
alter system checkpoint;(寫髒資料）
我們再次dump之前的索引塊：
alter system dump datafile 4 block 1677644;
row#0[8020] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 02
col 1; len 6; (6): 01 19 99 44 00 00
row#1[7996] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 04
col 1; len 6; (6): 01 19 99 44 00 02
row#2[7972] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 06
col 1; len 6; (6): 01 19 99 44 00 04
row#3[7948] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 08
col 1; len 6; (6): 01 19 99 44 00 06
row#4[7924] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 0a
col 1; len 6; (6): 01 19 99 44 00 08
row#5[7912] flag: ------, lock: 2, len=12------------新插入的entry
col 0; len 2; (2): c1 0b
col 1; len 6; (6): 01 19 99 46 00 00
----- end of leaf block dump -----
我們從dump的顯示中可以知道，之前的索引entry已經被cleanout，也就是說空間已經被重用了，索引的最後是我們新插入的資料。
透過分析索引的統計資訊，也說明了這一點：一共6個索引entry，不存在被刪除的entry。
ANALYZE INDEX i VALIDATE STRUCTURE;
select LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_ROWS DEL_LF_ROWS
---------- -----------
6 0
場景一總結：
1）任何一個新事務，往葉子塊插入一條記錄，ORACLE都會移除/釋放這個索引塊內所有的已被刪除的entry。
2）在一個隨機插入的索引列上，被刪除空間的重用往往不會存在什麼問題，就像我們看到的，只要一有新插入，空間就會釋放。
【場景二】
很多人對於這個場景有誤解：
一張表的欄位id是自增的，如果我們按照id的某個範圍，把記錄刪除。那麼這些記錄佔用的索引空間將不會被重用。
測試思路：
1）新建一張表，表上有一個自增的索引欄位，一萬記錄數，然後清空表。
2）往表裡再插入一萬記錄，但是這一萬記錄的值要比上面步驟一里的大。
3）觀察索引的葉子塊數有沒有變化
我們來試驗驗證一把。
drop table test;
create table test as select rownum id,object_name from dba_objects where rownum<10000;
create index i on test(id);
ANALYZE INDEX i VALIDATE STRUCTURE;
Index analyzed.
select lf_blks,LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------
21 9999 0
經過上面的步驟，我們建立了一張9999條記錄的一張表，id的值從1到9999。id上有一個普通的索引。
我們然後把表清空。
delete from test;
commit;
ANALYZE INDEX i VALIDATE STRUCTURE;
Index analyzed.
select lf_blks,LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------
21 9999 9999
表清空後，我們分析了索引，查詢INDEX_STATS檢視，也印證了表裡有9999條記錄，而且全部是已經被刪除的，這些記錄一共佔取了21個索引塊。
然後我們往表裡插入10000條記錄，id的值從20001開始遞增，保證不會跟上面的id有重複。如果說這些葉子塊不會被重用的話，我們插入資料後，葉子塊的數目應該是要變多的。
INSERT INTO test SELECT rownum+20000, 'weixh' FROM dba_objects where rownum<= 10000;
commit;
ANALYZE INDEX i VALIDATE STRUCTURE;
select lf_blks,LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------
21 10000 0
你發現了什麼？
索引葉子塊的數目沒發生任何的變化！而且，DEL_LF_ROWS的值為0，說明這些被刪除的entry，已經被cleanout，被oracle重用了。
場景二總結：
索引的葉子塊被清空後，會把這個葉子塊做為空閒塊加入到索引段的空閒bitmap裡（如果你使用的是ASSM），這樣後面的事務就可以重新使用這些空閒的索引塊了。
【場景三】
這個場景知道的人比較少，可以在Richard Foote部落格裡找到類似的博文。
延遲塊清除會對索引葉子塊做cleanout，釋放索引的空間。
測試思路：
1）新建一張一萬記錄的表，表上存在一個普通的索引
2）清空表，不要提交
3）發生檢查點
4）提交步驟2的事物
5）分析索引（分析索引會導致物理讀，發生延遲塊清除），檢視索引統計值
drop table test;
create table test as select rownum id,object_name from dba_objects where rownum<10000;
create index i on test(id);
ANALYZE INDEX i VALIDATE STRUCTURE;
select lf_blks,LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------
21 9999 0
我們經過上面的步驟，建立了一個有9999條記錄的表。在id上存在一個普通索引。
delete from test;
另開一個session 執行：alter system flush buffer_cache;
然後對delete的會話進行commit;
上面的步驟，在清空表資料後，把髒資料全部重新整理磁碟，這樣就能保證，ORACLE不能做任何的塊清除。
經過上面兩個場景的鍛鍊，我想你一定能知道，現在索引裡有9999個entry，其中9999個是被刪除的entry。
ANALYZE INDEX i VALIDATE STRUCTURE;
我們透過ANALYZE來分析一下索引，注意哦，這個命令會真實的去讀索引塊，讀取索引塊的過程中，發生延遲塊清除。並且這裡ORACLE會在延遲塊清除的過程中，把索引的被刪除的entry做cleanout，空間也會被釋放了。
select lf_blks,LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------
21 0 0
可以看到DEL_LF_ROWS，LF_ROWS的值都為0。索引空間已經在ANALYZE後完全釋放了。（並不是ANALYZE的功勞，而是這個操作遞迴導致的延遲塊清楚產生的效果）
來讓我們具體dump下，索引葉子塊現在長什麼樣子。如果像INDEX_STATS裡顯示的，那麼此時索引的葉子塊應該是沒有entry的。
col object_name for a30
select object_name,object_id from dba_objects where object_name in ('I');
OBJECT_NAME OBJECT_ID
------------------------------ ----------
I 119023
alter session set events 'immediate trace name treedump level 119025';
Session altered.
branch: 0x119b1ec 18461164 (0: nrow: 21, level: 1)
leaf: 0x119b1ed 18461165 (-1: nrow: 0 rrow: 0)
leaf: 0x119b1ee 18461166 (0: nrow: 0 rrow: 0)
leaf: 0x119b1ef 18461167 (1: nrow: 0 rrow: 0)
leaf: 0x119b1f0 18461168 (2: nrow: 0 rrow: 0)
leaf: 0x119b1f1 18461169 (3: nrow: 0 rrow: 0)
leaf: 0x119b1f2 18461170 (4: nrow: 0 rrow: 0)
leaf: 0x119b1f3 18461171 (5: nrow: 0 rrow: 0)
leaf: 0x119b1f4 18461172 (6: nrow: 0 rrow: 0)
leaf: 0x119b1f5 18461173 (7: nrow: 0 rrow: 0)
leaf: 0x119b1f6 18461174 (8: nrow: 0 rrow: 0)
leaf: 0x119b1f7 18461175 (9: nrow: 0 rrow: 0)
leaf: 0x119b1f8 18461176 (10: nrow: 0 rrow: 0)
leaf: 0x119b1fa 18461178 (11: nrow: 0 rrow: 0)
leaf: 0x119b1fb 18461179 (12: nrow: 0 rrow: 0)
leaf: 0x119b1fc 18461180 (13: nrow: 0 rrow: 0)
leaf: 0x119b1fd 18461181 (14: nrow: 0 rrow: 0)
leaf: 0x119b1fe 18461182 (15: nrow: 0 rrow: 0)
leaf: 0x119b1ff 18461183 (16: nrow: 0 rrow: 0)
leaf: 0x119b200 18461184 (17: nrow: 0 rrow: 0)
leaf: 0x119b201 18461185 (18: nrow: 0 rrow: 0)
leaf: 0x119b202 18461186 (19: nrow: 0 rrow: 0)
----- end tree dump
隨便找個葉子塊dump一下。
SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(18461165),DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(18461165)FROM dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(18461165) DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(18461165)
---------------------------------------------- -----------------------------------------------
4 1683949
alter system dump datafile 4 block 1683949;
Leaf block dump
===============
header address 133640292=0x7f73064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: pcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 0
kdxcofbo 36=0x24
kdxcofeo 8032=0x1f60
kdxcoavs 7996
kdxlespl 0
kdxlende 0
kdxlenxt 18461166=0x119b1ee
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
----- end of leaf block dump -----
發現索引葉子塊出了塊頭的一些資訊，沒有任何的entry，真的是空空如也！被刪除的索引entry都已經被ORACLE做了cleanout了。
上面的步驟裡，如果你省去了flush buffer cache這個步驟，查詢的INDEX_STATS的結果就會是下面這樣了。由於commit發生的時候資料都在記憶體中，因此ORACLE會對塊做快速塊清除，快速的塊清除不會去做索引entry的cleanout。：
select lf_blks,LF_ROWS,DEL_LF_ROWS from INDEX_STATS;
LF_BLKS LF_ROWS DEL_LF_ROWS
---------- ---------- -----------
21 9999 9999
場景三的總結：
1）一些大的事物由於髒塊較多，非常可能會導致事物提交的時候，一些髒塊已經被刷出磁碟了
2）當這些沒有清除的塊再次被訪問的時候，會發生延遲塊清除。
3）延遲塊清除會把這個塊上所有的被刪除的索引entry做cleanout。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22034023/viewspace-765910/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle資料庫中索引空間的重用Oracle資料庫索引
- 索引表空間不足的幾個處理思路索引
- 沉浸式空間場景的使用有哪幾種方式？
- 關於聯合索引，範圍查詢，時間列索引的幾個問題索引
- 全域性索引失效帶來的幾個測試場景索引
- 關於oracle的表空間，分割槽表，以及索引的總結Oracle索引
- sudo命令使用的幾個場景
- 關於OpenAI GPT-3幾個也許很有用的場景案例 - datamahadevOpenAIGPTdev
- 關於ORA-01536 報錯的幾種場景：
- 關於oracle的表空間，分割槽表，以及索引的總結(轉)Oracle索引
- 關於oracle的表空間，分割槽表，以及索引的總結 -- 轉Oracle索引
- 上海辦公室出租，場景化空間
- oracle 估算一個索引所需要的空間Oracle索引
- 關於oracle的空間查詢Oracle
- 關於表空間和表的關係
- mysql關於表空間的總結MySql
- 關於shutdown的幾個模式模式
- go語言reflect包使用的幾個場景Go
- MySQL單表模擬鎖的幾個場景MySql
- 函式索引的兩個應用場景示例（下）函式索引
- 索引失效場景索引
- 本地索引和全域性索引的適用場景索引
- 關於oracle表空間的規劃方法Oracle
- MVC常遇見的幾個場景程式碼分享MVC
- 空間索引 - 各資料庫空間索引使用報告索引資料庫
- 深入淺出空間索引：為什麼需要空間索引索引
- 遊戲場景設計探究：空間潛意識遊戲
- MySQL索引之空間索引（SPATIAL）MySql索引
- Bash 中為 _ 變數賦空值的三個場景變數
- 2.5.4.1 關於SYSAUX表空間UX
- 程式間通訊的場景
- 關於singleton的幾個實現
- 關於INLIST ITERATOR的幾個測試
- 關於時間、物質結構、四維空間的猜想
- Oracle中表空間、表、索引的遷移Oracle索引
- 關於Ajax和websocket的區別以及使用場景！Web
- Java中關於OOM的場景及解決方法JavaOOM
- 一個空間可以放幾個網站嗎網站