前言
深入理解索引，对于优化与索引相关的性能问题，同时进行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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/9240380/viewspace-1712150/
204
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
