oracle索引(oracle索引失效的几种情况)

oracle索引

1.阐明
1）索引是数据库目标之一，用于加速数据的检索，类似于书本的索引。在数据库中索引能够减少数据库程序查询成果时需要读取的数据量，类似于在书本中咱们运用索引能够不用翻阅整本书即可找到想要的信息。
2）索引是树立在表上的可选目标；索引的关键在于通过一组排序后的索引键来替代默许的全表扫描检索方法，然后进步检索功率
3）索引在逻辑上和物理上都与相关的表和数据无关，当创立或许删去一个索引时，不会影响基本的表；
4）索引一旦树立，在表上进行DML操作时（例如在履行刺进、修正或许删去相关操作时），oracle会主动办理索引，索引删去，不会对表发生影响
5）索引对用户是透明的，不论表上是否有索引，sql语句的用法不变
6）oracle创立主键时会主动在该列上创立索引
索引原理
1.若没有索引，查找某个记载时（例如查找name=’wish’）需要查找一切的记载，由于不能确保只要一个wish，必须悉数查找一遍
2.若在name上树立索引，oracle会对全表进行一次查找，将每条记载的name值哪找升序摆放，然后构建索引条目（name和rowid），存储到索引段中，查询name为wish时即可直接查找对应当地
3.创立了索引并不一定就会运用，oracle主动统计表的信息后，决定是否运用索引，表中数据很少时运用全表扫描速度现已很快，没有必要运用索引
索引运用（创立、修正、删去、查看）
1.创立索引语法
CREATE[UNIQUE]|[BITMAP]INDEXindex_name–unique表明仅有索引ONtable_name([column1[ASC|DESC],column2–bitmap，创立位图索引[ASC|DESC],…]|[express])[TABLESPACEtablespace_name][PCTFREEn1]–指定索引在数据块中空闲空间[STORAGE(INITIALn2)][NOLOGGING]–表明创立和重建索引时答应对表做DML操作，默许情况下不应该运用[NOLINE][NOSORT];–表明创立索引时不进行排序，默许不适用，假如数据现已是按照该索引顺序摆放的能够运用
2.修正索引
1）重命名索引
alterindexindex_snorenametobitmap_index;
2)兼并索引（表运用一段时刻后在索引中会发生碎片，此时索引功率会降低，能够挑选重建索引或许兼并索引,兼并索引方法更好些，无需额定存储空间，价值较低）
alterindexindex_snocoalesce;
3)重建索引
方法一：删去本来的索引，从头树立索引
方法二：
alterindexindex_snorebuild;
3.删去索引
dropindexindex_sno;
4.查看索引
selectindex_name,index-type,tablespace_name,uniquenessfromall_indexeswheretable_name=’tablename’;–eg:createindexindex_snoonstudent(‘name’);select*fromall_indexeswheretable_name=’student’;
索引分类
1.B树索引（默许索引，保存讲过排序过的索引列和对应的rowid值）
1）阐明：
1.oracle中最常用的索引；B树索引就是一颗二叉树；叶子节点（双向链表）包括索引列和指向表中每个匹配行的ROWID值
2.一切叶子节点具有相同的深度，因而不论查询条件怎样，查询速度基本相同
3.能够适应精确查询、模糊查询和比较查询
2）分类：
UNIQUE,NON-UNIQUE(默许),REVERSEKEY（数据列中的数据是反向存储的）
3）创立比如
craeteindexindex_snoonstudent(‘sno’);
4）合适运用场景：
列基数（列不重复值的个数）大时合适运用B数索引
2.位图索引
1）阐明：
1.创立位图索引时，oracle会扫描整张表，并为索引列的每个取值树立一个位图（位图中，对表中每一行运用一位（bit，0或许1）来标识该行是否包括该位图的索引列的取值，假如为1，表明对应的rowid地点的记载包括该位图索引列值），最后通过位图索引中的映射函数完结位到行的ROWID的转换
2)创立比如
createbitmapindexindex_snoonstudent(sno);
3)合适场景：
关于基数小的列合适简历位图索引（例如性别等）
3.单列索引和复合索引（基于多个列创立）
1)注意：
即假如索引树立在多个列上，只要它的第一个列被where子句引用时，优化器才会运用该索引，即至少要包括组合索引的第一列
4.函数索引
1)阐明：
1.当常常要拜访一些函数或许表达式时，能够将其存储在索引中，这样下次拜访时，该值现已计算出来了，能够加速查询速度
2.函数索引既能够运用B数索引，也能够运用位图索引；当函数成果不确定时选用B树索引，成果是固定的某几个值时运用位图索引
3.函数索引中能够水泥用len、trim、substr、upper（每行回来独立成果），不能运用如sum、max、min、avg等
2）比如：
createindexfbionstudent(upper(name));select*fromstudentwhereupper(name)=’WISH’;
索引树立原则总结
1.假如有两个或许以上的索引，其中有一个仅有性索引，而其他是非仅有，这种情况下oracle将运用仅有性索引而完全忽略非仅有性索引
2.至少要包括组合索引的第一列（即假如索引树立在多个列上，只要它的第一个列被where子句引用时，优化器才会运用该索引）
3.小表不要简历索引
4.关于基数大的列合适树立B树索引，关于基数小的列合适简历位图索引
5.列中有许多空值，但常常查询该列上非空记载时应该树立索引
6.常常进行衔接查询的列应该创立索引
7.运用createindex时要将最常查询的列放在最前面
8.LONG（可变长字符串数据，最长2G）和LONGRAW（可变长二进制数据，最长2G）列不能创立索引
9.限制表中索引的数量（创立索引消耗时刻，而且随数据量的增大而增大；索引会占用物理空间；当对表中的数据进行添加、删去和修正的时分，索引也要动态的保护，降低了数据的保护速度）
注意事项
1.通配符在查找词首出现时，oracle不能运用索引，eg：
仿制代码
–咱们在name上创立索引；createindexindex_nameonstudent(‘name’);–下面的方法oracle不适用name索引select*fromstudentwherenamelike’%wish%’;–假如通配符出现在字符串的其他方位时，优化器能够运用索引；如下：select*fromstudentwherenamelike’wish%’;
2.不要在索引列上运用not，能够选用其他方法代替如下：（oracle碰到not会停止运用索引，而选用全表扫描）
select*fromstudentwherenot(score=100);select*fromstudentwherescore<>100;–替换为select*fromstudentwherescore>100orscore<100
3.索引上运用空值比较将停止运用索引，eg：
select*fromstudentwherescoreisnotnull;

