Oracle索引

在关系数据库中，索引是一种与表有关的数据库结构，它可以使对应于表的 SQL 语句执行得更快。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

对于数据库来说，索引是一个必选项，但对于现在的各种大型数据库来说，索引可以大大提高数据库的性能，以至于它变成了数据库不可缺少的一部分。

1 、索引的概念

索引是关系数据库中用于存放每一条记录的一种对象，主要目的是加快数据的读取速度和完整性检查。

如果某列出现在查询的条件中，而该列的数据是无序的，查询时只能从第一行开始一行一行的匹配。创建索引就是对某些特定列中的数据排序，生成独立的索引表。在某列上创建索引后，如果该列出现在查询条件中， Oracle 会自动的引用该索引，先从索引表中查询出符合条件记录的 ROWID ，由于 ROWID 是记录的物理地址，因此可以根据 ROWID 快速的定位到具体的记录，表中的数据非常多时，引用索引带来的查询效率非常可观。

2 、建立索引的目的

(1) 提高对表的查询速度
(2) 对表有关列的取值进行检查

但是， 对表进行 insert,update,delete 处理时，由于要将表的存放位置记录到索引项中而会降低一些速度。

注意： 一个基表不能建太多的索引；
空值不能被索引 -- 创建索引

3 、什么时候使用索引

1. 如果表中的某些字段经常被查询并作为查询的条件出现时，就应该考虑为该列创建索引。
2. 当从很多行的表中查询少数行时，也要考虑创建索引。有一条基本的准则是：当任何单个查询要检索的行少于或者等于整个表行数的 10% 时，索引就非常有用。

索引的缺点：

Oracle 数据库会为表的主键和包含唯一约束的列自动创建索引。索引可以提高查询的效率，但是在数据增删改时需要更新索引，因此索引对增删改时会有负面影响。

4 、创建索引的一些规则 :

1 、权衡索引个数与 DML 之间关系， DML 也就是插入、删除数据操作。

这里需要权衡一个问题，建立索引的目的是为了提高查询效率的，但建立的索引过多，会影响插入、删除数据的速度，因为我们修改的表数据，索引也要跟着修改。这里需要权衡我们的操作是查询多还是修改多。

2 、把索引与对应的表放在不同的表空间。

当读取一个表时，表与索引是同时进行的。如果表与索引在一个表空间里就会产生资源竞争，放在两个表这空就可并行执行。

3 、最好使用一样大小块。

Oracle 默认五块，读一次 I/O ，如果你定义 6 个块或 10 个块都需要读取两次 I/O 。最好是 5 的整数倍更能提高效率。

4 、如果一个表很大，建立索引的时间很长，因为建立索引也会产生大量的 redo 信息，所以在创建索引时可以设置不产生或少产生 redo 信息。只要表数据存在，索引失败了大不了再建，所以可以不需要产生 redo 信息。

5 、建索引的时候应该根据具体的业务 SQL 来创建，特别是 where 条件，还有 where 条件的顺序，尽量将过滤大范围的放在后面（ GMSFHM ），因为 SQL 执行是从后往前执行的。

5 、索引操作

5.1 创建索引

创建索引：

CREATE [UNIQUE] INDEX index_name ON table_name(column_name[,column_name … ])

实例 : 为 EMP 表的 ENAME 列创建创建唯一索引，为 EMP 表的工资列创建普通索引，把 JOB 列先变为小写再创建索引。

CREATE UNIQUE INDEX UQ_ENAME_IDX ON EMP(ENAME);

CREATE INDEX IDX_SAL ON EMP(SAL);

CREATE INDEX IDX_JOB_LOWER ON EMP(LOWER(JOB));

5.2 修改索引

修改索引 : 主要任务是修改已存在索引的存储参数适应增长的需要或者重新建立索引。

简要语法结构如下 :

ALTER [UNIQUE] INDEX [user.]index
[INITRANS n]
[MAXTRANS n]
REBUILD
[STORAGE n]

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

例： alter index IDX_SAL rebuild storage(initial 1m next512k);

ALTER INDEX IDX_SAL REBUILD REVERSE;

Oracle8i 的新功能可以对索引的无用空间进行合并：

ALTER INDEX . . . COALESCE; （整合索引碎片）

例如：ALTER INDEX IDX_SALCOALESCE; **

5.3 删除索引

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

DROP INDEX [schema.]indexname

例如：

sql> drop index IDX_SAL;

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

5.4 查询索引

查询索引：

显示表的所有索引：
select index_name,index_type from user_indexes

where table_name=' 表名 ';

显示索引列：

select table_name,column_name from user_ind_columns

where index_name='UQ_ENAME_IDX';

6 、索引分类

oracle 的索引分为 5 种 :

（ 1 ）唯一索引：唯一索引确保被索引的字段或多个联合字段在表中绝对不会有重复值

