Oracle --- 索引

一、索引的概念

索引是一种与表相关的可选数据对象。通过在表中的一个或多个列上创建索引，就能够为数据的检索提供快捷的存取路径，减少查询时所需的磁盘I/O操作，加快数据的检索速度。

 

索引是将创建列的键值和对应记录的物理记录号(ROWID)排序后存储起来，需要占用额外的存储空间来存放。由于索引占用的控件远小于表所占用的实际控件，在系统通过索引进行数据检索时，可先将索引调入内存，通过索引对记录进行定位，大大减少了磁盘I/O操作次数，提高检索效率。一般而言，表中的记录数据越多，索引带来的效率提高就越明显，所以在数据库系统中，索引是必不可少的数据对象之一。

 

创建或删除索引的操作不会影响到数据库中的表、数据库应用程序或其他的索引。这是索引独立性的一个体现。如果用户删除一个索引或索引损坏了，任何应用程序任然还能够继续正常工作，唯一受影响的可能就是某些查询的速度会减慢。但是反过来，创建或删除表的操作，却会引起在该表上进一步创建或删除索引的操作。

 

建立在表上的索引是一个独立于表的数据对象，，它可以被存储在于表不同的磁盘或表空间中，有单独为其设立命名的存储结构，即索引段。索引一旦被创建，在表上执行DML操作时Oracle就会自动对索引进行维护，并且由Oracle决定何时使用索引，用户完全不需要在SQL语句中指定使用哪个索引、如何使用索引。无论在表上是否创建了索引，编写和使用SQL语句都是一样的。

 

提示：由于Oracle有时也会利用索引，如唯一索引来实现一些完整性约束，因此在创建主键约束时会自动创建主键索引。

 

二、索引的创建语法

创建语法：

CREATE [UNIQUE]|[BITMAP] INDEX [user.]index_name

ON [user.]table_name(column [ASC | DESC][,column] [ASC | DESC] ...)

[CLUSTER [scheam.]cluster]

[INITTRANS n]

[MAXTRANS n]

[PCTFREE n]

[STORAGE storage]

[TABLESPACE tablespace]

[NO SORT]

[REVERSE]

 

参数说明：

UNIQUE : 用于创建唯一索引

BITMAP : 位图索引，如果不指定BITMAP选项，则默认创建的是B树索引

index_name : 索引名称

table_name : 创建索引的基表名

column : 基表中的列名，一个索引最多有16列，long列、long raw列不能建索引列

DESC、ASC : 缺省为ASC，即升序排序

CLUSTER : 指定一个聚簇(Hash cluster不能建索引)

INITRANS、MAXTRANS : 指定初始和最大事务入口数

PCTFREE : 索引数据块空闲空间的百分比(不能指定pctused)

Tablespace : 表空间名

STORAGE : 存储参数，同create table中的storage

NO SORT : 不排序(存储时就已按升序，所以指出不再排序)

REVERSE : 反向键索引

 

 

三、创建索引

3.1、普通索引

 

create index INDEX_TESTINDEX on T_INDEXTEST(name);

 

 

 

 

 

3.2、唯一索引

如果我们需要在某个表某个列创建索引，而这个列的值是不会重复的。这时我们可以创建唯一索引。

 

create unique index INDEX_TESTINDEX_ID on T_INDEXTEST(id);

 

 

 

 

 

3.3、复合索引

我们经常要对某几列进行查询，我们可以建立复合索引，也就是基于两个以上的列建立索引。

 

create index owners_index_ah on T_OWNERS(addressid,housenumber);

 

 

3.4、反向键索引

 

当某个字段的值为连续增长的值，如果构建标准索引，会形成歪脖子树。这样会增加查询的层数，性能会下降。建立反向键索引，可以使索引的值变得不规则，从而使索引树能够均匀分布。

 

 

create index test_reverse_index on T_TESTREVERSEINDEX(avalue)

 

 

使一个索引变为反向键索引：

 

ALTER INDEX INDEX_TEST1 REBUILD NOREVERSE;

 

 

 

 

 

3.4、位图索引