oracle索引失效的几种情况

创立Oracle索引的意图是为了防止全表扫描数据，进步查询功率，但是假如sql句子写的欠好致使索引失效，反而会影响数据查询功率。以下几种状况就会导致索引失效：
0.没有WHERE子句
众所周知，添加索引的字段必需要在where条件后恰当运用才会收效，假如连查询条件都没有，那肯定不会用到索引的。
1．隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的过错。
dept_id是一个varchar2型的字段，但在查询时把该字段作为number类型以where条件传给Oracle,这样会导致索引失效。
select*fromtempwheredept_id=33333333333;//过错的比如select*fromtempwheredept_id=’33333333333′;//正确的比如
原因：oracle会自动把where子句转换成to_number(dept_id)=33333333333，相当于运用函数，这样就约束了索引的运用。
2.运用ISNULL和ISNOTNULL
select*fromempwherecolnumisnull;//运用ISNULL后，colnum列的索引会失效
3.WHERE子句中运用函数不标准
3.1转变一下：
假如没有运用根据函数的索引，那么where子句中对存在索引的列运用函数时，会使优化器忽略掉这些索引。例如：
select*fromstaffwheretrunc(birthdate)=’01-MAY-82′;
把函数应用在条件上，索引是能够收效的，把上面的句子改成下面的句子，就能够经过索引进行查找。
select*fromstaffwherebirthdate<(to_date(’01-MAY-82′)+0.9999);
留意：关于MIN,MAX函数，Oracle依然运用索引。
3.2根据函数的索引：
运用Oracle内部函数导致索引失效.关于这样状况应当创立根据函数的索引.
过错的比如：select*fromtestwhereround(id)=10;阐明，此刻id的索引现已不起作用了
正确的比如：首要建立函数索引，createindextest_id_fbi_idxontest(round(id));然后select*fromtestwhereround(id)=10;这时函数索引起作用了
4.运用LIKE‘%T’进行含糊查询
select*fromstudentwherenamelike’aaa%’;//’aaa%’会用到索引select*fromstudentwherenamelike’%aaa’;//’%aaa’或者’_aaa’不会运用索引
5.对索引列进行运算导致索引失效,我所指的对索引列进行运算包括(+，-，*，/，!等)
select*fromtestwhereid-1=9;//过错的比如select*fromtestwhereid=10;//正确的比如
6.WHERE子句中运用不等于操作
不等于操作包括：<>,!=,NOTcolum>=?,NOTcolum<=?
关于这个约束条件能够经过OR代替，例如：colum<>0===>colum>0ORcolum<0
7.等于和范围索引不会被兼并运用
SELECTemp_id,emp_m,salary_q…FROMempWHEREjob=’manager’ANDdeptno>10
job和deptno都对错仅有索引，这种条件下oracle不会兼并索引，它只会运用第一个索引。
8.不要在SQL代码中运用双引号。
因为字符常量运用单引号。假如没有必要限定目标称号，能够运用（非ANSISQL标准）括号将称号括起来。
9.将索引地点表空间和数据地点表空间别离设于不同的磁盘chunk上，有助于进步索引查询的功率。
10.Oracle默许运用的根据价值的SQL优化器（CBO）非常依赖于计算信息，一旦计算信息不正常，会导致数据库查询时不运用索引或运用过错的索引。
一般来说，Oracle的自动使命里面会包括更新计算信息的句子，但假如表数据发生了比较大的变化（超过20%）,能够考虑立即手动更新计算信息，
例如：analyzetableabccomputestatistics，但留意，更新计算信息比较消耗体系资源，建议在体系空闲时履行。
11.Oracle在进行一次查询时，一般对一个表只会运用一个索引。
因此，有时候过多的索引或许导致Oracle运用过错的索引，降低查询功率。例如某表有索引1（Policyno）和索引2（classcode），
假如查询条件为policyno=‘xx’andclasscode=‘xx’，则体系有或许会运用索引2，相较于运用索引1，查询功率显着降低。
12.优先且尽或许运用分区索引。