Oracle索引类型图_B树索引,位图索引,HASH索引

索引类型

• B树索引
• 位图索引
• HASH索引
• 索引组织表IOT
• 反转键（reverse keys）索引
• 基于函数的索引
• 分区索引（本地索引、全局索引）
• 位图连接索引

1、B树索引

B树索引在Oracle中是一个通用索引，创建索引的时候默认就是B树索引。

单列索引

组合索引（最多可以多达32个列）

对于B树索引，我们需要关注他的二元高度（blevel）。

B树索引保存了在索引列上有值的每个数据行的ROWID。

Oracle不会对索引列上包含NULL值的行进行索引，如果索引是一个组合索引，而其中列上包含NULL值，这一行会于索引列中。

• 适合与大量的增、删、改（OLTP）；
• 不能用包含OR操作符的查询；
• 适合高基数的列（唯一值多）
• 典型的树状结构；
• 每个结点都是数据块；
• 大多都是物理上一层、两层或三层不定，逻辑上三层；
• 叶子块数据是排序的，从左向右递增；
• 在分支块和根块中放的是索引的范围。

2、位图索引

• 适合与决策支持系统（OLAP），不适用与OLTP系统；
• 做UPDATE代价非常高；
• 非常适合OR操作符的查询；
• 基数比较少的时候才能建位图索引；

2.1位图索引的描述

对于位图索引的列，列值的数量要求较少或者中等（索引列基数较小）。

前面的例子中，列的基数是4。

Oracle为每个唯一键创建一个位图，然后把与键值所关联的ROWID保存为位图。最多可以包括30列。

位图发挥最大威力的场合是：当一个表中包含了多个位图索引，Oracle就可以合并从每个位图索引得到的结果集，快速删除不必要的数据。

对于较低基数的位图索引来说，位图索引的尺寸远远的小于B树索引，因此可以大大的减少IO的数量。

对于非常大的表来说，在多个低基数列上建立位图索引是一个很好的选择。

对于位图索引来说，即使从表中读取很多行，也会使用位图索引。

例如在一个sex列上建立索引，每次从表中读取半数的数据行，但是还是会使用位图索引。

2.2位图索引的插入

（1）位图索引在批处理（单用户）操作中加载表（插入操作）方面通常比B树做得好；

（2）当有多个会话同时向表中插入数据行时不应该使用位图索引；

（3）当每条记录都增加一个新值时，B-树索引要比位图索引快3倍。

在B树索引中，可以实现行级锁定，但是在位图索引中，因为对ROWID进行压缩存放（一个ROWID范围+位图），因此每次锁定的都是整个的ROWID范围，因此对表中的位图索引列进行更新的时候，并发性很差，容易导致死锁。SELECT语句不会受到这种锁定问题的影响。

2.3位图索引有很多的限制

（1）基于规则的优化器不会考虑位图索引；

（2）当执行alter table语句并修改包含位图索引列时，会使得位图索引失效；

（3）位图索引不包含任何列数据，不能用于任何类型的完整性检查；例如主键、唯一键约束；

（4）位图索引不能被声明为唯一索引；

（5）位图索引的最大长度为30。

注意：不要在繁忙的OLTP系统中使用位图索引。

3、HASH 索引

使用HASH索引必须要使用hash cluster。我们首先来看一下cluster表的结构。

HASH索引可能是访问数据库中数据的最快方法，但是有自身的缺点：

（1）集群键上不同值的数目必须在创建hash集群之前就需要确定，需要在创建hash集群的时候指定这个值，低估了集群键的不同值的数字可能会导致集群的冲突（两个集群键有相同的HASH值）；

（2）一旦这个值设置过低，需要重建cluster；

（3）hash集群还可能浪费空间，如果无法确定需要多少空间来维护某个集群键上的所有行，就可能造成空间浪费；

（4）如果不能为集群的未来增长分配好附加的空间，hash集群可能就不是最好的选择；