（ 2 ）组合索引：当两个或多个列经常一起出现在 where 条件中时，则在这些列上同时创建组合索引（组合索引中列的顺序是任意的，也无需相邻。但是建议将最频繁访问的列放在列表的最前面）

（ 3 ）反向键索引

（ 4 ）位图索引：列中有非常多的重复的值时候。例如某列保存了 “性别”信息； Where 条件中包含了很多 OR 操作符；较少的 update 操作，因为要相应的更新所有的 bitmap

（ 5 ）基于函数的索引：在 WHERE 条件语句中包含函数或者表达式时

创建索引的标准语法 :

CREATE INDEX 索引名 ON 表名 ( 列名 )

TABLESPACE 表空间名 ;

6.1 唯一索引

创建唯一索引 :

CREATE unique INDEX 索引名 ON 表名 ( 列名 )

TABLESPACE 表空间名 ;

6.2 组合索引

创建组合索引 :

CREATE INDEX 索引名 ON 表名 ( 列名 1, 列名 2)

TABLESPACE 表空间名 ;

6.3 反向键索引

创建反向键索引 :

CREATE INDEX 索引名 ON 表名 ( 列名 ) reverse

TABLESPACE 表空间名 ;

6.4 位图索引

创建位图索引 :

CREATE BITMAP INDEX index ON table (column[, column]...);

我们可以看出，一个叶子节点（用不同颜色标识）代表一个 key , start rowid 和 end rowid 规定这种类型的检索范围，一个叶子节点标记一个唯一的 bitmap 值。因为一个数值类型对应一个节点，当查询时，位图索引通过不同位图取值直接的位运算（与或），来获取到结果集合向量（计算出的结果）。

举例 ;

ename gender salary dept_id
lily M 3121.26 7

Wanghua F 4177.26 34

Smith M 13649.17 11

对 ename ， gender ， dept_id 分别建立位图索引

ename 列中的数据：

lily Wanghua Smith

1 0 0

0 1 0

0 0 1

gender 列中的数据： dept_id 列中的数据：

M F 7 34 11

1 0 1 0 0

0 1 0 1 0

1 0 0 0 1

select * from employees where ename = lily ' and gender = 'M' and dept_id = '7';

那么对于位图索引，就是把上面的结果执行与（ and ）运算。（如果 SQL 语句中的谓词使用的是 or ，那么，对于位图索引使用的是或运算）。

lily M 7

1 and 1 and 1 = 1

0 0 0 0

0 1 0 0

这样，记录已经找到。

从上述这个结构，我们应该已经知道位图使用的场合了，就是对于相异基数较低的情况下适合。比如一个列的值只有 Y ， N ， NULL ，那么，可以考虑使用位图索引。

位图索引的特点：

1.Bitmap 索引的存储空间节省

2.Bitmap 索引创建的速度快

3.Bitmap 索引允许键值为空

4.Bitmap 索引对表记录的高效访问

6.5 B-Tree 索引

B-Tree index 也是我们传统上常见所理解的索引。 B-tree （ balance tree ）即平衡树，左右两个分支相对平衡。

Root 为根节点， branch 为分支节点， leaf 到最下面一层称为叶子节点。每个节点表示一层，当查找某一数据时先读根节点，再读支节点，最后找到叶子节点。叶子节点会存放 index entry （索引入口），每个索引入口对应一条记录。

Indexentry的组成部分：

（ 1 ）Indexentry entryheader** 存放一些控制信息。

（ 2 ）Key columnlength 某一 key 的长度

（ 3 ）Key columnvalue 某一个 key 的值

（ 4 ）ROWID 指针，具体指向于某一个数据

这张图能更加清晰的描述索引的结构。

根节点记录 0 至 50 条数据的位置，分支节点进行拆分记录 0 至 10.......42 至 50 ，叶子节点记录每个数据的长度和值，并由指针指向具体的数据。最后一层的叶子节是双向链接，它们是被有序的链接起来，这样才能快速锁定一个数据范围。

比如： select * from dex where id>23 and id<32;

通过索引的方式先找到第 23 条数据，再找到第 32 条数据，这样就能快速的锁定一个查找的范围，如果每条数据都要从根节点开始查找的话，那么效率就会非常低下。

但是：将该语句换成使用不等于操作符（ <> 、 != ）会使得查询不走索引。

6.6 函数索引

创建函数的索引 :

CREATE INDEX index ON table (FUNCTION(column));

例如：

例 1 ：为 EMP 表的 JOB 列建立小写转换函数的索引 idx ：

CREATE INDEX IDX_JOB_LOWER ON EMP(LOWER(JOB));

这样就可以在查询语句来使用：

SELECT * FROM EMP WHERE LOWER(JOB) LIKE 'tea%';

前提：连接到 DBA 帐户并授权：

SQL> grantGLOBAL QUERY REWRITEto scott;

Grant succeeded.

SQL> grant CREATE ANY INDEXto scott;