用于重复率高的列创建索引，比如sex列只有男和女；类型列，这样的列适合创建位图索引

 

create bitmap index index_owners_typeid on T_OWNERS(ownertypeid)

 

 

 

 

 

3.5、函数索引

基于函数的索引就是存储预先计算好的函数或表达式的索引。这些表达式可以是算数运算表达式、SQL或PL/SQL函数、C调用等。值得注意的是，一般用户要创建函数索引，必须具有GLOBAL QUERY REWRITE和CREATE ANY INDEX权限。否则不能创建函数索引。

 

create index idx on T_EMP(UPPER(ename))

授权语句：

 

 

grant GLOBAL QUERY REWRITE to scott;

grant CREATE ANY INDEX to scott;

 

3.6、索引组织表

 

 

 

 

 

与普通的索引不一样，索引组织表（Index_Organized Table）是根据表来存储数据，即将索引和表存储在一起。这样的索引结构表（Index_organized table—IOT）的特点是：对表数据的改变，如插入一新行、删除某行都引起索引的更新。

索引组织表就象带一个或多个列所有的普通表一样，但索引组织表在B-树索引结构的叶节点上存储行数据。通过在索引结构中存储数据，索引组织表减少了总的存储量，此外，索引组织表也改善访问性能。

由于表中的行与B_树索引存放在一起，每个行都没有ROWID，而是用主键来标识。但是Oracle会“猜”这些行的位置并为每个行分配逻辑的ROWID。此外，你可以为这样的表建立第二个索引。

创建索引结构表也是用CREATE TABLE 命令加ORGANIZATION INDEX关键字来实现。但是，这样的表在创建完后，你还必须为该表建立一个主键。

例子：

 

CREATE TABLE IOT_EXPAMPLE
(
Pk_col1 number(4),
Pk_col2 varchar2(10),
Non_pk_col1 varchar2(40),
Non_pk_col2 date,
CONSTRAINT pk_iot PRIMARY KEY
                 ( pk_col1, pk_col2)
)
ORGANIZATION INDEX
TABLESPACE INDEX
STORAGE( INITIAL 1M NEXT 512K PCTINCREASE 0 );

 

 

 

索引组织表有些限制：

• 不能使用唯一约束；
• 必须具有一个主键；
• 不能建立簇；
• 不能包含LONG类型列；
• 不支持分布和复制。

 

 

 

提示：如果建立了索引组织表，则会在DBA_TABLES中的IOT_TYPE和IOT_NAME列上记录有索引组织表的信息。

 

例1．修改索引结构表 docindex 的索引段的INITRANS参数：

 

ALTER TABLE docindex INITRANS 4;

 

 

例2．下面语句加一个的溢出数据段到索引组织表 docindex中：

 

ALTER TABLE docindex ADD OVERFLOW;

 

 

 

例3．下面语句为索引组织表 docindex的溢出数据段修改INITRANS参数：

 

ALTER TABLE docindex OVERFLOW INITRANS 4;

 

 

四、删除索引

一般来讲，若出现如下几种情况之一将有必要删除响应的索引。

 

• 索引的创建不合理或不必要，应删除该索引，以释放其占用的控件。
• 通过一段时间的监视，发现几乎没有查询，或者只有极少数查询会使用到该索引
• 由于该索引中包含损坏的数据块，或者包含过多的存储碎片，需要首先删除该索引，然后再重建该索引。
• 如果移动了表的数据，导致索引无效，此时需要删除并重建该索引。
• 当使用SQL*Loader给单个表装载数据时，系统也会同时给该表的索引增加数据，为了加快数据装载的速度，应在装载之前删除所有索引，然后再数据装载完毕之后重新创建各个索引。

 

删除索引时，如果要在自己的方案中删除索引，需要具有DROP INDEX系统权限；如果要在其他用户的方案中删除索引，需要具有DROP ANY INDEX系统权限。

如果索引时在定义约束时由Oracle自动建立的，则可以通过禁用约束(DISABLE)或删除约束的方式来删除对应的索引。

 

 

示例:

DROP INDEX index_test1;

 

 

 

 

