index leaf block索引葉子塊何時會刪除—part1

wisdomone1發表於2015-06-25
前言
   深入理解索引,對於最佳化與索引相關的效能問題,同時進行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稽核專案
          深圳穆迪軟體有限公司資料庫效能最佳化專案
聯絡方式:
手機:18201115468
qq   :   305076427
qq微博: wisdomone1
新浪微博:wisdomone9
qq群:275813900    
itpub部落格名稱:wisdomone1    http://blog.itpub.net/9240380/

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/9240380/viewspace-1712150/,如需轉載,請註明出處,否則將追究法律責任。

相關文章