索引

索引

在oracle中，索引是除表之外另一个重要的模式对象，索引主要用于提高表查询速度。索引与表一样，有独立的数据段存储，并且可以通过设置储存的参数，控制索引段的盘区分配方式。索引可以由用户显示创建，也可以有oracle自动创建。

Ø        Oracle数据库的体系结构

1.      物理存储结构

2.      逻辑存储结构

3.      内存结构

4.      实力进程结构

一、            了解索引

常用的索引类型有B树索引、反向键索引、位图索引、基于函数的索引、簇索引、全局和局部索引等。其中，簇索引专门用于簇的索引

Oracle支持多种类型的索引，可以按列的多少、索引值是否唯一和索引数据的组织形式对索引进行分类，以满足各种表和查询条件的要求。

1.      单列索引和复合索引

     一个索引可以由一个或多个列组成。

        基于单个列所创建的索引称为单列索引，基于两列或多列所创建的索引称为多列索引。

2.      B树索引

        B树索引是Oracle数据库中最常用的一种索引。当使用CREATE INDEX语句创建索引时，默认创建的索引就是B树索引。

        B树索引是按B树结构或使用B树算法组织并存储索引数据的。B树索引就是一棵二叉树，它由根、分支节点和叶子节点三部分构成。其中，根包含指向分支节点的信息，分支节点包含指向下级分支节点和指向叶子节点的信息，叶子节点包含索引列和指向表中每个匹配行的ROWID值。叶子节点是一个双向链表，因此可以对其进行任何方面的范围扫描。

          B树索引中所有叶子节点都具有相同的深度，所以不管查询条件如何，查询速度基本相同。另外，B树索引能够适应各种查询条件，包括精确查询、模糊查询和比较查询。

    B树索引的分类如下所示：

1)      Unique：唯一索引，其索引值不能重复，但允许为NULL。在创建索引时指定UNIQUE关键字可以创建唯一索引。当建立“主键约束条件”时Oracle会自动在相应列上建立唯一索引，主键列不允许为NULL。

2)      Non-Unique：非唯一索引，其索引值可以重复，允许为NULL。默认情况下，Oracle创建的索引是非唯一索引。

3)      Reverse Key：反向关键字索引。通过在创建索引时指定“REVERSE”关键字，可以创建反向关键字索引，被索引的每个数据列中的数据都是反向存储的，但仍然保持原来数据列的次序。   

    对建立了B树索引的表进行查询时，只需要读取4次数据：

1)      第一次读取根节点

2)      第二次读取分支节点

3)      第三次读取叶子节点

4)      最后一次用于从表中获取相关的数据

3.      位图索引

        在B树索引中，保存的是经排序过的索引列及其对应的ROWID值。但是对于一些基数很小的列来说，这样做并不能显著提高查询的速度。所谓基数，是指某个列可能拥有的不重复值的个数。比如性别列的基数为2（只有男和女）。

        因此，对于象性别、婚姻状况、政治面貌等只具有几个固定值的字段而言，如果要建立索引，应该建立位图索引，而不是默认的B树索引。

        当创建位图索引时，Oracle会扫描整张表，并为索引列的每个取值建立一个位图。在这个位图中，对表中每一行使用一位（bit，取值为0或1）来表示该行是否包含该位图的索引列的取值，如果为1，则表示该位对应的ROWID所在的记录包含该位图索引列值。最后通过位图索引中的映射函数完成位到行的ROWID的转换。 

4.       反向键索引

在oracle中，系统会自动为表的主键列建立索引，这个默认的索引是普通的B树索引。对于主键值是按顺序添加的，默认的B树索引并不理想，反向键是一种特殊的类型的B树索引，在索引基于含有序数的列时非常有用。

反向键索引的工作原理：在存储结构方面，它与常规的B树索引相同，

   然而，如果用户使用序列编号在表中输入新纪录，则反向键索引首先反向每个列键值字节，然后在反向后的新数据上进行索引。

5.      函数索引

