Oracle优化-建立索引(三)

什么是索引，比作字典的目录。查字典的时候，先查找部首几个笔画，然后找到部首，然后找到字的页码。不是在目录中每个笔画和部首都找一遍才能找到字的页码。在计算机上索引最常见的就是树状结构，后面有介绍。使用了索引就是一句话杠杠的快。

建立索引方法：

1、普通索引 频繁使用的关联字段，条件字段需要建立普通索引。普通索引字段可以为空null、重复。

CREATE INDEX 索引名 ON 表名 (列名) 
TABLESPACE 表空间名;--默认表空间此行可以省略

2、位图索引 比如一个表有10000行数据，一列只有100个取值，100/10000 =基数为1%。Oracle推荐，当一个列的基数小于1%时，适合建立位图索引。各种分类字段适合建立位图索引，可以为null、重复，代码如下：

create bitmap index 索引名称 on 表名 (列名)
tablespace Tiger_TS;--默认表空间此行可以省略

3、唯一索引，当表中的数据不允许重复但允许为空null时，建立唯一索引，建立主键时，自动建立唯一索引

CREATE unique INDEX 索引名 ON 表名 (列名)
TABLESPACE 表空间名;--默认表空间此行可以省略

4、联合索引 某几个字段经常同时作为关联字段频繁使用时，可以建立联合索引；只可以一行为空（未测）还有联合唯一索引最好不用，要使用联合主键

CREATE INDEX 索引名 ON 表名 (列名1,列名2，列名3)
TABLESPACE 表空间名;--默认表空间此行可以省略

5、反向索引，海量序列字段时使用

CREATE INDEX 索引名 ON 表名 (列名)  reverse
TABLESPACE 表空间名;--默认表空间此行可以省略

6、删除索引 ，在索引多需要大量的insert操作时，可以先删除索引，然后建立索引，提高效率。

drop index 索引名称；

7、重建索引 ，频繁大量update，delete时可以选择重建索引。比先drop 然后create要快。

alter index 索引名称 rebuild

8、函数索引：需要时现查，几乎不适用

9、分区索引：在分区表中描述。

是否生效需要看执行计划，里面是否含有index内容。查看oracle调优（一）

索引失效情况：

1、隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误. 由于表的字段tu_mdn定义为varchar2(20),

但在查询时把该字段作为number类型以where条件传给Oracle,这样会导致索引失效.

错误的例子：select * from test where tu_mdn=13333333333;
正确的例子：select * from test where tu_mdn='13333333333';

2、对索引列进行运算导致索引失效,我所指的对索引列进行运算包括(+，-，*，/，<,>,<>, 等)但是<=,>=索引有效

错误的例子：select * from test where id-1=9;
正确的例子：select * from test where id=10;

3、使用Oracle内部函数导致索引失效.对于这样情况应当创建基于函数的索引.

错误的例子：select * from test where round(id)=10;

说明，此时id的索引已经不起作用了 正确的例子：首先建立函数索引，

create index test_id_fbi_idx on test(round(id));
然后 select * from test where round(id)=10; 这时函数索引起作用了 1,<> 2,单独的>,<,(有时会用到，有时不会)

4、like "%_" 百分号在前.

5、单独引用复合索引里非第一位置的索引列.在oracle18c中是可以使用的。在执行计划中第一列使用的索引是index，第二列单独使用时index skip 。

6、not in ,not exist。in走索引

7、有时都考虑到了 但就是不走索引,drop了从建试试在

8、B-tree索引 is null，is not null不会走,位图索引 is null,is not null 都会走，位图未测。

下面拷贝别人的文章，帮助理解索引。

一.索引概述

ROWID是ORACLE中的一个重要的概念。用于定位数据库中一条记录的一个相对唯一地址值。通常情况下，该值在该行数据插入到数据库表时即被确定且唯一。ROWID它是一个伪列，它并不实际存在于表中。它是ORACLE在读取表中数据行时，根据每一行数据的物理地址信息编码而成的一个伪列。所以根据一行数据的ROWID能找到一行数据的物理地址信息。从而快速地定位到数据行。数据库的大多数操作都是通过ROWID来完成的，而且使用ROWID来进行单记录定位速度是最快的。

Oracle作为关系型数据库，用户查找数据与行的物理位置无关，表中的每一行均用一个ROWID来标识，当Oracle数据库中存储海量的记录时，就意味着有大量的ROWID标识，那么Oracle如何能快速找到指定的ROWID呢？这时就需要索引对象了。

如果一个表中存有海量的数据记录，当对该表进行指定条件的查询时，常规的查询方法会将所有的记录都读取出来，一一与查询条件进行比较，最后返回满足条件的记录，这样操作会导致时间和I/O开销都非常大，我们可以通过建立索引来减少开销，有了索引之后，只需要在索引中找到符合查询条件的索引值字段，就可以通过保存在索引中的ROWID快速找到表中对应的记录。

Oracle不仅要在数据字典中保存索引的定义，还需要在表空间中为它分配实际的存储空间。创建索引时，Oracle会自动在默认表空间或指定表空间中创建一个索引段。（因为Oracle能并行读取不同硬盘的数据，所以将索引和表放在不同硬盘的不同表空间中能够提高查询的速度，避免产生I/O冲突）

建立索引需要注意的几点：

• 索引应该建立在where语句频繁引用的表的列上。
• 索引应该建立在经常需要基于某列或某几列排序的表的列上。
• 一个表中的索引不是越多越好。索引主要用于加快查询速度，但索引越多会导致DML（insert ，update，delete）的速度越慢，所以索引的个数需要你好好权衡一下。

