Oracle中索引树的结构与块的尺寸

     关于Oracle索引树的结构以及它们对Oracle性能调优是否重要存在大量的、激烈的争论，而且已经有很多文章试图来描述这些重要的Oracle性能工具的内部工作机制。关于这个论题也出现了一些新书，例如由“国际Oracle用户组”（IOUG）主席Kim Floss所著的《Oracle索引治理秘诀》和《Oracle SQL 性能调优和“基于代价的优化器”内幕》。正如我们知道的，Oracle提供了大量索引结构，每种索引结构都有其好处和不足： * B树索引。从最早的Oracle发行版本开始，Oracle就一直使用的标准树索引就是B树索引。
* 位图索引。当某个索引列含有很少数量的不同的值（即低基数）时，使用位图索引。这对于那些只读数据库而言速度超快，但对需要经常性更新的系统不适合。
* 位图连接索引。这是针对来自其他表的数据列出现在某个连接表的多列索引时使用的索引。下面是在from子句和where子句中使用类似SQL的创建索引的惟一语法。 create bitmap index
   part_suppliers_state
on
   inventory( parts.part_type, supplier.state )
from
   inventory i,
   parts     p,
   supplier s
where
   i.part_id=p.part_id
and
   i.supplier_id=p.supplier_id;     尽管有关索引重建的争论仍在激烈进行着，但还是存在每个人都认可的索引治理的某些领域。在内部机制上，一个Oracle B-树索引的结构和一个UNIX I-结点的结构非常相似。索引中的每个数据块都是索引树中的一个结点，位于最底部的结点（叶数据块）包含一对符号键和行ID值。 Oracle b-树索引     为了正确治理这些数据块，Oracle控制着每个数据块中指针的分配。随着一棵Oracle树的增长（通过往表里插入新行），Oracle会填充这个数据块，当这个数据块满时Oracle会分裂它，创建新的索引结点（数据块）来治理索引内的符号键。因此，一个Oracle索引块可能包含以下两种类型的指针： * 指向其他索引结点（数据块）的指针
* 指向数据库表中特定行的行ID指针     Oracle治理着索引块内指针的分配，这就是为什么我们不能为索引指定一个PCTUSED值（自由列表重链接门槛）的原因。当我们检查一个索引块的结构时，我们发现每个索引结点内部条目的数量是下面两个值的一个函数： 1．符号键的长度
2．索引表空间的块尺寸     由于块尺寸影响每个索引结点内部的符号键的数量，可以推理出：块尺寸对一棵索引树的结构也会有影响。在其他条件相同的情况下，采用32K的大数据块能容纳更多的符号键，从而能够比在2K表空间中创建的相同的索引更加平整。采用大的数据块也将减少索引访问期间一致获取的数量，从而提高分散读访问的性能。     索引中的每个数据块包含索引树中的“结点”，位于最底部的结点（叶数据块）包含一对符号键和行ID值。随着一棵Oracle树的增长（通过往表里插入新行），Oracle会填充这个数据块，当这个数据块满时Oracle会分裂它，创建新的索引结点（数据块）来治理索引内的符号键。因此，一个Oracle索引块可能包含指向其他索引结点或行ID/符号键对的指针。 索引行为和Oracle块尺寸     由于块尺寸影响每个索引结点内部的符号键的数量，可以推理出：块尺寸对一棵索引树的结构也会有影响。在其他条件相同的情况下，采用32K的大数据块能容纳更多的符号键，从而能够比在2K表空间中创建的相同的索引更加平整。     今天，大多数Oracle性能调优专家都利用Oracle提供的多种块尺寸的特色，因为它提供了缓冲区隔离和以最合适块尺寸来存放对象从而减少缓冲区浪费的能力。一些Oracle基准测试的世界记录都使用很大的数据缓冲区和多种块尺寸。     根据《Oracle数据库治理员认证：“Oracle 认证数据库治理专家”教师指南》一书的作者Christopher Foot的一篇文章，更大的块尺寸在某些情况下非常有帮助：     “更大的块尺寸意味着在B-树索引的分支结点中有更多的空间来存储符号键，从而可以降低树的高度和提高索引查询的性能。”     在任何情况下，似乎有证据表明块尺寸影响树的结构，这为数据块影响树的结构提供了有力支持。     你可以使用大数据块（16-32）缓冲区来存储来自作为重复性大规模扫描对象的索引或表中的数据。这真的会提高性能么？一个小的但透漏内情的测试能回答这个问题。     在这个测试中，将对某个使用8K数据块尺寸的Oracle 9i数据库执行以下查询，这个数据库同时也使用16K缓冲区和16K大小的表空间。
select
   count(*)
