index leaf block索引葉子塊何時會刪除—part1
前言
深入理解索引，對於最佳化與索引相關的效能問題，同時進行SQL最佳化至關重要。
測試結論
1,葉子塊中儲存的索引鍵值即使全部刪除，資料庫也不會移除這個葉子塊結構,僅在重建索引方會刪除這個葉子塊
2,只要重建索引方可重新組織索引，這樣掃描索引時就不會掃描已經刪除的葉子塊，可以減少邏輯讀
3,綜上，大家可知，為何在定期重建索引的必要性了，這就是根源所在
4,分支塊會儲存所屬葉子塊的實體地址，
葉子塊會會以雙向指標儲存前後葉子塊的實體地址
測試明細
SQL> create table t_index_block(a int,b int);
Table created.
SQL> insert into t_index_block select level,level from dual connect by level<=1000;
1000 rows created.
SQL> commit;
Commit complete.
create index idx_t_index_block on t_index_block(a);
SQL> select object_id from user_objects where lower(object_name)='idx_t_index_block';
OBJECT_ID
----------
82186
SQL> conn /as sysdba
Connected.
SQL> oradebug setmypid
Statement processed.
SQL> alter session set events 'immediate trace name treedump level 82186';
Session altered.
SQL> oradebug tracefile_name
/oracle/diag/rdbms/guowang/guowang/trace/guowang_ora_21656.trc
可見索引有1個分支塊及2個葉子塊
----- begin tree dump
branch: 0x102973b 16947003 (0: nrow: 2, level: 1)
leaf: 0x102973f 16947007 (-1: nrow: 540 rrow: 540)
leaf: 0x102973c 16947004 (0: nrow: 460 rrow: 460)
----- end tree dump
轉儲分支塊
select dbms_utility.data_block_address_file(16947003) as branch_file_no,
dbms_utility.data_block_address_block(16947003) as branch_block_no
from dual;
BRANCH_FILE_NO BRANCH_BLOCK_NO
-------------- ---------------
4 169787
SQL> conn /as sysdba
Connected.
SQL> oradebug setmypid
Statement processed.
SQL> alter system dump datafile 4 block 169787;
System altered.
SQL> oradebug tracefile_name
/oracle/diag/rdbms/guowang/guowang/trace/guowang_ora_22603.trc
Block header dump: 0x0102973b
Object id on Block? Y
seg/obj: 0x1410b csc: 0x00.468c40 itc: 1 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1029738 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0002.01f.00000902 0x00c01799.0385.01 -BU- 1 fsc 0x0000.00468d13
Branch block dump
=================
header address 47527428880972=0x2b39d783fa4c
kdxcolev 1
KDXCOLEV Flags = - - -
kdxcolok 1
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 1
kdxconro 1
kdxcofbo 30=0x1e
kdxcofeo 8047=0x1f6f
kdxcoavs 8017
kdxbrlmc 16947007=0x102973f —可見分支塊會指向第1個葉子塊地址
kdxbrsno 0
kdxbrbksz 8056
kdxbr2urrc 0
row#0[8047] dba: 16947004=0x102973c —這裡好像指向第2個葉子塊地址
col 0; len 3; (3): c2 06 2a
col 1; TERM
----- end of branch block dump -----
轉儲第1個葉子塊
Block header dump: 0x0102973f
Object id on Block? Y
seg/obj: 0x1410b csc: 0x00.468d12 itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1029738 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0002.01f.00000902 0x00c0179b.0385.01 CB-- 0 scn 0x0000.00468d13
0x02 0x0005.018.00000868 0x00c00287.04ba.04 --U- 540 fsc 0x0000.00468d1a
Leaf block dump
===============
header address 47790214281828=0x2b7706befa64
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 1
kdxconro 540
kdxcofbo 1116=0x45c
kdxcofeo 1116=0x45c
kdxcoavs 0
kdxlespl 0
kdxlende 0
kdxlenxt 16947004=0x102973c —可見第一個葉子塊會指向下面的第二個葉子塊地址
kdxleprv 0=0x0 —因為是第一個葉子塊，所有它的前向指標，為0，我們可以轉儲第2個葉子塊，我想這個資料結構即有值，它的值就是第1個葉子塊的地址
(插入第2個葉子塊的轉儲內容
Leaf block dump
===============
header address 46926997166692=0x2aae0aff1a64
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 1
kdxconro 460
kdxcofbo 956=0x3bc
kdxcofeo 2057=0x809
kdxcoavs 1101
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 16947007=0x102973f —第2個葉子塊也會透過指標指向第1個葉子塊，所以葉子塊中是雙向指標，這樣把葉子塊就聯絡到一起了
kdxledsz 0
kdxlebksz 8032
)
kdxledsz 0
kdxlebksz 8032
row#0[4818] flag: ------, lock: 2, len=12 ——這些是葉子塊中具體儲存的鍵值條目，其實就是和表的記錄對應起來了，最終透過這些資訊即可以定位到表的記錄了，所以這些資訊包含什麼內容，肯定有索引列的值以及錶行所處的ROWID，用於定位表具體物理位置哈
col 0; len 2; (2): c1 02
col 1; len 6; (6): 01 02 84 bc 00 00
row#1[4830] flag: ------, lock: 2, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 01 02 84 bc 00 01
row#2[4842] flag: ------, lock: 2, len=12
col 0; len 2; (2): c1 04
col 1; len 6; (6): 01 02 84 bc 00 02
row#3[4854] flag: ------, lock: 2, len=12
col 0; len 2; (2): c1 05
col 1; len 6; (6): 01 02 84 bc 00 03
row#4[4866] flag: ------, lock: 2, len=12
col 0; len 2; (2): c1 06
col 1; len 6; (6): 01 02 84 bc 00 04
row#5[4878] flag: ------, lock: 2, len=12
col 0; len 2; (2): c1 07
col 1; len 6; (6): 01 02 84 bc 00 05
row#6[4890] flag: ------, lock: 2, len=12
col 0; len 2; (2): c1 08
col 1; len 6; (6): 01 02 84 bc 00 06
row#7[4902] flag: ------, lock: 2, len=12
col 0; len 2; (2): c1 09
col 1; len 6; (6): 01 02 84 bc 00 07
row#8[4914] flag: ------, lock: 2, len=12
col 0; len 2; (2): c1 0a
col 1; len 6; (6): 01 02 84 bc 00 08
row#9[4926] flag: ------, lock: 2, len=12
中間略
row#535[1441] flag: ------, lock: 2, len=13
col 0; len 3; (3): c2 06 25
col 1; len 6; (6): 01 02 84 bc 02 17
row#536[1454] flag: ------, lock: 2, len=13
col 0; len 3; (3): c2 06 26
col 1; len 6; (6): 01 02 84 bc 02 18
row#537[1467] flag: ------, lock: 2, len=13
col 0; len 3; (3): c2 06 27
col 1; len 6; (6): 01 02 84 bc 02 19
row#538[1480] flag: ------, lock: 2, len=13
col 0; len 3; (3): c2 06 28
col 1; len 6; (6): 01 02 84 bc 02 1a
row#539[1493] flag: ------, lock: 2, len=13
col 0; len 3; (3): c2 06 29
col 1; len 6; (6): 01 02 84 bc 02 1b
----- end of leaf block dump -----
我們嘗試刪除第1個葉子塊中一些表記錄，注意僅刪除第1個葉子塊部分表記錄，這樣我想第1個葉子塊仍會在索引結構中保留
SQL> conn scott/system
Connected.
SQL> delete from t_index_block where a<=10;
10 rows deleted.
SQL> commit;
Commit complete.
轉儲分支塊，可知是第1個葉子塊已刪除了10個索引條目
----- begin tree dump
branch: 0x102973b 16947003 (0: nrow: 2, level: 1)
leaf: 0x102973f 16947007 (-1: nrow: 540 rrow: 530)
leaf: 0x102973c 16947004 (0: nrow: 460 rrow: 460)
----- end tree dump
轉儲第1個葉子塊，由下可知，剛好對應上述分支塊的資訊，葉子塊中刪除了10個索引鍵條目
Block header dump: 0x0102973f
Object id on Block? Y
seg/obj: 0x1410b csc: 0x00.469000 itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1029738 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0002.01f.00000902 0x00c0179b.0385.01 CB-- 0 scn 0x0000.00468d13
0x02 0x000a.001.0000089f 0x00c092f1.04c6.0e --U- 10 fsc 0x008c.00469002
Leaf block dump
===============
header address 47268536183396=0x2afd904eea64
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 1
kdxconro 540
kdxcofbo 1116=0x45c
kdxcofeo 1116=0x45c
kdxcoavs 0
kdxlespl 0
kdxlende 10
kdxlenxt 16947004=0x102973c
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[4818] flag: ---D--, lock: 2, len=12 —刪除僅是更新個標誌，因為這個葉子塊仍在使用
col 0; len 2; (2): c1 02
col 1; len 6; (6): 01 02 84 bc 00 00
row#1[4830] flag: ---D--, lock: 2, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 01 02 84 bc 00 01
row#2[4842] flag: ---D--, lock: 2, len=12
col 0; len 2; (2): c1 04
col 1; len 6; (6): 01 02 84 bc 00 02
row#3[4854] flag: ---D--, lock: 2, len=12
col 0; len 2; (2): c1 05
col 1; len 6; (6): 01 02 84 bc 00 03
row#4[4866] flag: ---D--, lock: 2, len=12
col 0; len 2; (2): c1 06
col 1; len 6; (6): 01 02 84 bc 00 04
row#5[4878] flag: ---D--, lock: 2, len=12
col 0; len 2; (2): c1 07
col 1; len 6; (6): 01 02 84 bc 00 05
row#6[4890] flag: ---D--, lock: 2, len=12
col 0; len 2; (2): c1 08
col 1; len 6; (6): 01 02 84 bc 00 06
row#7[4902] flag: ---D--, lock: 2, len=12
col 0; len 2; (2): c1 09
col 1; len 6; (6): 01 02 84 bc 00 07
row#8[4914] flag: ---D--, lock: 2, len=12
col 0; len 2; (2): c1 0a
col 1; len 6; (6): 01 02 84 bc 00 08
row#9[4926] flag: ---D--, lock: 2, len=12
col 0; len 2; (2): c1 0b
col 1; len 6; (6): 01 02 84 bc 00 09
我們把整個第1個葉子塊全部刪除，看看第分支塊及第2個葉子塊的資訊有何變化
SQL> conn scott/system
Connected.
SQL> delete from t_index_block where a<=540;
530 rows deleted.
SQL> commit;
Commit complete.
轉儲分支塊
可見原有的第1個葉子塊已儲存在索引結構中，不過其鍵值條目已經沒有內容
----- begin tree dump
branch: 0x102973b 16947003 (0: nrow: 2, level: 1)
leaf: 0x102973f 16947007 (-1: nrow: 530 rrow: 0)
leaf: 0x102973c 16947004 (0: nrow: 460 rrow: 460)
----- end tree dump
轉儲分支塊，看內容有何變化,可見分支塊儲存的內容仍沒有改變
Branch block dump
=================
header address 47358367337036=0x2b127aa98a4c
kdxcolev 1
KDXCOLEV Flags = - - -
kdxcolok 1
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 1
kdxconro 1
kdxcofbo 30=0x1e
kdxcofeo 8047=0x1f6f
kdxcoavs 8017
kdxbrlmc 16947007=0x102973f
kdxbrsno 0
kdxbrbksz 8056
kdxbr2urrc 0
row#0[8047] dba: 16947004=0x102973c
col 0; len 3; (3): c2 06 2a
col 1; TERM
----- end of branch block dump -----
轉儲第1個葉子塊，看到底有何內容
雖說刪除了葉子塊中所有鍵值條目，此葉子塊仍然儲存
Leaf block dump
===============
header address 47758653512292=0x2b6fad93da64
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 1
kdxconro 530
kdxcofbo 1096=0x448
kdxcofeo 1116=0x45c
kdxcoavs 140
kdxlespl 0
kdxlende 530
kdxlenxt 16947004=0x102973c
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[4938] flag: ---D--, lock: 2, len=12
col 0; len 2; (2): c1 0c
col 1; len 6; (6): 01 02 84 bc 00 0a
row#1[4950] flag: ---D--, lock: 2, len=12
col 0; len 2; (2): c1 0d
col 1; len 6; (6): 01 02 84 bc 00 0b
row#2[4962] flag: ---D--, lock: 2, len=12
col 0; len 2; (2): c1 0e
col 1; len 6; (6): 01 02 84 bc 00 0c
row#3[4974] flag: ---D--, lock: 2, len=12
col 0; len 2; (2): c1 0f
col 1; len 6; (6): 01 02 84 bc 00 0d
row#4[4986] flag: ---D--, lock: 2, len=12
col 0; len 2; (2): c1 10
col 1; len 6; (6): 01 02 84 bc 00 0e
row#5[4998] flag: ---D--, lock: 2, len=12
col 0; len 2; (2): c1 11
中間略
col 1; len 6; (6): 01 02 84 bc 02 18
row#527[1467] flag: ---D--, lock: 2, len=13
col 0; len 3; (3): c2 06 27
col 1; len 6; (6): 01 02 84 bc 02 19
row#528[1480] flag: ---D--, lock: 2, len=13
col 0; len 3; (3): c2 06 28
col 1; len 6; (6): 01 02 84 bc 02 1a
row#529[1493] flag: ---D--, lock: 2, len=13
col 0; len 3; (3): c2 06 29
col 1; len 6; (6): 01 02 84 bc 02 1b
----- end of leaf block dump -----
哪麼何處會清除或回收這個索引鍵值條目已經刪除的葉子塊呢
我們先暫停上述的研究，從SQL方面看下，掃描索引鍵值條目已經刪除的葉子塊，消耗的邏輯讀情況，為何是2個邏輯讀，因為要掃描分支塊及第1個葉子塊
SQL> select a from t_index_block where a<=530;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 4009802970
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 3 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_T_INDEX_BLOCK | 1 | 5 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"<=530)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
329 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
重建索引
SQL> set autot off
SQL> alter index IDX_T_INDEX_BLOCK rebuild;
轉儲分支塊
可見重建索引後，索引結構發生了大變化
----- begin tree dump
leaf: 0x10284c3 16942275 (0: nrow: 460 rrow: 460)
----- end tree dump
轉儲分支塊（現在分支塊和葉子塊同屬一個塊）
Branch block dump
=================
header address 46928045972044=0x2aae49829a4c
kdxcolev 1
KDXCOLEV Flags = - - -
kdxcolok 1
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 1
kdxconro 1
kdxcofbo 30=0x1e
kdxcofeo 8047=0x1f6f
kdxcoavs 8017
kdxbrlmc 16947007=0x102973f
kdxbrsno 0
kdxbrbksz 8056
kdxbr2urrc 0
row#0[8047] dba: 16947004=0x102973c
col 0; len 3; (3): c2 06 2a
col 1; TERM
----- end of branch block dump -----
重建索引後，邏輯讀也減少1次，為何消耗1次邏輯讀，因為僅掃描一個分支塊即可
SQL> select a from t_index_block where a<=530;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 4009802970
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_T_INDEX_BLOCK | 1 | 5 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"<=530)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
329 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
個人簡介
8年oracle從業經驗，具備豐富的oracle技能，目前在國內北京某專業oracle服務公司從事高階技術顧問。
服務過的客戶：
中國電信
中國移動
中國聯通
中國電通
國家電網
四川達州商業銀行
湖南老百姓大藥房
山西省公安廳
中國郵政
北京302醫院
河北廊坊新奧集團公司
專案經驗:
中國電信3G專案AAA系統資料庫部署及最佳化
中國聯通CRM資料庫效能最佳化
中國移動10086電商平臺資料庫部署及最佳化
湖南老百姓大藥房ERR資料庫sql最佳化專案
四川達州商業銀行TCBS核心業務系統資料庫模型設計和RAC部署及最佳化
四川達州商業銀行TCBS核心業務系統後端批處理儲存過程功能模組編寫及最佳化
北京高鐵訊號監控系統RAC資料庫部署及最佳化
河南宇通客車資料庫效能最佳化
中國電信電商平臺核心採購模組表模型設計及最佳化
中國郵政儲蓄系統資料庫效能最佳化及sql最佳化
北京302醫院資料庫遷移實施
河北廊坊新奧data guard部署及最佳化
山西公安廳身份證審計資料庫系統故障評估
國家電網上海災備專案4 node rac+adg
貴州移動crm及客服資料庫效能最佳化專案
貴州移動crm及客服務資料庫sql稽核專案
深圳穆迪軟體有限公司資料庫效能最佳化專案
貴州移動crm及客服資料庫效能最佳化專案
貴州移動crm及客服務資料庫sql稽核專案
深圳穆迪軟體有限公司資料庫效能最佳化專案
聯絡方式：
手機：18201115468
qq : 305076427
qq微博: wisdomone1
新浪微博：wisdomone9
qq群：275813900
itpub部落格名稱：wisdomone1 http://blog.itpub.net/9240380/
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9240380/viewspace-1712150/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- index branch and leaf block structure(轉)IndexBloCStruct
- zt_如何確定index root block split索引塊分裂的時間點IndexBloC索引
- Oracle索引分裂（Index Block Split）Oracle索引IndexBloC
- Drop table時候會徹底刪除index, Truncate 時候會清除index 但是index資料還是保留在HIndex
- 如何在生產庫評估索引並刪除無效索引index索引Index
- zt_Jonathan Lewis大師:何時重構索引index索引Index
- elasticsearch按照日期定時批量刪除索引Elasticsearch索引
- 做實驗驗證MongoDB刪除文件後索引是否會自動刪除MongoDB索引
- Solr 刪除和新增 indexSolrIndex
- 理解index leaf node 90－10 splitIndex
- Oracle index索引塊分裂split資訊彙總OracleIndex索引
- mongodb建立索引和刪除索引和背景索引backgroundMongoDB索引
- MySQL建立索引、修改索引、刪除索引的命令語句MySql索引
- Mysql索引的建立與刪除MySql索引
- elasticsearch(三)----索引建立與刪除Elasticsearch索引
- MySQL 5.5 刪除索引的方法MySql索引
- 從v$database_block_corruption 刪除資料DatabaseBloC
- 如何加快建 index 索引 的時間Index索引
- rhel4 mysql5.1 索引index_約束constraint建立_刪除_查詢MySql索引IndexAI
- index索引Index索引
- [BUG反饋]刪除模型屬性時 不會刪除模型表的field_sort模型
- 【Mongo】MongoDB索引管理－索引的建立、檢視、刪除MongoDB索引
- mysql 建立和刪除聯合索引MySql索引
- 刪除字串中的子串字串
- 儲存系統實現-資料刪除之索引的刪除索引
- Solr小技巧之快速刪除index的方法SolrIndex
- ElasticSearch分片互動過程(建立索引、刪除索引、查詢索引)Elasticsearch索引
- SELECT COUNT(*) 索引會走 index fast full scan索引IndexAST
- js刪除陣列指定索引的元素JS陣列索引
- Oracle刪除主鍵保留索引的方法Oracle索引
- 刪除分割槽需要更新全域性索引索引
- 分割槽索引之本地(local index)索引和全域性索引(global index)索引Index
- Lucene中建立索引的效率和刪除索引的實現索引
- Elasticsearch之索引模板index template與索引別名index aliasElasticsearch索引Index
- IOS Block 塊用法iOSBloC
- RMAN刪除歸檔日誌時將ASM上空目錄同時刪除ASM
- oracle資料庫建立、刪除索引等操作Oracle資料庫索引
- 資料庫 - 索引、基本表建立與刪除資料庫索引