指定索引块空间的使用参数。基于表建立索引时，Oracle 会将相应表的列数据添加到索引块中。为索引块添加数据时，Oracle会按照PCTFREE参数在索引块中预留部分空间，该预留空间是为将来的INSERT操作准备的。如果将来在表上执行大量INSERT操作，那么应该在建立索引时设置较大的PCTFREE。

将表和索引部署到相同的表空间中，可以简化表空间的管理。将表和索引部署到不同的表空间中，可以提高访问性能。

当在大表上建立索引时，使用NOLOGGING选项可以最小化重做记录。使用NOLOGGING选项可以节省重做日志空间、降低索引建立时间、提高索引并行建立的性能。

最好不要在小表上建立索引，杀鸡何须宰牛刀？

多表连接时应在连接列上建立索引。

二.建立索引

Oracle创建索引的流程是首先对将要建立索引的字段进行排序，然后将排序后的字段值和对应记录ROWID存储在索引段中。Oracle索引也分几个类型，按照索引数据的存储方式可以分为B树索引、位图索引、反向健索引和基f函数的案引:按照索引列的唯性又可以分为唯有和非唯索引:按照索引列的个数又可以分为单列索引和复合索引，下面我就分类讲解一下各个索引。

1.B树索引

B树索引是Oracle默认的索引类型，它以B树结构组织并存储索引数据，不懂什么是B树的可以百度一下，你就知道。B树索引中的数据是以升序方式排序的，B树索引由根块、分支块和叶块组成，其中主要数据都集中在叶子节点上。

Oracle采用这种方式的索引，可以确保无论索引条目在何处，只需要花费相同的I/O即可获取它，所以它被称为B（Balanced）树索引。如果在Where子句中要经常引用某列或某几列，应该基于这些列建立B树索引。代码如下：

create index tiger_index on tigerlearn(tiger)
pctfree 25 --可以默认不写
tablespace Tiger_TS;

为tigerlearn表的tiger列创建索引tiger_index，子句pctfree指定为将来insert操作所预留的空闲空间，子句tablespace用于指定索引段所在的表空间。假设表已经包含了大量数据，那么在建立索引时应该仔细规划pctfree的值，以便为以后的insert操作预留空间。

2.位图索引

在B树索引中，通过在索引中保存排序的索引列的值以及记录物理地址ROWID来实现快速查找，但对于一些特殊的表，B树索引效率较低，比如一个表中有一列，数据只有正确、错误两个取值，如果建立B树索引，使用该索引对表进行检索时，将返回近一半的数据，效率非常低下，这种情况我们定义一个变量“基数”，比如一个表有10000行数据，一列只有100个取值，基数为1%。Oracle推荐，当一个列的基数小于1%时，适合建立位图索引。代码如下：

create bitmap index tiger_index_bitmap on tigerlearn(tiger)
tablespace Tiger_TS;

初始化参数CREATE_BITMAP_AREA_SIZE用于指定建立位图索引时分配的位图区大小，默认值为8MB，该参数值越大建立位图索引的速度就越快。修改该参数的代码如下：

alter system set create_bitmap_are_size = 8388608
scope = spfile

3.反向键索引

Oracle会自动为表的主键创建B树索引，通常主键会是一个递增的序列编号，如果使用默认的B树索引，当数据量巨大时会导致索引数据分布不均，叶子节点可能会偏向某一个方向，这时就需要另一个索引机制，反向键索引，它可以将添加的数据随机分散到索引中，它在顺序递增列上建立索引时非常有用。代码如下：

create bitmap index tiger_index_reverse on tigerlearn(tiger) reverse
tablespace Tiger_TS;

果在WHERE语句中，需要对索引列的值进行范围性的搜索，如BETWEEN、<、>等，其反向键索引无法使用，此时，Oracle将执行全表扫描；只有对反向键索引列进行 <>和 = 的比较操作时，其反向键索引才会得到使用。

4.基于函数的索引

基于函数的索引是常规的B树索引，但它存储的数据是由表中的数据应用函数后所得到的，而不是直接存储表中的数据本身。下面是基于lower函数建立的索引。代码如下：

create index tiger_index_fun on tigerlearn(lower(tiger));

创建这个函数索引后，如果在查询条件中包含相同的lower函数，则会提高查询的执行效率，如果没有创建函数索引，而是创建了普通的B树索引，在查询中使用lower函数会执行全表进行搜索，因为lower数据不在表中也不在索引中。

三.索引的其他操作

1.修改索引

对一个表建立索引后，对表的DML操作不断增加，索引中会产生越来越多的存储碎片，会拉低索引的工作效率，可以采用两种方式来清除碎片，重建索引或合并索引，重建索引就是简单的新建一个索引，然后把原来的索引删除。

合并索引代码：

alter index tiger_index coalesce deallocate unused;

重建索引可重新指定索引对象的表空间，代码如下：

alter index tiger_index rebulid

2.删除索引

删除索引的方式与索引的创建方式有关，如果使用create index语句显式地创建该索引，则可以用drop index语句删除该索引。

drop index tiger_index

如果索引是在定义约束时由Oracle自动创建的，则必须禁用或删除该约束本身。

3.显示索引信息

通过查询数据字典视图dba_indexes,可以显示数据库中的所有索引;

通过查询数据字典视图all_indexes,可以显示当前用户可访问地所有索引;

通过查询数据字典视图user_indexes,可以显示当前用户地索引信息。

通过查询数据字典视图dba_ind_columns,可以显示所有索引地表列信息；

通过查询数据字典视图all_ind_columns,可以显示当前用户可访问地所有索引地表列信息；

通过查询数据字典视图user_ind_columns,可以显示当前用户索引地表列信息。

例：

select index_name,index_type
from dba_indexes
where owner = 'tiger'