前面的索引都是直接对表中的列创建索引，除此之外，Oracle还可以对包含有列的函数或表达式创建索引，这就是函数索引。

        当需要经常访问一些函数或表达式时，可以将其存储在索引中，当下次访问时，由于该值已经计算出来了，因此，可以大大提高那些在WHERE子句中包含该函数或表达式的查询操作的速度。

         函数索引既可以使用B树索引，也可以使用位图索引，可以根据函数或表达式的结果的基数大小来进行选择，当函数或表达式的结果不确定时采用B树索引，当函数或表达式的结果是固定的几个值时采用位图索引。 

下面通过一个例子看看函数索引的用法。在SALES表中，TOPIC列的值如果采用首字母大写的方式存储。

 

ID	TOPIC	ISLOOK	ROWID
T0001	Book	Y	AAAHagAABAAAMZKAAA
T0203	Pen	Y	AAAHagAABAAAMZKAAB
T1437	Tee	N	AAAHagAABAAAMZKAAC
T1682	Cup	Y	AAAHagAABAAAMZKAAD
T2735	Hat	N	AAAHagAABAAAMZKAAE
T3412	Apple	N	AAAHagAABAAAMZKAAF
T4724	Wine	Y	AAAHagAABAAAMZKAAG

 

现在使用下列代码查询：

SELECT * FROM SALES WHERE TOPIC=’TEE’;

将没有结果。现在忽略大小写，将代码修改如下：

SELECT * FROM SALES WHERE UPPER(TOPIC)=’TEE’;

这样可以查到相应的结果，但是，由于不是直接查询TOPIC列，所以，即使在TOPIC列上创建了索引也无法使用。

这时，就可以使用函数索引，创建函数索引的代码如下：

CREATE INDEX funidx_upper_topic ON SALES(UPPER(TOPIC));

由于函数索引存储了预先计算过的值，因此，查询时不需要对每条记录都再计算一次WHERE条件，从而可以提高查询的速度。

在函数索引中可以使用各种算术运算符、PL/SQL函数和内置SQL函数，如LEN、TRIM、SUBSTR等。这些函数的共同特点是为每行返回独立的结果，因此，象集函数（如SUM、MAX、MIN、AVG等）不能使用。

6.      全局索引和局部索引

通常在对表进行分区时，也会将对应的索引进行分区，但是分区的表也可以有未分区的索引，而未分区的表可以有分区的索引。对索引分区的目的与对表进行分区相同，都是为了更加易于管理和维护。

在oracle中，一共可以为分区表建立3种类型的索引：局部分区索引、全局分区索引、全局非分区索引。

1)      局部分区索引

局部分区索引是为分区表中的各个分区单独地建立分区，各个索引分区之间是相互独立的。

    局部分区索引相对比较简单，也比较容易管理

2)      全局分区索引

    全局分区管理是对整个分区建立的索引，然后再由oracle对索引进行分区。全局分区索引的各个分区不是相互独立的，索引分区与分区表之间不是简单的一对一的关系

3)      全局非分区索引

        全局非分区就是对整个分区表建立索引，但是未对索引进行分区。局部分区索引的管理大部分由oracle自动完成，而全局分区索引的部分管理操作比较特殊，需要DBA进行更多的干预

二、            管理索引的原则

使用索引的目的是为了提高系统的效率，但同时它也会增加系统的负担，进行影响系统的性能，因为系统必须在进行DML操作后维护索引数据。

在新的SQL标准中并不推荐使用索引，而是建议在创建表的时候用主键替代。因此，为了防止使用索引后反而降低系统的性能，应该遵循一些基本的原则。

使用索引应该遵循以下一些基本的原则:

1.      小表不需要建立索引。

2.      对于大表而言，如果经常查询的记录数目少于表中总记录数目的15%时，可以创建索引。这个比例并不绝对，它与全表扫描速度成反比。

3.      对于大部分列值不重复的列可建立索引。

4.      对于基数大的列，适合建立B树索引，而对于基数小的列适合建立位图索引。

5.      对于列中有许多空值，但经常查询所有的非空值记录的列，应该建立索引。

6.      LONG和LONG RAW列不能创建索引。

备注：

 

字符类

LONG:可变长的字符串数据，最长2G，LONG具有VARCHAR2列的特性，可以存储长文本一个表中最多一个LONG列

 

二进制类

       LONG　RAW:可变长二进制数据，最长2G

 