五、索引的分类

B-tree索引(默认类型)

位图索引

HASH索引

索引组织表索引

反转键(reverse key)索引

基于函数的索引

分区索引(本地和全局索引)

位图连接索引

 

5.1、B树索引(默认类型)

 

B树索引在Oracle中是一个通用索引。在创建索引时它就是默认的索引类型。B树索引可以是一个列的(简单)索引，也可以是组合/复合(多个列)的索引。B树索引最多可以包括32列。

在下图的例子中，B树索引位于雇员表的last_name列上。这个索引的二元高度为3；接下来，Oracle会穿过两个树枝块(branch block)，到达包含有ROWID的树叶块。在每个树枝块中，树枝行包含链中下一个块的ID号。
树叶块包含了索引值、ROWID，以及指向前一个和后一个树叶块的指针。Oracle可以从两个方向遍历这个二叉树。B树索引保存了在索引列上有值的每个数据行的ROWID值。Oracle不会对索引列上包含NULL值的行进行索引。如果索引是多个列的组合索引，而其中列上包含NULL值，这一行就会处于包含NULL值的索引列中，且将被处理为空(视为NULL)。
                         

 

技巧：索引列的值都存储在索引中。因此，可以建立一个组合(复合)索引，这些索引可以直接满足查询，而不用访问表。这就不用从表中检索数据，从而减少了I/O量。

 

B-tree 特点：

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

 

5.2  位图索引
位图索引非常适合于决策支持系统(Decision Support System，DSS)和数据仓库，它们不应该用于通过事务处理应用程序访问的表。它们可以使用较少到中等基数(不同值的数量)的列访问非常大的表。尽管位图索引最多可达30个列，但通常它们都只用于少量的列。
 

例如，您的表可能包含一个称为Sex的列，它有两个可能值：男和女。这个基数只为2，如果用户频繁地根据Sex列的值查询该表，这就是位图索引的基列。当一个表内包含了多个位图索引时，您可以体会到位图索引的真正威力。如果有多个可用的位图索引，Oracle就可以合并从每个位图索引得到的结果集，快速删除不必要的数据。

Bitmapt 特点：

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

技巧：

 

对于有较低基数的列需要使用位图索引。性别列就是这样一个例子，它有两个可能值：男或女(基数仅为2)。位图对于低基数(少量的不同值)列来说非常快，这是因为索引的尺寸相对于B树索引来说小了很多。因为这些索引是低基数的B树索引，所以非常小，因此您可以经常检索表中超过半数的行，并且仍使用位图索引。

当大多数条目不会向位图添加新的值时，位图索引在批处理(单用户)操作中加载表(插入操作)方面通常要比B树做得好。当多个会话同时向表中插入行时不应该使用位图索引，在大多数事务处理应用程序中都会发生这种情况。

示例
下面来看一个示例表PARTICIPANT，该表包含了来自个人的调查数据。列Age_Code、Income_Level、Education_Level和Marital_Status都包括了各自的位图索引。下图显示了每个直方图中的数据平衡情况，以及对访问每个位图索引的查询的执行路径。图中的执行路径显示了有多少个位图索引被合并，可以看出性能得到了显著的提高。

                      


如上图图所示，优化器依次使用4个单独的位图索引，这些索引的列在WHERE子句中被引用。每个位图记录指针(例如0或1)，用于指示表中的哪些行包含位图中的已知值。有了这些信息后，Oracle就执行BITMAP AND操作以查找将从所有4个位图中返回哪些行。该值然后被转换为ROWID值，并且查询继续完成剩余的处理工作。注意，所有4个列都有非常低的基数，使用索引可以非常快速地返回匹配的行。

技巧：在一个查询中合并多个位图索引后，可以使性能显著提高。位图索引使用固定长度的数据类型要比可变长度的数据类型好。较大尺寸的块也会提高对位图索引的存储和读取性能。

下面的查询可显示索引类型。
SQL> select index_name, index_type from user_indexes;

INDEX_NAME         INDEX_TYPE

------------------------------ ----------------------

