oracle 索引管理

select * from  user_indexes 查询现有的索引
select * from   user_ind_columns 可获知索引建立在那些字段上

16.2、索引优缺点

建 立 索 引的 优 点
1.大 大加 快数 据的 检索 速度 ;
2.创 建唯 一性 索引 ，保 证数 据库 表中 每一 行数 据的 唯一性 ;
3.加 速表 和表 之间 的连 接 ;
4.在 使用 分组 和排 序子 句进 行数 据检 索时 ，可 以显 著减少 查询 中分 组和 排序 的时 间。 索 引 的 缺点
1.索 引需 要占 物理 空间 。
2.当 对 表 中 的数 据 进 行 增 加 、 删除 和 修 改 的 时 候 ， 索 引 也 要 动态 的 维 护 ， 降 低了 数 据 的
维护速 度。

 

16.3、创建索引的原则

创建索 引： 创建 索引 一般 有以 下两 个目 的： 维护 被索 引 列的唯 一性 和提 供快 速访 问表 中数 据 的策略 。
--在 select 操作 占大 部分 的表 上创 建索 引；
--在 where 子句 中出 现最 频繁 的列 上创 建索 引；
--在选 择性 高的 列上 创建 索引 （补 充索 引选 择性 ，最 高 是 1， eg：primary  key）
--复合 索引 的主 列应 该是 最有 选择 性的和 where 限定条 件最常 用的 列， 并以 此类 推第 二

Oracle 教程

列……。
--小于 5M 的表 ，最 好不 要使 用索 引来 查询 ，表 越小 ， 越适合 用全 表扫 描。

16.4、使用索引的原则

--查询 结果 是所 有数 据行的 5%以下 时， 使用 index 查 询效果 最好 ；
--where 条 件中 经常 用到 表的 多列 时， 使用 复合 索引 效 果会好 于几 个单 列索 引。 因为 当  sql 语句所 查询 的列 ，全 部都 出现 在复 合索 引中 时， 此时 由 于   Oracle  只需 要查 询索 引块 即可 获 得所有 数据 ，当 然比 使用 多个 单列 索引 要快 得多 ；
--索引 利于 select，但对 经常 insert， delte 尤其 update 的表 ，会 降低 效率 。
eg： 试比 较下 面两 条 SQL 语句(emp  表的 deptno 列上 建有 ununique  index)：
语句 A：SELECT  dname,  deptno  FROM  dept  W HERE  deptno  NOT  IN

(SELECT  deptno  FROM  emp);
语句 B：SELECT  dname,  deptno  FROM  dept  W HERE  NOT  EXISTS

(SELECT  deptno   FROM  emp  W HERE  dept.deptno  =  emp.deptno);
这两条 查询 语句 实现 的结 果是 相同 的， 但是 执行 语句  A 的时候， ORACLE 会 对整个 emp 表 进行扫 描， 没有 使用 建立 在 emp 表 上的 deptno 索引 ， 执行语 句 B 的时 候， 由于 在子 查询 中 使用了 联合 查询 ，ORACLE 只是对 emp 表进 行的 部分 数据扫 描 ，并 利用了 deptno 列 的索 引， 所以语 句 B 的效 率要 比语句 A 的 效率 高。

----where  子句中的这个字段，必须是复合索引的第一个字段；
eg：一个索引是按 f1, f2, f3 的次序建立的，若 where  子句是 f2 = : var2,  则因为 f2  不是索 引的第 1 个字段，无法使用该索引。
----  where  子句中的这个字段，不应该参与任何形式的计算：任何对列的操作都将导致表 扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。
---- 应尽量熟悉各种操作符对   Oracle  是 否 使 用 索 引 的 影 响 ： 以 下 这 些 操 作 会 显 式
（ explicitly ）地阻止   Oracle  使用索引：   is  null  ;    is  not   null   ;    not  in;    !=;   like  ;
numeric_col+0;date_col+0; char_col||' ';   to_char;   to_number，to_date  等。
Eg：
Select  jobid  from  mytabs  where  isReq='0'  and to_date  (updatedate)  >=  to_Date  (  '2001-7-18',
'YYYY-MM-DD')；--updatedate 列的索引也不会生效。