LONG使用说明：

1)      LONG 数据类型中存储的是可变长字符串，最大长度限制是2GB。

2)      对于超出一定长度的文本，基本只能用LONG类型来存储，数据字典中很多对象的定义就是用LONG来存储的。

3)      LONG类型主要用于不需要作字符串搜索的长串数据，如果要进行字符搜索就要用varchar2类型。

4)      很多工具，包括SQL*Plus，处理LONG 数据类型都是很困难的。

5)      LONG 数据类型的使用中，要受限于磁盘的大小。

能够操作 LONG 的 SQL 语句：

 

1)      Select语句

2)      Update语句中的SET语句

3)      Insert语句中的VALUES语句

限制：

1)      一个表中只能包含一个 LONG 类型的列。

2)      不能索引LONG类型列。

3)      不能将含有LONG类型列的表作聚簇。

4)      不能在SQL*Plus中将LONG类型列的数值插入到另一个表格中,如insert into ...select。

5)      不能在SQL*Plus中通过查询其他表的方式来创建LONG类型列,如create table as select。

6)      不能对LONG类型列加约束条件（NULL、NOT NULL、DEFAULT除外），如：关键字列(PRIMARY KEY)不能是 LONG 数据类型。

7)      LONG类型列不能用在Select的以下子句中：where、group by、order by，以及带有distinct的select语句中。

8)      LONG类型列不能用于分布查询。

9)      PL/SQL过程块的变量不能定义为LONG类型。

10)  LONG类型列不能被SQL函数所改变，如：substr、instr。

SQL*Plus 中操作LONG类型列：

1)      set long n// n代表n位字符（n为大于零的整数）

2)      col 列名 format An

//An表示将此列的数据显示宽度限制为不超过n位。

7.      经常进行连接查询的列上应该创建索引。

8.      在使用CREATE INDEX语句创建查询时，将最常查询的列放在其他列前面。

9.      维护索引需要开销，特别是对表进行插入和删除操作时，因此要限制表中索引的数量。对于主要用于读的表，则索引多就有好处，但是，一个表如果经常被更改，则索引应少点。

10.  在表中插入数据后创建索引。如果在装载数据之前创建了索引，那么当插入每行时，Oracle都必须更改每个索引。

三、            创建索引

     创建索引使用CREATE INDEX语句。

     在用户自己的方案中创建索引，需要CREATE INDEX系统权限，在其他用户的方案中创建索引则需要CREATE ANY INDEX系统权限。另外，索引需要存储空间，因此，还必须在保存索引的表空间中有配额，或者具有UNLIMITED TABLESPACE系统权限。

 CREATE INDEX语句的语法如下：

CREATE [UNIQUE] | [BITMAP] INDEX index_name

ON table_name([column1 [ASC|DESC],column2

[ASC|DESC],…] | [express])

[TABLESPACE tablespace_name]

[PCTFREE n1]

[STORAGE (INITIAL n2)]

[NOLOGGING]

[NOLINE]

[NOSORT];

其中：

UNIQUE：表示唯一索引，默认情况下，不使用该选项。

BITMAP：表示创建位图索引，默认情况下，不使用该选项。

PCTFREE：指定索引在数据块中的空闲空间。对于经常插入数据的表，应该为表中索引指定一个较大的空闲空间。

NOLOGGING：表示在创建索引的过程中不产生任何重做日志信息。默认情况下，不使用该选项。

ONLINE：表示在创建或重建索引时，允许对表进行DML操作。默认情况下，不使用该选项。

NOSORT：默认情况下，不使用该选项。则Oracle在创建索引时对表中记录进行排序。如果表中数据已经是按该索引顺序排列的，则可以使用该选项。

 

备注：

可以在一个表上创建多个索引，但这些索引的列的组合必须不同。如下列的索引是合法的。

  CREATE INDEX idx1 ON SALES(ID,TOPIC)

  CREATE INDEX idx2 ON SALES(TOPIC,ID)

其中，idx1和idx2索引都使用了ID和TOPIC列，但由于顺序不同，因此是合法的。

 

1.      创建B树索引