TT_INDEX            NORMAL

IX_CUSTADDR_TP    NORMAL

B树索引作为NORMAL列出；而位图索引的类型值为BITMAP。

技巧：

如果要查询位图索引列表，可以在USER _INDEXES视图中查询index_type列。
建议不要在一些联机事务处理(OLTP)应用程序中使用位图索引。B树索引的索引值中包含ROWID，这样Oracle就可以在行级别上锁定索引。位图索引存储为压缩的索引值，其中包含了一定范围的ROWID，因此Oracle必须针对一个给定值锁定所有范围内的ROWID。这种锁定类型可能在某些DML语句中造成死锁。SELECT语句不会受到这种锁定问题的影响。
 

位图索引的使用限制：

 

• 基于规则的优化器不会考虑位图索引。
• 当执行ALTER TABLE语句并修改包含有位图索引的列时，会使位图索引失效。
• 位图索引不包含任何列数据，并且不能用于任何类型的完整性检查。
• 位图索引不能被声明为唯一索引。
• 位图索引的最大长度为30。

技巧：不要在繁重的OLTP环境中使用位图索引


5.3  HASH索引
使用HASH索引必须要使用HASH集群。建立一个集群或HASH集群的同时，也就定义了一个集群键。这个键告诉Oracle如何在集群上存储表。在存储数据时，所有与这个集群键相关的行都被存储在一个数据库块上。如果数据都存储在同一个数据库块上，并且将HASH索引作为WHERE子句中的确切匹配，Oracle就可以通过执行一个HASH函数和I/O来访问数据——而通过使用一个二元高度为4的B树索引来访问数据，则需要在检索数据时使用4个I/O。如下图所示，其中的查询是一个等价查询，用于匹配HASH列和确切的值。Oracle可以快速使用该值，基于HASH函数确定行的物理存储位置。

 

HASH索引可能是访问数据库中数据的最快方法，但它也有自身的缺点。集群键上不同值的数目必须在创建HASH集群之前就要知道。需要在创建HASH集群的时候指定这个值。低估了集群键的不同值的数字可能会造成集群的冲突(两个集群的键值拥有相同的HASH值)。这种冲突是非常消耗资源的。冲突会造成用来存储额外行的缓冲溢出，然后造成额外的I/O。如果不同HASH值的数目已经被低估，您就必须在重建这个集群之后改变这个值。

 

ALTER CLUSTER命令不能改变HASH键的数目。HASH集群还可能浪费空间。如果无法确定需要多少空间来维护某个集群键上的所有行，就可能造成空间的浪费。如果不能为集群的未来增长分配好附加的空间，HASH集群可能就不是最好的选择。如果应用程序经常在集群表上进行全表扫描，HASH集群可能也不是最好的选择。由于需要为未来的增长分配好集群的剩余空间量，全表扫描可能非常消耗资源。

在实现HASH集群之前一定要小心。您需要全面地观察应用程序，保证在实现这个选项之前已经了解关于表和数据的大量信息。通常，HASH对于一些包含有序值的静态数据非常有效。

技巧：HASH索引在有限制条件(需要指定一个确定的值而不是一个值范围)的情况下非常有用。
                         


5.4  索引组织表
索引组织表会把表的存储结构改成B树结构，以表的主键进行排序。这种特殊的表和其他类型的表一样，可以在表上执行所有的DML和DDL语句。由于表的特殊结构，ROWID并没有被关联到表的行上。

对于一些涉及精确匹配和范围搜索的语句，索引组织表提供了一种基于键的快速数据访问机制。基于主键值的UPDATE和DELETE语句的性能也同样得以提高，这是因为行在物理上有序。由于键列的值在表和索引中都没有重复，存储所需要的空间也随之减少。

如果不会频繁地根据主键列查询数据，则需要在索引组织表中的其他列上创建二级索引。不会频繁根据主键查询表的应用程序不会了解到使用索引组织表的全部优点。对于总是通过对主键的精确匹配或范围扫描进行访问的表，就需要考虑使用索引组织表。

技巧：可以在索引组织表上建立二级索引。