16.4、创建索引

create index abc on student(sid,sname);
create index abc1 on student(sname,sid);
这两种索引方式是不一样的
索引 abc 对 Select * from student where sid=1;  这样的查询语句更有效
索引 abc1 对 Select * from student where sname=‟louis‟;  这样的查询语句更有效 因此建立索引的时候，字段的组合顺序是非常重要的。一般情况下，需要经常访问的字段 放在组合字段的前面

16.5、索引的存储
 
Oracle 教程
 

索引和表都是独立存在的。在为索引指定表空间的时候，不要将被索引的表和索引指向同
一个表空间，这样可以避免产生 IO 冲突。使 Oracle 能够并行访问存放在不同硬盘中的索 引数据和表数据，更好的提高查询速度。

16.6、删除索引

drop index PK_DEPT1;

16.7、索引类型

B 树索引（B-Tree Index）

创建索引的默认类型，结构是一颗树，采用的是平衡 B 树算法：
 右子树节点的键值大于等于父节点的键值
 左子树节点的键值小于等于父节点的键值 比如有数据:100,101,102,103,104,105,106

103

 

101 105

 

100 102 104 106

 

位图索引(BitMap Index)

如果表中的某些字段取值范围比较小，比如职员性别、分数列  ABC  级等。只有两个值。 这样的字段如果建 B 树索引没有意义，不能提高检索速度。这时我们推荐用位图索引
Create BitMap Index student on(sex);

16.8、管理索引

1）先插入数据后创建索引 向表中插入大量数据之前最好不要先创建索引，因为如果先建立索引。那么在插入每行 数据的时候都要更改索引。这样会大大降低插入数据的速度。
2）设置合理的索引列顺序
3）限制每个表索引的数量
4）删除不必要的索引
5）为每个索引指定表空间
6）经常做 insert，delete 尤其是 update 的表最好定期 exp/imp 表数据，整理数据，降低碎

 

Oracle 教程
片（缺点：要停应用，以保持数据一致性，不实用）；有索引的最好定期 rebuild 索引（rebuild
期间只允许表的 select 操作，可在数据库较空闲时间提交），以降低索引碎片，提高效率

 

16.8、索引问题

1：针对一个表的查询语句能否会用到两个索引?
2：如果能用到，那么其实现原理是怎样的？
3：效率如何？其代价如何，比如额外开销等。

======================================
回答：

1.一个表的查询语句可以同时用到两个索引。如下图：

 

2.索引是以独立于表存在的一种数据库对象，它是对基表的一种排序（默认是 B 树索引就是二叉树的排序
方式），比如：
t 表(x,y,z) ,在 x,y,z 上分别都建立了索引(index1,index2,index3)，那在查询 select * from t where x=1 and y=2;的时候，会分别用到 index1,index2。
原理是先到 index1 索引表中查到符合 x=1 条件的记录，然后到 index2 索引表中查到 y=2 条件的记录。
3.这样的查询效率，肯定是大于没有索引情况的全表扫描（table access full)，但是有两个问题。

问题一：建立索引将占用额外的数据库空间，更重要的是增删改操作的时候，索引的排序也必须改变， 加大的维护的成本
问题二：如果经常查询 x=?和 y=?，那推荐使用组合 index(x,y)，这种情况下组合索引的效率是远高于
两个单独的索引的。

同时在用组合索引的时候，大家一定要注意一个细节：建立组合索引  index(x,y,z)的时候，那在查询条件 中出现 x,xy,xyz,yzx 都是可以用到该组合索引，但是 y,yz,z 是不能用到该索引的。关于这段话的原文如下：

A leading portion of an index is a set of one or more columns that were specified first and consecutively in the list  of  columns  in  the  CREATE  INDEX  statement  that  created  the  index.  Consider  this  CREATE  INDEX statement:

CREATE INDEX comp_ind