B树索引是Oracle默认的索引类型，当在WHERE子句中经常要引用某些列时，应该在这些列上创建索引。例如，经常需要在SALES表的TOPIC列上按标题查询，就可以在TOPIC列上建立B树索引。

如果用户要在自己的模式中创建索引，则必须有create Index系统权限，如果要在其他用户模式下创建索引，则必须有create any index

默认情况下，当用户为表定义一个主键时，系统将自动为该列创建一个B树索引，因此用户不能再为主键创建B树索引

//在“商品信息”表的“供应商编号”列创建一个名为MERCH_PURVEY_INDEX的索引

Create index merch_purvey_index on 商品信息（供应商编号）tablespace users;

On关键字后指定索引基于的表和列名

Tablespace指定存储索引的表空间storage子句为索引指定存储参数

//为“商品信息”表的“商品名称”列创建唯一的索引：

Create unique index merch_uniqne_index on 商品信息（商品名称）;

//复合索引

Create unique index merch_uniqne_index on 商品信息（供应商编号，产地）;

Create unique index merch_uniqne_index on 商品信息（产地，供应商编号）;

备注：顺序不同索引不同

//对索引列进行压缩

Create unique index merch_uniqne_index on 商品信息（供应商编号，产地）compress 2;

2.      创建位图索引（关键字：bitmap）

位图索引适合于那些基数较少，且经常对该列进行查询、统计的列。

//在商品信息表的供应商编号和产地列上创建位图索引merch_bitmap_index:

Create bitmap index merch_bitmap_index on 商品信息(供应商编号，产地) tablespace users;

3.      创建反向键索引（关键字：REVERSE）

//为商品信息表的商品编号列创建反向键索引

Create index merch_peverse_index on 商品信息（商品编号）peverse 30 tablespace users

4.      创建函数索引

        使用函数索引可以提高在查询条件中使用函数和表达式的查询语句的执行速度。

        Oracle在创建函数索引时，首先对包含索引列的函数值或表达式进行求值，然后将排序后的结果存储到索引中。函数索引可以根据基数的大小，选择使用B树索引或位图索引。

在SALES表中，TOPIC列的值可能是大写形式、小写形式或首字母大写的方式存储。因此在按TOPIC值查询时可以采取忽略大小写的方式，其代码如下：

    SELECT * FROM SALES WHERE UPPER(TOPIC)=’TEE’;

    但是，由于不是直接查询TOPIC列，所以，即使在TOPIC列上创建了索引也无法使用。这样就需要使用函数索引。

    //在“vendition入库单信息“表的”入库日期“列上创建一个基于函数to_char()的函数索引：

Create index function_index on 入库单信息(to_char(入库日期，’yyyy-mm-dd’)) tablespace users;

//查询将使用function_index索引

Select * from 入库单信息 where To_char(入库信息，’yyyy-mm-dd’)=’2005-11-27’;

四、            修改索引

1.      重命名索引

 

当需要修改已创建的索引时，可以使用ALTER INDEX语句。

用户想要修改自己方案中的索引，需要具有ALTER INDEX系统权限，如果想要修改其他用户方案中的索引，则需要具有ALTER ANY INDEX系统权限。

重命名索引可以使用ALTER INDEX语句。例如，将上节中为SALES表创建的索引“标题索引”改名为“标题B树索引”，代码如下。

ALTER INDEX "SCOTT"."标题索引" RENAME TO "标题B树索引";

在“SQL Plus Worksheet”中执行以上SQL代码，结果：索引已更改

 

2.      合并索引

表在使用一段时间后，由于用户不断对其进行更新操作，而每次对表的更新必然伴随着索引的改变，因此，在索引中会产生大量的碎片，从而降低索引的使用效率。

 

有两种方法可以清理碎片：合并索引和重建索引。

 

合并索引就是将B树叶子节点中的存储碎片合并在一起，从而提高存取效率，但这种合并并不会改变索引的物理组织结构。

 

使用如下代码对SALES表的“标题索引”进行合并。

       ALTER INDEX "SCOTT"."标题索引" COALESCE;

      在“SQL Plus Worksheet”中执行以上SQL代码，结果：索引已更改

3.     重建索引