5.5  反转键索引
当载入一些有序数据时，索引肯定会碰到与I/O相关的一些瓶颈。在数据载入期间，某部分索引和磁盘肯定会比其他部分使用频繁得多。为了解决这个问题，可以把索引表空间存放在能够把文件物理分割在多个磁盘上的磁盘体系结构上。

为了解决这个问题，Oracle还提供了一种反转键索引的方法。如果数据以反转键索引存储，这些数据的值就会与原先存储的数值相反。这样，数据1234、1235和1236就被存储成4321、5321和6321。结果就是索引会为每次新插入的行更新不同的索引块。

技巧：

如果您的磁盘容量有限，同时还要执行大量的有序载入，就可以使用反转键索引。
不可以将反转键索引与位图索引或索引组织表结合使用。因为不能对位图索引和索引组织表进行反转键处理。


5.6  基于函数的索引
可以在表中创建基于函数的索引。如果没有基于函数的索引，任何在列上执行了函数的查询都不能使用这个列的索引。例如，下面的查询就不能使用JOB列上的索引，除非它是基于函数的索引：
select * from emp where UPPER(job) = 'MGR';
下面的查询使用JOB列上的索引，但是它将不会返回JOB列具有Mgr或mgr值的行：
select * from emp where job = 'MGR';

可以创建这样的索引，允许索引访问支持基于函数的列或数据。可以对列表达式UPPER(job)创建索引，而不是直接在JOB列上建立索引，如：
create index EMP$UPPER_JOB on emp(UPPER(job));

尽管基于函数的索引非常有用，但在建立它们之前必须先考虑下面一些问题：

• 能限制在这个列上使用的函数吗？如果能，能限制所有在这个列上执行的所有函数吗
• 是否有足够应付额外索引的存储空间？
• 在每列上增加的索引数量会对针对该表执行的DML语句的性能带来何种影响？

 

基于函数的索引非常有用，但在实现时必须小心。在表上创建的索引越多，INSERT、UPDATE和DELETE语句的执行就会花费越多的时间。

注意：对于优化器所使用的基于函数的索引来说，必须把初始参数QUERY _REWRITE _ ENABLED设定为TRUE。

示例：
select  count(*) from  sample where ratio(balance,limit) >.5;
Elapsed time: 20.1 minutes

create index ratio_idx1 on sample (ratio(balance, limit));

select  count(*) from  sample where ratio(balance,limit) >.5;
Elapsed time: 7 seconds!!!


5.7  分区索引
分区索引就是简单地把一个索引分成多个片断。通过把一个索引分成多个片断，可以访问更小的片断(也更快)，并且可以把这些片断分别存放在不同的磁盘驱动器上(避免I/O问题)。B树和位图索引都可以被分区，而HASH索引不可以被分区。可以有好几种分区方法：表被分区而索引未被分区；表未被分区而索引被分区；表和索引都被分区。不管采用哪种方法，都必须使用基于成本的优化器。分区能够提供更多可以提高性能和可维护性的可能性

有两种类型的分区索引：本地分区索引和全局分区索引。每个类型都有两个子类型，有前缀索引和无前缀索引。表各列上的索引可以有各种类型索引的组合。如果使用了位图索引，就必须是本地索引。把索引分区最主要的原因是可以减少所需读取的索引的大小，另外把分区放在不同的表空间中可以提高分区的可用性和可靠性。

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

5.7.1.本地分区索引(通常使用的索引)
可以使用与表相同的分区键和范围界限来对本地索引分区。每个本地索引的分区只包含了它所关联的表分区的键和ROWID。本地索引可以是B树或位图索引。如果是B树索引，它可以是唯一或不唯一的索引。
这种类型的索引支持分区独立性，这就意味着对于单独的分区，可以进行增加、截取、删除、分割、脱机等处理，而不用同时删除或重建索引。Oracle自动维护这些本地索引。本地索引分区还可以被单独重建，而其他分区不会受到影响。

