《oracle的索引初步学习》40多页.docx
《《oracle的索引初步学习》40多页.docx》由会员分享，可在线阅读，更多相关《《oracle的索引初步学习》40多页.docx（40页珍藏版）》请在冰豆网上搜索。
《oracle的索引初步学习》40多页
Oracle索引的初步学习
海量oracle资料下载：
http:
//www.D
1.B树索引的相关概念
索引与表一样，也属于段（segment）的一种。
里面存放了用户的数据，跟表一样需要占用磁盘空间。
只
不过，在索引里的数据存放形式与表里的数据存放形式非常的不一样。
在理解索引时，可以想象一本书，其中书的内容就相当于表里的数据，而书前面的目录就相当于该表的索引。
同时，通常情况下，索引所占用的磁盘空间要比表要小的多，其主要作用是为了加快对数据的搜索速度，也可以用来保证数据的唯一性。
但是，索引作为一种可选的数据结构，你可以选择为某个表里的创建索引，也可以不创建。
这是因为一旦创建了索引，就意味着oracle对表进行DML（包括INSERT、UPDATE、DELETE）时，必须处理额外的工作量（也就是对索引结构的维护）以及存储方面的开销。
所以创建索引时，需要考虑创建索引所带来的查询性能方面的提高，与引起的额外的开销相比，是否值得。
从物理上说，索引通常可以分为：
分区和非分区索引、常规B树索引、位图（bitmap）索引、翻转（reverse）索引等。
其中，B树索引属于最常见的索引，由于我们的这篇文章主要就是对B树索引所做的探讨，因此下面只要说到索引，都是指B树索引。
B树索引是一个典型的树结构，其包含的组件主要是：
叶子节点（Leafnode）：
包含条目直接指向表里的数据行。
分支节点（Branchnode）：
包含的条目指向索引里其他的分支节点或者是叶子节点。
根节点（Rootnode）：
一个B树索引只有一个根节点，它实际就是位于树的最顶端的分支节点。
可以用下图一来描述B树索引的结构。
其中，B表示分支节点，而L表示叶子节点。
对于分支节点块（包括根节点块）来说，其所包含的索引条目都是按照顺序排列的（缺省是升序排列，也可以在创建索引时指定为降序排列）。
每个索引条目（也可以叫做每条记录）都具有两个字段。
第一个字段表示当前该分支节点块下面所链接的索引块中所包含的最小键值；第二个字段为四个字节，表示所链接的索引块的地址，该地址指向下面一个索引块。
在一个分支节点块中所能容纳的记录行数由数据块大小以及索引键值的长度决定。
比如从上图一可以看到，对于根节点块来说，包含三条记录，分别为（0B1）、（500B2）、（1000B3），它们指向三个分支节点块。
其中的0、500和1000分别表示这三个分支节点块所链接的键值的最小值。
而B1、B2和B3则表示所指向的三个分支节点块的地址。
对于叶子节点块来说，其所包含的索引条目与分支节点一样，都是按照顺序排列的（缺省是升序排列，也可以在创建索引时指定为降序排列）。
每个索引条目（也可以叫做每条记录）也具有两个字段。
第一个字段表示索引的键值，对于单列索引来说是一个值；而对于多列索引来说则是多个值组合在一起的。
第二个字段表示键值所对应的记录行的ROWID，该ROWID是记录行在表里的物理地址。
如果索引是创建在非分区表上或者索引是分区表上的本地索引的话，则该ROWID占用6个字节；如果索引是创建在分区表上的全局索引的话，则该ROWID占用10个字节。
知道这些信息以后，我们可以举个例子来说明如何估算每个索引能够包含多少条目，以及对于表来说，所产生的索引大约多大。
对于每个索引块来说，缺省的PCTFREE为10％，也就是说最多只能使用其中的90％。
同时9i以后，这90％中也不可能用尽，只能使用其中的87％左右。
也就是说，8KB的数据块中能够实际用来存放索引数据的空间大约为6488（8192×90％×88％）个字节。
假设我们有一个非分区表，表名为warecountd，其数据行数为130万行。
该表中有一个列，列名为goodid，其类型为char（8），那么也就是说该goodid的长度为固定值：
8。
同时在该列上创建了一个B树索引。
在叶子节点中，每个索引条目都会在数据块中占一行空间。
每一行用2到3个字节作为行头，行头用来存放标记以及锁定类型等信息。
同时，在第一个表示索引的键值的字段中，每一个索引列都有1个字节表示数据长度，后面则是该列具体的值。
那么对于本例来说，在叶子节点中的一行所包含的数据大致如下图二所示：
从上图可以看到，在本例的叶子节点中，一个索引条目占18个字节。
同时我们知道8KB的数据块中真正可以用来存放索引条目的空间为6488字节，那么在本例中，一个数据块中大约可以放360（6488/18）个索引条目。
而对于我们表中的130万条记录来说，则需要大约3611（1300000/360）个叶子节点块。
而对于分支节点里的一个条目（一行）来说，由于它只需保存所链接的其他索引块的地址即可，而不需要保存具体的数据行在哪里，因此它所占用的空间要比叶子节点要少。
分支节点的一行中所存放的所链接的最小键值所需空间与上面所描述的叶子节点相同；而存放的索引块的地址只需要4个字节，比叶子节点中所存放的ROWID少了2个字节，少的这2个字节也就是ROWID中用来描述在数据块中的行号所需的空间。
因此，本例中在分支节点中的一行所包含的数据大致如下图三所示：
从上图可以看到，在本例的分支节点中，一个索引条目占16个字节。
根据上面叶子节点相同的方式，我们可以知道一个分支索引块可以存放大约405（6488/16）个索引条目。
而对于我们所需要的3611个叶子节点来说，则总共需要大约9个分支索引块。
这样，我们就知道了我们的这个索引有2层，第一层为1个根节点，第二层为9个分支节点，而叶子节点数为3611个，所指向的表的行数为1300000行。
但是要注意，在oracle的索引中，层级号是倒过来的，也就是说假设某个索引有N层，则根节点的层级号为N，而根节点下一层的分支节点的层级号为N-1，依此类推。
对本例来说，9个分支节点所在的层级号为1，而根节点所在的层级号为2。
2. B树索引的内部结构
我们可以使用如下方式将B树索引转储成树状结构的形式而呈现出来：
altersessionsetevents'immediatetracenametreedumplevelINDEX_OBJECT_ID';
比如，对于上面的例子来说，我们把创建在goodid上的名为idx_warecountd_goodid的索引转储出来。
SQL>selectobject_idfromuser_objectswhereobject_name='IDX_WARECOUNTD_GOODID';
OBJECT_ID
----------
7378
SQL>altersessionsetevents'immediatetracenametreedumplevel7378';
打开转储出来的文件以后，我们可以看到类似下面的内容：
-----begintreedump
branch:
0x180eb0a25225994（0:
nrow:
9,level:
2）
branch:
0x180eca125226401（-1:
nrow:
405,level:
1）
leaf:
0x180eb0b25225995（-1:
nrow:
359rrow:
359）
leaf:
0x180eb0c25225996（0:
nrow:
359rrow:
359）
leaf:
0x180eb0d25225997（1:
nrow:
359rrow:
359）
leaf:
0x180eb0e25225998（2:
nrow:
359rrow:
359）
…………………
branch:
0x180ee3825226808（0:
nrow:
406,level:
1）
leaf:
0x180eca025226400（-1:
nrow:
359rrow:
359）
leaf:
0x180eca225226402（0:
nrow:
359rrow:
359）
leaf:
0x180eca325226403（1:
nrow:
359rrow:
359）
leaf:
0x180eca425226404（2:
nrow:
359rrow:
359）
…………………
其中，每一行的第一列表示节点类型：
branch表示分支节点（包括根节点），而leaf则表示叶子节点；第二列表示十六进制表示的节点的地址；第三列表示十进制表示的节点的地址；第四列表示相对于前一个节点的位置，根节点从0开始计算，其他分支节点和叶子节点从-1开始计算；第五列的nrow表示当前节点中所含有的索引条目的数量。
比如我们可以看到根节点中含有的nrow为9，表示根节点中含有9个索引条目，分别指向9个分支节点；第六列中的level表示分支节点的层级，对于叶子节点来说level都是0。
第六列中的rrow表示有效的索引条目（因为索引条目如果被删除，不会立即被清除出索引块中。
所以nrow减rrow的数量就表示已经被删除的索引条目数量）的数量，比如对于第一个leaf来说，其rrow为359，也就是说该叶子节点中存放了359个可用索引条目，分别指向表warecountd的359条记录。
上面这种方式以树状形式转储整个索引。
同时，我们可以转储一个索引节点来看看其中存放了些什么。
转储的方式为：
altersystemdumpdatafilefile#blockblock#;
我们从上面转储结果中的第二行知道，索引的根节点的地址为25225994，因此我们先将其转换为文件号以及数据块号。
SQL>selectdbms_utility.data_block_address_file（25225994）,
2 dbms_utility.data_block_address_block（25225994）fromdual;
DBMS_UTILITY.DATA_BLOCK_ADDRESDBMS_UTILITY.DATA_BLOCK_ADDRES
------------------------------------------------------------
6 60170
于是，我们转储根节点的内容。
SQL>altersystemdumpdatafile6block60170;
打开转储出来的跟踪文件，我们可以看到如下的索引头部的内容：
headeraddress85594180=0x51a1044
kdxcolev2
KDXCOLEVFlags=---
kdxcolok0
kdxcoopc0x80:
pcode=0:
iotflags=---isconverted=Y
kdxconco2
kdxcosdc0
kdxconro8
kdxcofbo44=0x2c
kdxcofeo7918=0x1eee
kdxcoavs7874
kdxbrlmc25226401=0x180eca1
kdxbrsno0
kdxbrbksz8060
其中的kdxcolev表示索引层级号，这里由于我们转储的是根节点，所以其层级号为2。
对叶子节点来说该值为0；kdxcolok表示该索引上是否正在发生修改块结构的事务；kdxcoopc表示内部操作代码；kdxconco表示索引条目中列的数量；kdxcosdc表示索引结构发生变化的数量，当你修改表里的某个索引键值时，该值增加；kdxconro表示当前索引节点中索引条目的数量，但是注意，不包括kdxbrlmc指针；kdxcofbo表示当前索引节点中可用空间的起始点相对当前块的位移量；kdxcofeo表示当前索引节点中可用空间的最尾端的相对当前块的位移量；kdxcoavs表示当前索引块中的可用空间总量，也就是用kdxcofeo减去kdxcofbo得到的。
kdxbrlmc表示分支节点的地址，该分支节点存放了索引键值小于row#0（在转储文档后半部分显示）所含有的最小值的所有节点信息；kdxbrsno表示最后一个被修改的索引条目号，这里看到是0，表示该索引是新建的索引；kdxbrbksz表示可用数据块的空间大小。
实际从这里已经可以看到，即便是PCTFREE设置为0，也不能用足8192字节。
再往下可以看到如下的内容。
这部分内容就是在根节点中所记录的索引条目，总共是8个条目。
再加上
row#0[8043]dba:
25226808=0x180ee38
col0;len8;（8）:
3130303030333932
col1;len3;（3）:
01401a
……
row#7[7918]dba:
25229599=0x180f91f
col0;len8;（8）:
3130303131323033
col1;len4;（4）:
01408fa5
kdxbrlmc所指向的第一个分支节点，我们知道该根节点中总共存放了9个分支节点的索引条目，而这正是我们在前面所指出的为了管理3611个叶子节点，我们需要9个分支节点。
每个索引条目都指向一个分支节点。
其中col1表示所链接的分支节点的地址，该值经过一定的转换以后实际就是row#所在行的dba的值。
如果根节点下没有其他的分支节点，则col1为TERM；col0表示该分支节点所链接的最小键值。
其转换方式非常复杂，比如对于row#0来说，col0为3130303030303033，则将其中每对值都使用函数to_number（NN,’XX’）的方式从十六进制转换为十进制，于是我们得到转换后的值：
49,48,48,48,48,48,48,51，因为我们已经知道索引键值是char类型的，所以对每个值都运用chr函数就可以得到被索引键值为：
10000003。
实际上，对10000003运用dump函数得到的结果就是：
49,48,48,48,48,48,48,51。
所以我们也就知道，10000003就是dba为25226808的索引块所链接的最小键值。
SQL>selectdump（'10000003'）fromdual;
DUMP（'10000003'）
-------------------------------------
Typ=96Len=8:
49,48,48,48,48,48,48,50
接下来，我们从根节点中随便找一个分支节点，假设就是row#0所描述的25226808。
对其运用前面所介绍过的dbms_utility里的存储过程获得其文件号和数据块号，并对该数据块进行转储，其内容如下所示。
可以
row#0[8043]dba:
25226402=0x180eca2
col0;len8;（8）:
3130303030333933
col1;len3;（3）:
01402e
………
row#404[853]dba:
25226806=0x180ee36
col0;len8;（8）:
3130303031363430
col1;len3;（3）:
014009
-----endofbranchblockdump-----
发现内容与根节点完全类似，只不过该索引块中所包含的索引条目（指向叶子节点）的数量更多了，为405个。
这也与我们前面所说的一个分支索引块可以存放大约405（6488/16）个索引条目完全一致。
然后，我们从中随便挑一个叶子节点，对其进行转储。
假设就选row#0行所指向的叶子节点，根据dba的值：
25226402可以知道，文件号为6，数据块号为60578。
将其转储以后，其内容如下所示，我只显示与分支节点不同的部分。
………
kdxlespl0
kdxlende0
kdxlenxt25226403=0x180eca3
kdxleprv25226400=0x180eca0
kdxledsz0
kdxlebksz8036
其中的kdxlespl表示当叶子节点被拆分时未提交的事务数量；kdxlende表示被删除的索引条目的数量；kdxlenxt表示当前叶子节点的下一个叶子节点的地址；kdxlprv表示当前叶子节点的上一个叶子节点的地址；kdxledsz表示可用空间，目前是0。
转储文件中接下来的部分就是索引条目部分，每个条目包含一个ROWID，指向一个表里的数据行。
如下所示。
其中flag表示标记，比如删除标记等；而lock表示锁定信息。
col0表示索引键值，其算法与我们在前面介绍分支节点时所说的算法一致。
col1表示ROWID。
我们同样可以看到，该叶子节点中包含了359个索引条目，与我们前面所估计的一个叶子节点中大约可以放360个索引条目也是基本一致的。
row#0[8018]flag:
-----,lock:
0
col0;len8;（8）:
3130303030333933
col1;len6;（6）:
01402e930016
row#1[8000]flag:
-----,lock:
0
col0;len8;（8）:
3130303030333933
col1;len6;（6）:
01402ee7000e
…………
row#358[1574]flag:
-----,lock:
0
col0;len8;（8）:
3130303030333937
col1;len6;（6）:
014018ba001f
-----endofleafblockdump-----
3. B树索引的访问
我们已经知道了B树索引的体系结构，那么当oracle需要访问索引里的某个索引条目时，oracle是如何找
到该索引条目所在的数据块的呢？
当oracle进程需要访问数据文件里的数据块时，oracle会有两种类型的I/O操作方式：
随机访问，每次读取一个数据块（通过等待事件“dbfilesequentialread”体现出来）。
顺序访问，每次读取多个数据块（通过等待事件“dbfilescatteredread”体现出来）。
第一种方式则是访问索引里的数据块，而第二种方式的I/O操作属于全表扫描。
这里顺带有一个问题，为
何随机访问会对应到dbfilesequentialread等待事件，而顺序访问则会对应到dbfilescatteredread等待事件呢？
这似乎反过来了，随机访问才应该是分散（scattered）的，而顺序访问才应该是顺序（sequential）的。
其实，等待事件主要根据实际获取物理I/O块的方式来命名的，而不是根据其在I/O子系统的逻辑方式来命名的。
下面对于如何获取索引数据块的方式中会对此进行说明。
我们看到前面对B树索引的体系结构的描述，可以知道其为一个树状的立体结构。
其对应到数据文件里的
排列当然还是一个平面的形式，也就是像下面这样。
因此，当oracle需要访问某个索引块的时候，势必会在这个结构上跳跃的移动。
/根/分支/分支/叶子/…/叶子/分支/叶子/叶子/…/叶子/分支/叶子/叶子/…/叶子/分支/.....
当oracle需要获得一个索引块时，首先从根节点开始，根据所要查找的键值，从而知道其所在的下一层的分支节点，然后访问下一层的分支节点，再次同样根据键值访问再下一层的分支节点，如此这般，最终访问到最底层的叶子节点。
可以看出，其获得物理I/O块时，是一个接着一个，按照顺序，串行进行的。
在获得最终物理块的过程中，我们不能同时读取多个块，因为我们在没有获得当前块的时候是不知道接下来应该访问哪个块的。
因此，在索引上访问数据块时，会对应到dbfilesequentialread等待事件，其根源在于我们是按照顺序从一个索引块跳到另一个索引块，从而找到最终的索引块的。
那么对于全表扫描来说，则不存在访问下一个块之前需要先访问上一个块的情况。
全表扫描时，oracle知道要访问所有的数据块，因此唯一的问题就是尽可能高效的访问这些数据块。
因此，这时oracle可以采用同步的方式，分几批，同时获取多个数据块。
这几批的数据块在物理上可能是分散在表里的，因此其对应到dbfilescatteredread等待事件。
4. B树索引的管理机制
4.1B树索引的对于插入（INSERT）的管理
对于B树索引的插入情况的描述，可以分为两种情况：
一种是在一个已经充满了数据的表上创建索引时，
索引是怎么管理的；另一种则是当一行接着一行向表里插入或更新或删除数据时，索引是怎么管理的。
对于第一种情况来说，比较简单。
当在一个充满了数据的表上创建索引（createindex命令）时，oracle会先扫描表里的数据并对其进行排序，然后生成叶子节点。
生成所有的叶子节点以后，根据叶子节点的数量生成若干层级的分支节点，最后生成根节点。
这个过程是很清晰的。
但是对于第二种情况来说，会复杂很多。
我们结合一个例子来说明。
为了方便起见，我们在一个数据块为2KB的表空间上创建一个测试表，并为该表创建一个索引，该索引同样位于2KB的表空间上。
SQL>createtableindex_test（idchar（150））tablespacetbs_2k;
SQL>createindexidx_testonindex_test（id）tablespacetbs_2k;
当一开始在一个空的表上创建索引的时候，该索引没有根节点，只有一个叶子节点。
我们以树状形式转储上面的索引idx_test。
SQL>selectobject_idfromuser_objectswhereobject_name='IDX_TEST';
OBJECT_ID
----------
7390
SQL>altersessionsetevents'immediatetracenametreedumplevel7390';
从转储文件可以看到，该索引中只有一个叶子节点（leaf）。
-----begintreedump
leaf:
0x1c001a229360546（0:
nrow:
0rrow:
0）
-----endtreedump
随着数据不断被插入表里，该叶子节点中的索引条目也不断增加，当该叶子节点充满了索引条目而不能再放下新的索引条目时，该索引就必须扩张，必须再获取一个可用的叶子节点。
这时，索引就包含了两个叶子节点，但是两个叶子节点不可能单独存在的，这时它们两必须有一个上级的分支节点，其实这也就是根节点了。
于是，现在，我们的索引应该具有3个索引块，一个根节点，两个叶子节点。
我们来做个试验看看这个过程。
我们先试着插入插入10条记录。
注意，对于2KB的索引块同时PCTFREE为缺省的10％来说，只能使
升级会员 