深入索引

select * from  user_indexes 查询现有的索引

select * from  user_ind_columns 可获知索引建立在那些字段上

1索引优缺点

建立索引的优点

1.大大加快数据的检索速度;

2.创建唯一性索引，保证数据库表中每一行数据的唯一性;

3.加速表和表之间的连接;

4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。 索引的缺点

1.索引需要占物理空间。

2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的

维护速度。

2.创建索引的原则

创建索引：创建索引一般有以下两个目的：维护被索引列的唯一性和提供快速访问表中数据 的策略。

--在 select 操作占大部分的表上创建索引；

--在 where 子句中出现最频繁的列上创建索引；

--在选择性高的列上创建索引（补充索引选择性，最高是 1，eg：primary  key）

--复合索引的主列应该是最有选择性的和 where 限定条件最常用的列，并以此类推第二列MicrosoftInternetExplorer402DocumentNotSpecified7.8Normal0

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

3.使用索引的原则

--查询结果是所有数据行的 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 列的索引也不会生效。

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‟; 这样的查询语句更有效 因此建立索引的时候，字段的组合顺序是非常重要的。一般情况下，需要经常访问的字段 放在组合字段的前面

5.索引的存储

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

6.删除索引

drop index PK_DEPT1;

7.索引类型

B 树索引（B-Tree Index）

创建索引的默认类型，结构是一颗树，采用的是平衡 B 树算法：

l 右子树节点的键值大于等于父节点的键值

l 左子树节点的键值小于等于父节点的键值 比如有数据:100,101,102,103,104,105,106

MicrosoftInternetExplorer402DocumentNotSpecified7.8Normal0

位图索引(BitMap Index)

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

Create BitMap Index student on(sex);

8.管理索引

1）先插入数据后创建索引 向表中插入大量数据之前最好不要先创建索引，因为如果先建立索引。那么在插入每行 数据的时候都要更改索引。这样会大大降低插入数据的速度。

2）设置合理的索引列顺序

3）限制每个表索引的数量

4）删除不必要的索引

5）为每个索引指定表空间

6）经常做 insert，delete 尤其是 update 的表最好定期 exp/imp 表数据，整理数据，降低碎

片（缺点：要停应用，以保持数据一致性，不实用）；有索引的最好定期rebuild索引（rebuild

期间只允许表的 select 操作，可在数据库较空闲时间提交），以降低索引碎片，提高效率