oracle索引

select * from user_indexes 查询出用户所有表的索引
select * from userindcolumns 可获知索引建立在那些字段上
1、索引是一种用于提升查询效率的数据库对象；通过快速定位数据的方法，减少磁盘的I/O操作；索引信息与表独立存放；Oracle数据库自动使用和维护索引。
索引分为唯一性索引和非唯一性索引；
创建索引分为自动创建:在定义主键或唯一约束时系统会自动在相应的字段上创建唯一性索引；手动创建：用户可以在其它列上创建非唯一的索引，以加速查询；
2、建立索引的优点：
–大大较快数据的检索速度；
–创建唯一性索引，保证数据库表中每一行数据的唯一性；
–加速表和表之间的连接；
–在使用分组和排序子句进行检索是，可以显著减少查询中分组和排序的时间。
索引的缺点：
–索引需要占物理空间；
–当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。
3、创建索引的原则
创建索引一般有一下两个目的：维护被索引列的唯一性和提供快速访问表中数据的策略。
–在select操作站大部分的表上创建索引；
–在where字句中出现嘴频繁的列上创建索引；
–在选择性高的列上创建索引（索引选择性，最高是1，eg：primary key）；
–复合索引的主列应该是最有选择性的和where限定条件最常用的列，并以此类推第二…
–小于5M的表，最好不要使用索引来查询，表越小，越适合用全表扫描。

4、使用索引的原则
—查询结果是所有数据行的5%以下时，使用index查询效果最好；
----where条件中经常用到表的多列时，使用复合索引效果会好于几个单列索引。因为当sql
语句所查询的列，全部都出现在复合索引中时，此时由于 Oracle 只需要查询索引块即可获
得所有数据，当然比使用多个单列索引要快得多；
------索引利于select，但对经常insert，delte尤其update的表，会降低效率。

语句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的效率高.

create index xxx on table1 (a,b,c,d)create index xxx on table1 (a,b,c,d)
—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 yx_index;

7、索引类型
B树索引（B-Tree Index）
创建索引的默认类型，结构是一颗树，采用的是平衡B树算法：
 右子树节点的键值大于等于父节点的键值
 左子树节点的键值小于等于父节点的键值
比如有数据:100,101,102,103,104,105,106
101 105
100 102 104 106
位图索引(BitMap Index)
如果表中的某些字段取值范围比较小，比如职员性别、分数列ABC级等。只有两个值。
这样的字段如果建B树索引没有意义，不能提高检索速度。这时我们推荐用位图索引
Create BitMap Index student on(sex);

8、管理索引
1 ）先插入数据后创建索引
向表中插入大量数据之前最好不要先创建索引，因为如果先建立索引。那么在插入每行
数据的时候都要更改索引。这样会大大降低插入数据的速度。
2 ）设置合理的索引列顺序
3 ）限制每个表索引的数量
4 ）删除不必要的索引
5 ）为每个索引指定表空间
CREATE INDEX idx_vehiclepass_com3
ON traffic_vehicle_pass(plate_no, pass_time, crossing_id) TABLESPACE P201507; --为索引指定表空间
6 ）经常做insert，delete尤其是update的表最好定期exp/imp表数据，整理数据，降低碎片（缺点：
要停应用，以保持数据一致性，不实用）；有索引的最好定期rebuild索引（rebuild
期间只允许表的select操作，可在数据库较空闲时间提交），以降低索引碎片，提高效率

9、索引问题
一个表的查询语句可以同时用到两个索引。如下图：
索引是以独立于表存在的一种数据库对象，它是对基表的一种排序（默认是 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 条件的记录。
这样的查询效率，肯定是大于没有索引情况的全表扫描（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