（5）如果应用程序经常在集群上进行全表扫描，hash集群可能不是最好的选择，由于需要为未来增长分配好集群的剩余空间，全表扫描可能非常的消耗资源。

HASH索引非常适合数据仓库（相对静态值）。

4、索引组织表（IOT）

索引组织表会把表的存储结构改成B树结构，以表的主键进行排序。对于频繁在主键列上进行精确查找、范围查找的表来说，可以考虑使用IOT。我们还可以在IOT上建立二级索引。

SQL> CREATE TABLE countries 
    ( country_id      CHAR(2) 
      CONSTRAINT  country_id_nn NOT NULL,
      country_name    VARCHAR2(40),
      currency_name   VARCHAR2(25),
      currency_symbol VARCHAR2(3),
      map             BLOB,
      flag            BLOB,
      CONSTRAINT     country_c_id_pk 
        PRIMARY KEY (country_id)) 
    ORGANIZATION INDEX
    PCTTHRESHOLD 20
           INCLUDING(currency_symbol)
    OVERFLOW TABLESPACE USERS;

organization index，注意两点：

• 创建IOT时，必须要设定主键，否则报错。
• 索引组织表实际上将所有数据都放入了索引中。

索引组织表属性

（1）OVERFLOW子句(行溢出) 因为所有数据都放入索引，所以当表的数据量很大时，会降低索引组织表的查询性能。此时设置溢出段将主键和溢出数据分开来存储以提高效率。溢出段的设置有两种格式：PCTTHRESHOLD n ：制定一个数据块的百分比，当行数据占用大小超出时，该行的其他列数据放入溢出段 INCLUDING column_name ：指定列之前的列都放入索引块，之后的列都放到溢出段。

• 当行中某字段的数据量无法确定时使用PCTTHRESHOLD；
• 若所有行均超出PCTTHRESHOLD规定大小，则考虑使用INCLUDING。create table t88( ID varchar2 ( 10 ), NAME varchar2 ( 20 ), constraint pk_id primary key ( ID ) ) organization index PCTTHRESHOLD 20 overflow tablespace users INCLUDING name ;
• 如上例所示，name及之后的列必然被放入溢出列，而其他列根据 PCTTHRESHOLD 规则。

（2）COMPRESS子句(键压缩)。

5、反转索引

当载入一些有序的数据时，索引肯定会碰到与IO相关的一些瓶颈。在载入期间，某部分索引和磁盘肯定会比其他部分使用频繁的多。

为了解决这个问题，我们可以使用反转索引，这样对于有序数据的载入，oracle会更新不同的索引块。

1234、1235、1236，如果数据以反转索引存储，那么这些数据的存放形式为：4321、5321、6321，结果就是索引会为每次新插入的行更新不同的索引块。

创建反转索引

SQL> create unique index i1_t1 ON t1(c1)
    REVERSE pctfree 30
    storage(initial 200k  next 200k
            pctincrease 0 maxextents 50)
    tablespace indx;
SQL> create unique index i2_t1 ON t1(c2);
SQL> alter index i2_t1 REBUILD REVERSE;

6、基于函数的索引

Function-Based Indexes

SQL> CREATE INDEX FBI_UPPER_LASTNAME
    ON CUSTOMERS(upper(cust_last_name));
SQL> SELECT *
           FROM   customers
           WHERE  upper(cust_last_name) = 'SMITH';

基于函数的索引需要考虑的问题

（1）基于函数的索引只能针对一种函数，对于其余的函数不起作用；

（2）控制索引的数量，因为对DML会产生影响。

对于查询使用函数的情况，基于函数的索引会带来极大的好处。

7、分区索引

分区索引就是简单的把一个索引分成多个片段，这样可以访问更小的片段，可以将片段分别放在不同的磁盘驱动器上。

B树索引和位图索引都可以被分区，而hash索引不能被分区。

（1）表被分区而索引未被分区；

（2）表未分区而索引被分区；