Grant succeeded.

7 、索引的限制

在 SQL 中有很多陷阱会使一些索引无法使用：（见举例）

（ 1 ）使用不等于操作符（ <> 、 != ）

（ 2 ）使用 IS NULL 或 IS NOT NULL

（ 3 ）使用函数

如果不使用基于函数的索引，那么在 SQL 语句的 WHERE 子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。

（ 4 ）比较不匹配的数据类型

8 、 index 举例脚本及原理

-- 删除索引

drop index IDX_SAL;

select * from user_indexes t where t.TABLE_NAME='EMP';

-- 查询执行计划

EXPLAIN PLAN FOR select t.*, t.rowid from EMP t where t.ename='Lily';

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

或者：长按 F5

-- 查询索引信息

select * from user_indexes where TABLE_NAME='EMP'

-- 查询索引列的信息

select * from user_ind_columns t where t.TABLE_NAME='EMP'

-- 唯一索引

CREATE UNIQUE INDEX UQ_ENAME_IDX ON EMP(ENAME);

-- （ 1 ）创建唯一约束或者主键约束，再或者建表以后给表添加唯一、主键约束时， Oracle 会自动在主键、唯一约束的字段上创建唯一索引，

-- 并且索引的名字跟约束的名字一样 .

create table tt(

id1 number primary key,

id2 number,

id3 number);

select index_name,table_name,uniqueness

from user_indexes

where table_name='TT';

select table_name,constraint_name,index_name,constraint_type

from user_constraints

where table_name='TT';

-- 在 id2 字段上添加一个唯一约束，并且手动指定名字：

alter table tt add constraint tt_id2_uk unique(id2);

select table_name,constraint_name,index_name,constraint_type

from user_constraints

where table_name='TT';

-- 组合索引

create index mgr_comm_index on emp(mgr,comm);

select *from emp where mgr= '12' and comm='0';

select *from emp where mgr= '12';

select *from emp where comm= ='0';

-- 反向索引

create index re_idx on EMP(COMM) reverse

-- 位图索引

create bitmap bit_index on emp()

select * from v$option

-- （ 1 ）比较是否走索引 不等于操作

select t.*, t.rowid from EMP t where t.ename='Lily';
select t.*, t.rowid from EMP t where t.ename<>'Lily';
-- 在采用基于规则的优化器而不是基于代价的优化器（更智能）时，将会使用索引。

(RBO: Rule-Based Optimization 基于规则的优化器

CBO: Cost-Based Optimization 基于代价的优化器 )

select t.*, t.rowid from EMP t where t.ename<'Lily' or t.ename>'Lily';

-- （ 2 ）比较是否走索引 is null

select t.*, t.rowid from EMP t where t.mgr=’12’;

select t.*, t.rowid from EMP t where t.mgr is null;

select t.*, t.rowid from EMP t where t.mgr is not null;

-- （ 3 ）比较是否走索引 使用函数

select t.*, t.rowid from EMP t where t.ename='Lily';
select t.*, t.rowid from EMP t where upper(t.ename)='Lily';
select t.*, t.rowid from EMP t where t.ename='Lily'||'qqq';

-- 比较不匹配的数据类型

-- （ 4 ）比较是否走索引

select t.*, t.rowid from EMP t where t.ename=88888;

-- Oracle 可以自动把 where 子句变成 to_number(ename)=88888 ，这样就限制了索引 的使用 ,

-- 改成下面的查询就可以使用索引：

select t.*, t.rowid from EMP t where t.ename='88888';

看一幅 B 树索引的内部结构图：

对于这幅 B 树存储结构图作以下几点介绍：

1 索引高度是指从根块到达叶子块时所遍历的数据块的个数，而索引层次 = 索引高度 -1 ；本图中的索引的高度是 3 ，索引层次等于 2 ；通常，索引的高度是 2 或者 3 ，即使表中有上百万条记录，也就意味着，从索引中定位一个键字只需要 2 或 3 次 I/O, 索引越高，性能越差；

2 B 树索引包含两种数据块儿：分枝块 (Branch Block) 和叶子块 (Leaf Block) ；

3 分枝块里存放指向下级分枝块 ( 索引高度大于 2 ，即有超过两层分枝块的情况 ) 或者直接指向叶子块的指针 ( 索引高度等于 2 ，即层次为 1 的索引 ) ；

4 叶子块，就是位于 B 树结构里最底层的数据块。叶子块里存放的是索引条目，即索引关键字和 rowid ， rowid 用来精确定位表里的记录；索引条目都是按照索引关键字 +rowid 已经排好序存放的；同一个叶子块里的索引条目同时又和左右兄弟条目形成链表，并且是一个双向链表；

5 B 树索引的所有叶子块一定位于同一层上，这是由 B 树的数据结构定义的。因此，从根块到达任何一个叶子块的遍历代价都是相同的；