oracle索引

本文转载自《超详细oracle教程》电子书.

16.1、索引 
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 限定条件最常用的列，并以此类推第二

列……。 
--小于 5M 的表，最好不要使用索引来查询，表越小，越适合用全表扫描。 
16.4、使用索引的原则 
--查询结果是所有数据行的 5%以下时，使用 index 查询效果最好； 
--where 条件中经常用到表的多列时，使用复合索引效果会好于几个单列索引。因为当 sql 
语句所查询的列，全部都出现在复合索引中时，此时由于  Oracle  只需要查询索引块即可获
得所有数据，当然比使用多个单列索引要快得多； 
--索引利于 select，但对经常 insert，delte 尤其 update 的表，会降低效率。 
eg：试比较下面两条 SQL 语句(emp  表的 deptno 列上建有 ununique index)： 
语句 A：SELECT dname, deptno FROM dept WHERE deptno NOT IN    
(SELECT deptno FROM emp);  
语句 B：SELECT dname, deptno FROM dept WHERE NOT EXISTS 
(SELECT deptno   FROM emp WHERE 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、索引的存储 
索引和表都是独立存在的。在为索引指定表空间的时候，不要将被索引的表和索引指向同
一个表空间，这样可以避免产生 IO 冲突。使 Oracle 能够并行访问存放在不同硬盘中的索
引数据和表数据，更好的提高查询速度。 
16.6、删除索引 
 drop index PK_DEPT1; 
16.7、索引类型 
B树索引（B-Tree Index） 
创建索引的默认类型，结构是一颗树，采用的是平衡B树算法： 
  右子树节点的键值大于等于父节点的键值 
  左子树节点的键值小于等于父节点的键值 
比如有数据:100,101,102,103,104,105,106 

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

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

片（缺点：要停应用，以保持数据一致性，不实用）；有索引的最好定期 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 ON table1(x, y, z); 
 
x, xy, and xyz combinations of columns are leading portions of the index 
 
yz, y, and z combinations of columns are not leading portions of the index