（3）表和索引都被分区。

无论哪种方式，都必须使用基于成本的优化器，分区可以提高性能和可维护性。

（1）本地分区索引

有前缀索引、无前缀索引

（2）全局分区索引

有前缀索引、无前缀索引

如果使用了位图索引，就必须使用本地索引。

索引分区最主要的原因就是减少所需读取的索引大小，另外把分区放在不同的表空间中可以提高分区的可用性和可靠性。

在使用分区后的表和索引时，oracle还支持并行查询和并行DML，这样可以同时执行多个进程，从而加速处理这条语句。

通常使用的索引，使用与表相同的分区键和范围界限来对本地索引分区。

每个本地索引的分区只包含它所关联的表分区的键和ROWDI。

也就是说：表分区和索引分区是相同的

本地分区索引可以是B树索引、位图索引，如果是B树索引，可以是唯一或不唯一的索引。

对于分区表来说，每一个分区表有很大的独立性，对于分区索引来说，每个分区索引有很大的独立性。Oracle会自动维护本地索引。本地索引可以被单独重建，在drop或truncate某个分区时也不影响该索引的其他分区索引的使用。

有前缀的索引

有前缀的索引包含了来自分区键的键，并把它们作为索引的前导。

Participant表进行范围分区：分区列（survey_id、survy_date），然后我们在survey_id列上建立了一个有前缀的本地索引。索引的分区都使用表的相同范围界限来创建。

本地的前缀索引可以让oracle快速剔除一些不必要的分区，也就是说没有包含在where条件子句中任何值的分区将不会被访问，这样可以大大的提高访问性能。

无前缀的索引

无前缀的索引并没有把分区键的前导列作为索引的前导列。若使用有同样分区键（survey_id、survey_date）的相同分区表，建立在survy_date上的索引就是一个本地的无前缀索引。只要是本地索引（有前缀、无前缀）,索引分区和表分区是一一对应的。

如果要把无前缀的索引设为唯一索引，这个索引就必须包含分区键的子集。

全局分区索引在一个索引分区中包含来自多个表分区的键。一个全局分区索引的分区键是分区表中不同的或指定一个范围的值。oracle会对主键自动创建全局索引。

在创建全局分区索引时，必须定义分区键的范围和值。

全局索引只能使用B树索引，Oracle在默认情况下不会维护全集索引分区，如果一个表分区被截取、增加、分割、删除等，就必须重建全局分区索引，除非在修改表时指定alter table …update global indexes。

有前缀的索引

每个分区索引都包含指向多个表分区中行的索引条目。

无前缀的索引

8、位图连接索引

和 Bitmap Index 建立在单个table上不同、Bitmap Join Index 是基于多表连接的，较普遍的用法是事实表的外键列和相关的维度表的主键列的连接操作。

• 事实表：你要关注的内容、比如高考状元；
• 维度表：你通过什么来关注、比如按地区、那么是福建的？北京的？感觉维度表是个窗口、不同窗口看到的内容不同。

SQL中，连接查询非常耗费时间。将一个表中的数据与另一张表的数据进行一一对应建立的索引表就称为连接索引。不仅适用于两个表的连接，还可以进行多个表的连接。

创建注意项：

①创建位图连接索引时WHERE 子句中的关联条件列必须是主键或唯一约束(不符合条件会报错ORA-25954: missing primary key or unique constraint on dimension)；

②在指定索引列时,如果两表都具有相同列,必须使用表名.列名的方式指定或者别名.列名的方式指定(不符合条件会报错:ORA-00918: column ambiguously defined)；

③创建位图索引同样DML操作效率差，建立前请仔细分析表结构和数据DML操作率；

④更新表数据时同样会有锁定主导表、关联表问题。

因为位图连接索引事前缓存了查询的结果集、所以造成如下几点限制：

转载请注明：XAMPP中文组官网 » Oracle索引类型图_B树索引,位图索引,HASH索引