Oracle——索引

简介：

索引是关系数据库中用于存放每一条记录的一种对象，主要目的是加快数据的读取速度和完整性检查。建立索引是一项技术性要求高的工作。一般在数据库设计阶段的与数据库结构一道考虑。应用系统的性能直接与索引的合理直接有关。下面给出建立索引的方法和要点。

特点

1. 大大加快检索数据的速度
2. 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
3. 加速表与表之间的连接
4. 查询的过程中，使用索引，使用优化隐藏器，从而提高系统的性能
5. 只有唯一索引才真正提高速度,一般的索引只能提高30%左右

   不足：

1. 创建和维护索引，比较耗费时间，随着数据量的增大而增大
2. 创建索引，占一定的物理空间（聚簇索引，占用空间会更大）
3. 在对表进行增删改时需要更新索引，因此索引对增删改时会有负面影响
4. 索引主要用于加快查询速度，但会降低DML操作的速度。索引越多，DML操作速度越慢，尤其会极大地影响INSERT和DELETE操作的速度。因此，规划索引时，必须仔细权衡查询和DML的需求。

适用性

比较适合建立索引的列的特点

1. 经常需要搜索的列上
2. 主键，一般建立唯一性索引，保持数据的唯一性
3. 外键，提高表与表之间连接的速度
4. 需要排序的列上

不适合建立索引的列的特点

1. 很少进行搜索的列上
2. blob类型的列上
3. 修改频率比较高的列上

分类：

索引按存储方法分类，可以分为2类：B*树索引和位图索引：

1. B*树索引                                                                                                                                                                                                B*树索引的存储结构类似书的索引结构，有分支和叶两种类型的存储数据块，分支块相当于书的大目录，叶块相当于索引到的具体的书页。Oracle用B*树机制存储索引条目，以保证用最短路径访问键值。默认情况下大多使用B*树索引，该索引就是通常所见的唯一索引、逆序索引。
2. 位图索引（Bitmap）                                                                                                                                                                              相对于B*Tree索引,位图索引由于只存储键值的起止Rowid和位图,占用的空间非常少.位图索引存储主要用于节省空间，减少oracle对数据块的访问。它采用位图偏移方式来与表的行ID号对应，采用位图索引一般是重复值太多的表字段(如性别)。非常适合OR操作符的查询。位图索引之所以在实际密集型OLTP（联机事物处理）中用的比较少，是因为OLTP会对表进行大量的删除、修改、新建操作，Oracle每次进行操作都会对要操作的数据块加锁，以防止多人操作，容易产生的数据库锁等待甚至死锁现象。在OLAP（联机分析处理）中应用位图有优势，因为OLAP中大部分是对数据库的查询操作，而且一般采用数据仓库技术，所以大量数据采用位图索引节省空间比较明显。当创建表的命令中包含有唯一性关键字时，不能创建位图索引，创建全局分区索引时也不能用位图索引。

索引使用

1.创建索引

CREATE [unique|bitmap] INDEX 索引名 
ON 基表名 (column_name [ASC | DESC] [,column_name[ASC | DESC] ] ...,[column_expression])|CLUSTER [scheam.]cluster_name
[TABLESPACE tablespace_name]
[INITRANS n][MAXTRANS n][PCTFREE n]
[STORAGE storage]
[logging|nologging]
[nosort|reverse]

1)unique|bitmap : unique表示唯一值索引，bitmap表示位图索引，为空则默认为B-tree索引
2)column_name [ASC | DESC]：可以单列索引，也可以多列进行复合索引。缺省为ASC即升序排序。column_expression为行表达式
3)CLUSTER 指定一个聚簇（Hash cluster不能建索引）
4)Tablespace：制定存放索引的表空间（当表和索引在不同的表空间的时候，效率更高）
5)INITRANS、MAXTRANS 指定初始和最大事务入口数
6)STORAGE 存储参数，同create table 中的storage.
7)PCTFREE 索引数据块空闲空间的百分比(不能指定pctused)指定索引块空间的使用参数。基于表建立索引时，Oracle会将相应表列数据添加到索引块。为索引块添加数据时，Oracle会按照PCTFREE参数在索引块上预留部分空间，该预留空间是为将来的INSERT操作准备的。如果将来在表上执行大量INSERT操作，那么应该在建立索引时设置较大的PCTFREE。
8)nosort|reverse : nosort表示与表中相同的顺序进行创建索引，reverse表示使用与表中相反的顺序进行创建索引
9)logging|nologging : 是否对索引产生redolog（对于大表来说，可以设置为nologging从而来减少空间占用，提高效率）当在大表上建立索引时，使用NOLOGGING选项可以最小化重做记录。使用NOLOGGING选项可以节省重做日志空间、降低索引建立时间、提高索引并行建立的性能。
10)一个索引最多有16列，long列（可变长字符串数据，最长2G）、long raw（可变长二进制数据，最长2G）列不能建索引列