from
   eradmin.admission
where
   patient_id between 1 and 40000; 表eradmin.admission含有150,000行数据，并在patient_id列上建立了一个索引。对上面的查询语句执行EXPLAIN命令揭示出：它使用索引范围扫描来产生想要的目的结果： Execution Plan
----------------------------------------------------------
SELECT STATEMENT Optimizer=CHOOSE
(Cost=41 Card=1 Bytes=4)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FAST FULL SCAN) OF 'ADMISSION_PATIENT_ID'
              (NON-UNIQUE) (Cost=41 Card=120002 Bytes=480008)
    使用位于一个标准8K表空间的索引来执行这个查询（两次以消除分析活动并缓冲任何数据）产生了以下实时统计信息： Statistics
---------------------------------------------------
          0 recursive calls
          0 db block gets
        421 consistent gets
          0 physical reads
          0 redo size
        371 bytes sent via SQL*Net to client
        430 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed     为了测试新的16K缓冲区和16K表空间的效果，将使用16K的表空间来重建这个查询所用的索引，16K的表空间和原来的8K的表空间相比，除了更大的块尺寸其他特性一模一样。 alter index
      eradmin.admission_patient_id
      rebuild nologging noreverse tablespace indx_16k;     一旦在16K表空间中建立好这个索引，就再次执行这个查询（同样也是执行两次），会产生以下的运行时统计信息： Statistics
---------------------------------------------------
          0 recursive calls
          0 db block gets
        211 consistent gets
          0 physical reads
          0 redo size
        371 bytes sent via SQL*Net to client
        430 bytes received via SQL*Net from client
          2 SQL*Net roundtrips to/from client
          0 sorts (memory)
          0 sorts (disk)
          1 rows processed
    正如你所看到的，逻辑读操作的次数被减少了一半，仅仅是由于使用了新的16K表空间和16K数据缓冲区。很显然，正确使用新的数据缓冲区和Oracle9i及其以上版本的多种块尺寸表空间的特色，很值得在你的数据库中被试验和研究。 定期进行索引重建？     争论的另外一个领域是：是否存在一些确定的规则可以用来确定何时能够从索引重建中获取性能提高。许多使用Oracle的企业都定期进行索引重建，并声称在他们和重建他们的Oracle B-树索引后获得了相当大的速度提升。     在“Oracle世界2003”上，展示了Sushil Kumar所著的一篇题为“Oracle数据库10g：自我治理的数据库”的文章，Kumar声明说Oracle10g的“自动维护任务”（AMT）特色将自动检测并重建潜在的最优化的索引。     “自动工作负载信息库（AWR）给oracle10g提供了关于数据库各种使用情况的具体信息。通过分析存储在自动工作负载信息库（AWR）中的信息，10g数据库可以决定是否需要执行数据库性能维护任务，比如优化器统计数字的刷新、重建索引等等。以“自动维护任务”AMT为基础，oracle数据库可以自动执行这些操作。”     尽管如此，仍有不少反对定期重建索引的论点。一些oracle内部的专家坚持认为oracle索引在空间重用和访问速度上是非常高效的，b－树索引在极少情况下才需要被重建。他们坚持认为逻辑输入输出的减少应该是可测量的，并且假如重建索引是有好处的，某些人应该已经提出了可以证实的规则。结论     多种数据块尺寸的特色能够提高Oracle索引的性能，而且在某些情况下重建索引可以提高查询速度，这是显而易见的。人们期望新的Oracle10g“自动维护任务”AMT答应自动检测并重建潜在的最优化索引结构。     假如你喜欢Oracle性能调优技巧，你或许会喜欢Rampant科技出版社出版的我的最新著作“创建具有自我调优功能的Oracle数据库”它仅售9.95美元（我认为收取书费是不对的！）