5.7.1.1 有前缀的索引
有前缀的索引包含了来自分区键的键，并把它们作为索引的前导。例如，让我们再次回顾participant表。在创建该表后，使用survey_id和survey_date这两个列进行范围分区，然后在survey_id列上建立一个有前缀的本地索引，如下图所示。这个索引的所有分区都被等价划分，就是说索引的分区都使用表的相同范围界限来创建。
                 

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


5.7.1.2 无前缀的索引
无前缀的索引并没有把分区键的前导列作为索引的前导列。若使用有同样分区键(survey_id和survey_date)的相同分区表，建立在survey_date列上的索引就是一个本地的无前缀索引，如下图所示。可以在表的任一列上创建本地无前缀索引，但索引的每个分区只包含表的相应分区的键值。
                         

 

如果要把无前缀的索引设为唯一索引，这个索引就必须包含分区键的子集。在这个例子中，我们必须把包含survey和(或)survey_id的列进行组合(只要survey_id不是索引的第一列，它就是一个有前缀的索引)。

技巧：对于一个唯一的无前缀索引，它必须包含分区键的子集。


5.7.2. 全局分区索引
全局分区索引在一个索引分区中包含来自多个表分区的键。一个全局分区索引的分区键是分区表中不同的或指定一个范围的值。在创建全局分区索引时，必须定义分区键的范围和值。全局索引只能是B树索引。Oracle在默认情况下不会维护全局分区索引。如果一个分区被截取、增加、分割、删除等，就必须重建全局分区索引，除非在修改表时指定ALTER TABLE命令的UPDATE GLOBAL INDEXES子句。

5.7.2.1 有前缀的索引
通常，全局有前缀索引在底层表中没有经过对等分区。没有什么因素能限制索引的对等分区，但Oracle在生成查询计划或执行分区维护操作时，并不会充分利用对等分区。如果索引被对等分区，就必须把它创建为一个本地索引，这样Oracle可以维护这个索引，并使用它来删除不必要的分区，如下图所示。在该图的3个索引分区中，每个分区都包含指向多个表分区中行的索引条目。
         
                       


         分区的、全局有前缀索引

技巧：如果一个全局索引将被对等分区，就必须把它创建为一个本地索引，这样Oracle可以维护这个索引，并使用它来删除不必要的分区。

5.7.2.2 无前缀的索引
Oracle不支持无前缀的全局索引。


5.8  位图连接索引
位图连接索引是基于两个表的连接的位图索引，在数据仓库环境中使用这种索引改进连接维度表和事实表的查询的性能。创建位图连接索引时，标准方法是连接索引中常用的维度表和事实表。当用户在一次查询中结合查询事实表和维度表时，就不需要执行连接，因为在位图连接索引中已经有可用的连接结果。通过压缩位图连接索引中的ROWID进一步改进性能，并且减少访问数据所需的I/O数量。

创建位图连接索引时，指定涉及的两个表。相应的语法应该遵循如下模式：
create bitmap index FACT_DIM_COL_IDX on FACT(DIM.Descr_Col) from FACT, DIM
where FACT.JoinCol = DIM.JoinCol;

位图连接的语法比较特别，其中包含FROM子句和WHERE子句，并且引用两个单独的表。索引列通常是维度表中的描述列——就是说，如果维度是CUSTOMER，并且它的主键是CUSTOMER_ID，则通常索引Customer_Name这样的列。如果事实表名为SALES，可以使用如下的命令创建索引：
create bitmap index SALES_CUST_NAME_IDX

on  SALES(CUSTOMER.Customer_Name)  from SALES, CUSTOMER
where  SALES.Customer_ID=CUSTOMER.Customer_ID;

如果用户接下来使用指定Customer_Name列值的WHERE子句查询SALES和CUSTOMER表，优化器就可以使用位图连接索引快速返回匹配连接条件和Customer_Name条件的行。

位图连接索引的使用一般会受到限制：

 

• 只可以索引维度表中的列。
• 用于连接的列必须是维度表中的主键或唯一约束；如果是复合主键，则必须使用连接中的每一列。
• 不可以对索引组织表创建位图连接索引，并且适用于常规位图索引的限制也适用于位图连接索引。