--增加索引
create index idx_emp_dep on emp(dep);
-- Create/Recreate indexes 
create index IDX_EMP_DEP on EMP (DEP)
  tablespace LYTP
  pctfree 10
  initrans 2
  maxtrans 255;

2.查看索引

--查看索引信息  
select * from user_indexes;
--查看索引被索引的字段信息  
select * from user_ind_columns;

1)选择性user_indexes中distinct_keys,选择性越高，那么索引返回的值就越少。
2)群集因子user_indexes中的clustering factor 越接近 leaf block的值的话，说明表中的数据越有序。
3)二元高度dba_indexes 的 Blevel列查看对应索引的二元高度，二元高度随着表的大小以及被索引的列中值的范围的狭窄程度而变化。重建索引可以降低二元高度。

3.修改索引

1）重命名索引

alter index index_sno rename to bitmap_index;

2) 合并索引（表使用一段时间后在索引中会产生碎片，此时索引效率会降低，可以选择重建索引或者合并索引,合并索引方式更好些，无需额外存储空间，代价较低）

alter index index_sno coalesce;

3)重建索引：

ALTER [UNIQUE] INDEX 索引名[INITRANS n][MAXTRANS n] REBUILD [STORAGE n]

其中：REBUILD 是 根据原来的索引结构重新建立索引，实际是删除原来的索引后再重新建立。

4.删除索引

当不需要时可以将索引删除以释放出硬盘空间。命令如下：

DROP INDEX 索引名

注意：当表结构被删除时，有其相关的所有索引也随之被删除。

索引使用原则

1. 在表中插入数据后创建索引。在表中插入数据后，创建索引效率将更高。如果在装载数据之前创建索引，那么插入每行时oracle都必须更改索引。
2. 索引正确的表和列。如果经常检索包含大量数据的表中小于15%的行，就需要创建索引。为了改善多个表的相互关系，常常使用索引列进行关系连接。
3. 主键和唯一关键字所在的列自动具有索引，但应该在与之关联的表中的外部关键字所在的列上创建索引。
4. 合理安排索引列。在createindex语句中，列的排序会影响查询的性能，通常将最常用的列放在前面。创建一个索引来提高多列的查询效率时，只有它的第一个列被where子句引用时，优化器才会使用该索引。
   例如：在A,B,C三列上创建索引
   　　A有效
   　　AB有效
   　　ABC有效
5. 限制表中索引的数量。尽管表可以有任意数量的索引，可是索引越多，在修改表中的数据时对索引做出相应更改的工作量也越大，效率也就越低。同样，目前不用的索引应该及时删除。
6. 指定索引数据块空间的使用。创建索引时，索引的数据块是用表中现存的值填充的，直到达到PCTFREE为止。如果打算将许多行插入到被索引的表中，PCTFREE就应设置得大一点，不能给索引指定PCTUSED。
7. 根据索引大小设置存储参数。创建索引之前应先估计索引的大小，以便更好地促进规划和管理磁盘空间。单个索引项的最大值大约是数据块大小的一半。

限制索引（建立了索引，但是无法使用）

1. 使用不等于<> 、 != ，（不等于操作符一定会进行全表扫描）
2. oracle碰到not会停止使用索引，而采用全表扫描

   select * from student where not (score=100);
   
   select * from student where score <> 100;
   
   --替换为
   
   select * from student where score>100 or score <100

     

3. 索引上使用空值比较将停止使用索引,B*Tree索引由于不记录空值,当基于is null的查询时,会使用全表扫描,使用is null 、 is not null （只要索引中出现一个null，那么这个索引就报废了。所以在建立索引的时候，一定要将准备建立索引的列设置为not null）

4. 通配符(%)在搜索词首出现时，oracle不能使用索引

5. 使用函数（where子句中含有trunc()、add_months()之类）的时候，sql优化器会自动忽略掉索引
   where子句中，进行了数据类型不匹配的比较，比如(where row_num = '1')的时候，SQL优化器会限制索引的使用

基于函数的索引

基于函数的索引是指索引中的一列或者多列是一个函数或者表达式，索引根据函数或表达式计算索引列的值。可以将基于函数的索引建立创建成位图索引。。这些表达式可以是算术运算表达式、SQL或PL/SQL函数、C调用等。值得注意的是，一般用户要创建函数索引，必须具有GLOBAL QUERY REWRITE和CREATE ANY INDEX权限。否则不能创建函数索引，看下面例子：
例1：为EMP表的ename 列建立大写转换函数的索引idx ： 

CREATE INDEX idx ON emp ( UPPER(ename));
--这样就可以在查询语句来使用
SELECT * FROM EMP WHERE UPPER(ename) LIKE ‘JOH%’;