当表中一个已编制索引的值被更新后，旧值会从索引中删除，新值将被插入索引的另一个部分。旧值释放的空间将不能被再次使用。随着更新或删除索引值的增多，索引中不可用空间的量也在增加，这种情况称为索引滞留。由于滞留索引中的数据和空闲区混在一起，查看索引的效率便会降低。因此，如果在索引列上频繁进行UPDATE和DELETE操作，为了提高空间的利用率，应该定期重建索引。

    重建索引相当于删除原来的索引，然后再创建一个新的索引，因此，CREAT INDEX语句中的选项同样适用于重建索引。

重建索引使用ALTER INDEX语句的REBUILD选项。

   例如，使用如下代码重建SALES表的“标题索引”。

      ALTER INDEX "SCOTT"."标题索引" REBUILD;

在“SQL Plus Worksheet”中执行以上SQL代码，结果：索引已更改

合并索引和重建索引都能消除索引碎片，但两者在使用上有明显的区别。

   合并索引不能将索引移动到其他表空间，但重建索引可以；

   合并索引代价较低，无需额外存储空间，但重建索引恰恰相反；

    合并索引只能在B树的同一子树中合并，不改变树的高度，但重建索引重建整个B树，可能会降低树的高度。

 

4.     删除索引

 

当以下情况发生时，需要删除索引：

–    不需要该索引时。

–    当索引中包含损坏的数据块，或碎片过多时，应删除该索引，然后再重建。

–    如果移动了表的数据，将导致索引无效，此时应删除该索引，然后再重建。

–    当向表中装载大量数据时，Oracle也会向索引增加数据，为了加快装载速度，可以在装载之前删除索引，在装载完毕后重新创建索引。

    删除索引使用DROP INDEX语句。要删除用户自己方案中的索引，需要具有DROP INDEX系统权限，而要删除其他用户方案中的索引，则需要具有DROP ANY INDEX系统权限。

在“SQL Plus Worksheet”中执行以下SQL代码，也可以删除索引，如图所示。

   DROP INDEX "SCOTT"."标题索引";

结果：索引已丢弃

 

5.     监视索引

索引在创建后并不一定就会被使用，Oracle会在自动搜集了表和索引的统计信息之后，决定是否要使用索引。

       通过查询数据字典视图V$OBJECT_USAGE可以查看索引的使用情况。

1．在“SQL Plus Worksheet”中执行DESC表语句可以查看表视图的结构，如表所示。

2．查询V$OBJECT_USAGE，了解索引的使用情况。在“SQL Plus Worksheet”中执行以下代码，结果：未选定行

SELECT index_name,monitoring,used,start_monitoring,end_monitoring

FROM V$OBJECT_USAGE;

3．要监视索引，需要先将索引设置为被监视状态。在“SQL Plus Worksheet”中执行以下代码，结果：索引被更改。

ALTER INDEX "SCOTT"."标题索引" MONITORING USAGE;

4．现在，在“SQL Plus Worksheet”中执行以下代码查询V$OBJECT_USAGE可以发现“标题索引”的MON列为YES，表明已经处于被监视状态，但USE列为NO表明从开始监视（START_MONITORING）以来还没有被使用。

SELECT index_name,monitoring,used,start_monitoring,end_monitoring

FROM V$OBJECT_USAGE WHERE index_name='标题索引';

5．在“SQL Plus Worksheet”中执行以下代码查询SALES表。

SELECT ID,TOPIC,ISLOOK FROM "SCOTT"."SALES“

WHERE "TOPIC"='TEE';

6．在“SQL Plus Worksheet”中执行以下代码查询V$OBJECT_USAGE可以发现“标题索引”的USE列为YES，表明刚才的查询已经使用了“标题索引”。

7．下面在“SQL Plus Worksheet”中执行以下代码关闭该索引的监视状态。

ALTER INDEX "SCOTT"."标题索引" NOMONITORING USAGE;

8．在“SQL Plus Worksheet”中执行以下代码查询V$OBJECT_USAGE可以发现“标题索引”的END_MONITORING列被设置了时间，表明“标题索引”已经关闭了监视状态。

SELECT index_name,monitoring,used,start_monitoring,end_monitoring

FROM V$OBJECT_USAGE WHERE index_name='标题索引';