志在指尖
用双手敲打未来

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

oracle索引

一、以下的办法会引起索引失效
1,<>
2,独自的>,<,(有时会用到,有时不会)
3,like”%_”百分号在前.
4,表没剖析.
5,独自引证复合索引里非榜首方位的索引列.
6,字符型字段为数字时在where条件里不添加引号.
7,对索引列进行运算.需要树立函数索引.
8,notin,notexist.
9,当变量选用的是times变量,而表的字段选用的是date变量时.或相反状况。
10,索引失效。
11,根据cost本钱剖析(oracle由于走全表本钱会更小):查询小表,或者返回值大概在10%以上
12,有时都考虑到了但便是不走索引,drop了从建试试在
13,B-tree索引isnull不会走,isnotnull会走,位图索引isnull,isnotnull都会走
14,联合索引isnotnull只需在树立的索引列(不分先后)都会走,
innull时有必要要和树立索引榜首列一同运用,当树立索引榜首方位条件是isnull时,其他树立索引的列可所以isnull(但有必要在所有列都满足isnull的时分),或者=一个值;
当树立索引的榜首方位是=一个值时,其他索引列可所以任何状况(包含isnull=一个值),以上两种状况索引都会走。其他状况不会走。
二、索引失效解决办法
1.选用合适的Oracle优化器
Oracle的优化器共有3种:
a.RULE(根据规则)b.COST(根据本钱)c.CHOOSE(选择性)。
设置缺省的优化器,能够经过对init.ora文件中OPTIMIZER_MODE参数的各种声明,如RULE,COST,CHOOSE,ALL_ROWS,FIRST_ROWS。你当然也在SQL句级或是会话(session)级对其进行覆盖。
为了运用根据本钱的优化器(CBO,Cost-BasedOptimizer),你有必要常常运转analyze指令,以添加数据库中的对象统计信息(objectstatistics)的准确性。
假如数据库的优化器方式设置为选择性(CHOOSE),那么实践的优化器方式将和是否运转过analyze指令有关。假如table现已被analyze过,优化器方式将自动成为CBO,反之,数据库将选用RULE方式的优化器。
(剖析table
analyzetablePROD_PARTScomputestatistics;
ANALYZETABLEPROD_PARTSCOMPUTESTATISTICSFORALLINDEXEDCOLUMNS;
analyzetablePROD_PARTScomputestatisticsfortableforallindexesforallindexedcolumns;
)【有一次索引失效之后,请教DBA后,发现是数据统计的问题,具体的解决办法是履行以上句子】
在缺省状况下,Oracle选用CHOOSE优化器,为了避免那些不必要的全表扫描(fulltablescan),你有必要尽量避免运用CHOOSE优化器,而直接选用根据规则或者根据本钱的优化器。
2、重建索引
alterindex索引名rebuild【online】
3、强制索引
给该句子加上hint后,强制其运用’RECORD_ENTITYID’这个索引
sql句子变成这样
引证
select/*+index(record,record_entityid)*/*
fromRECORD
whereentityId=’24’andentityType=’blog’;
/*+index(record,record_entityid)*/中,index表明强制运用index,record是表名,record_entityid是索引名。其履行计划跟测验数据库上共同,都是运用用’RECORD_ENTITYID’这个索引,逻辑读写相同为4。
后来经过测验,在不加hint的状况下,对该表和两个索引履行analyze后,相同也能运用’RECORD_ENTITYID’这个索引。可是由于该表更新颇为频繁,不知道要多久就要再剖析一次
可是假如是相同的sql假如在之前能够运用到索引,那么现在运用不到索引,以下几种首要状况:索引失效的原因
1.随着表的增长,where条件出来的数据太多,大于15%,使得索引失效(会导致CBO核算走索引花费大于走全表)
2.统计信息失效需要从头收集统计信息
3.索引自身失效需要重建索引
下面是一些不会运用到索引的原因
索引失效
1)没有查询条件,或者查询条件没有树立索引
2)在查询条件上没有运用引导列
3)查询的数量是大表的大部分,应该是30%以上。
4)索引自身失效
5)查询条件运用函数在索引列上(见12)
6)对小表查询
7)提示不运用索引
8)统计数据不真实
9)CBO核算走索引花费过大的状况。其实也包含了上面的状况,这里指的是表占有的block要比索引小。
10)隐式转换导致索引失效.这一点应当引起注重.也是开发中常常会犯的过错.由于表的字段tu_mdn定义为varchar2(20),
但在查询时把该字段作为number类型以where条件传给Oracle,这样会导致索引失效.
过错的比如:select*fromtestwheretu_mdn=13333333333;
正确的比如:select*fromtestwheretu_mdn=’13333333333′;
11)对索引列进行运算导致索引失效,我所指的对索引列进行运算包含(+,-,*,/,!等)
过错的比如:select*fromtestwhereid-1=9;
正确的比如:select*fromtestwhereid=10;
12)运用Oracle内部函数导致索引失效.关于这样状况应当创立根据函数的索引.
过错的比如:select*fromtestwhereround(id)=10;
说明,此时id的索引现已不起作用了正确的比如:首要树立函数索引,
createindextest_id_fbi_idxontest(round(id));
然后select*fromtestwhereround(id)=10;这时函数索引起作用了1,<>2,独自的>,<,(有时会用到,有时不会)
3,like”%_”百分号在前.
4,表没剖析.
5,独自引证复合索引里非榜首方位的索引列.
6,字符型字段为数字时在where条件里不添加引号.
7,对索引列进行运算.需要树立函数索引.
8,notin,notexist.
9,当变量选用的是times变量,而表的字段选用的是date变量时.或相反状况。
10,索引失效。
11,根据cost本钱剖析(oracle由于走全表本钱会更小):查询小表,或者返回值大概在10%以上
12,有时都考虑到了但便是不走索引,drop了从建试试在
13,B-tree索引isnull不会走,isnotnull会走,位图索引isnull,isnotnull都会走
14,联合索引isnotnull只需在树立的索引列(不分先后)都会走,
innull时有必要要和树立索引榜首列一同运用,当树立索引榜首方位条件是isnull时,
其他树立索引的列可所以isnull(但有必要在所有列都满足isnull的时分),
或者=一个值;当树立索引的榜首方位是=一个值时,其他索引列可所以任何状况(包含isnull=一个值),
以上两种状况索引都会走。其他状况不会走。oracle索引

oracle索引失效的几种情况

咱们在运用一个B*树索引,并且谓词中没有运用索引的最前列。
假如这种状况,能够假设有一个表T,在T(x,y)上有一个索引。要做以下查询:select*fromtwherey=5。此刻,优化器就不打算运用T(x,y)上的索引,由于谓词中不涉及X列。在这种状况下,假使运用索引,或许就有必要查看每个索引条目,而优化器通常更倾向于对T表做一个全表扫描。
zx@ORCL>createtabletasselectrownumx,rownum+1y,rownum+2zfromdualconnectbylevel<100000;
Tablecreated.
zx@ORCL>selectcount(*)fromt;
COUNT(*)
———-
99999
zx@ORCL>createindexidx_tont(x,y);
Indexcreated.
zx@ORCL>execdbms_stats.gather_table_stats(user,’T’,cascade=>true);
PL/SQLproceduresuccessfullycompleted.
zx@ORCL>setautotracetraceonlyexplain
–where条件运用y=5
zx@ORCL>select*fromtwherey=5;
ExecutionPlan
———————————————————-
Planhashvalue:1601196873
————————————————————————–
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
————————————————————————–
|0|SELECTSTATEMENT||1|15|80(2)|00:00:01|
|*1|TABLEACCESSFULL|T|1|15|80(2)|00:00:01|
————————————————————————–
PredicateInformation(identifiedbyoperationid):
—————————————————
1-filter(“Y”=5)
–where条件运用x=5
zx@ORCL>select*fromtwherex=5;
ExecutionPlan
———————————————————-
Planhashvalue:1594971208
————————————————————————————-
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
————————————————————————————-
|0|SELECTSTATEMENT||1|15|3(0)|00:00:01|
|1|TABLEACCESSBYINDEXROWID|T|1|15|3(0)|00:00:01|
|*2|INDEXRANGESCAN|IDX_T|1||2(0)|00:00:01|
————————————————————————————-
PredicateInformation(identifiedbyoperationid):
—————————————————
2-access(“X”=5)
但这并不完全排除运用索引。假如查询是selectx,yfromtwherey=5,优化器就会注意到,它不必全面扫描表来得到X或Y(x和y都在索引中),对索引自身做一个民快速的全面扫描会更合适,由于这个索引一般比底层表小得多。还要注意,仅CBO能运用这个访问途径。
zx@ORCL>selectx,yfromtwherey=5;
ExecutionPlan
———————————————————-
Planhashvalue:2497555198
——————————————————————————
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
——————————————————————————
|0|SELECTSTATEMENT||1|10|81(2)|00:00:01|
|*1|INDEXFASTFULLSCAN|IDX_T|1|10|81(2)|00:00:01|
——————————————————————————
PredicateInformation(identifiedbyoperationid):
—————————————————
1-filter(“Y”=5)
另一种状况下CBO也会运用T(x,y)上的索引,这便是索引跳跃式扫描。当且仅当索引的最前列(在上面的比如中最前列是x)只要很少的几个不同值,并且优化器了解这一点,跳跃式扫描(skipscan)就能很好地发挥作用。例如,考虑(GEMDER,EMPNO)上的一个索引,其中GENDER可取值有M和F,并且EMPNO是仅有的。关于以下查询:
select*fromtwhereempno=5;
能够考虑运用T上的那个索引选用跳跃式扫描方法来满足这个查询,这说明从概念上讲这个查询会如下处理:
select*fromtwhereGENDER=’M’andempno=5
unionall
select*fromtwhereGENDER=’F’andempno=5
它会跳跃式地扫描索引,以为这是两个索引:一个对应值M,另一个对应值F。
zx@ORCL>createtablet1asselectdecode(mod(rownum,2),0,’M’,’F’)gender,all_objects.*fromall_objects;
Tablecreated.
zx@ORCL>createindexidx_t1ont1(gender,object_id);
Indexcreated.
zx@ORCL>execdbms_stats.gather_table_stats(user,’T1′,cascade=>true);
PL/SQLproceduresuccessfullycompleted.
zx@ORCL>setautotracetraceonlyexplain
zx@ORCL>select*fromt1whereobject_id=42;
ExecutionPlan
———————————————————-
Planhashvalue:4072187533
————————————————————————————-
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
————————————————————————————-
|0|SELECTSTATEMENT||1|100|4(0)|00:00:01|
|1|TABLEACCESSBYINDEXROWID|T1|1|100|4(0)|00:00:01|
|*2|INDEXSKIPSCAN|IDX_T1|1||3(0)|00:00:01|
————————————————————————————-
PredicateInformation(identifiedbyoperationid):
—————————————————
2-access(“OBJECT_ID”=42)
filter(“OBJECT_ID”=42)
INDEXSKIPSCAN步骤告知Oralce要跳跃式扫描这个索引,查询GENDER值有改动的当地,并从那里开始向下读树,然后在所考虑的各个虚拟索引中查询OBJECT_id=42。假如大幅添加GENDER的可取值,如下:
zx@ORCL>altertablet1modifyGENDERvarchar2(2);
Tablealtered.
zx@ORCL>updatet1setgender=(chr(mod(rownum,1024)));
84656rowsupdated.
zx@ORCL>commit;
Commitcomplete.
zx@ORCL>execdbms_stats.gather_table_stats(user,’T1′,cascade=>true);
PL/SQLproceduresuccessfullycompleted.
zx@ORCL>setautotracetraceonlyexplain
zx@ORCL>select*fromt1whereobject_id=42;
ExecutionPlan
———————————————————-
Planhashvalue:1601196873
————————————————————————–
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
————————————————————————–
|0|SELECTSTATEMENT||1|101|344(1)|00:00:05|
|*1|TABLEACCESSFULL|T1|1|101|344(1)|00:00:05|
————————————————————————–
PredicateInformation(identifiedbyoperationid):
—————————————————
1-filter(“OBJECT_ID”=42)
状况2:
在运用selectcount(*)fromt查询(或类似的查询),并且在表T上有一个B*树索引。不过,优化器并不是核算索引条目,而是在全面扫描这个表(虽然索引比表要小)。在这种状况下,索引或许树立在一个允许有NULL值的列上。由于关于索引键完全为null的行不会树立相应的索引条目,所以索引中的行数或许并不是表中的行数。这儿优化器的挑选是对的,如若不然,假使它运用索引来核算行数,则或许会得到一个过错的答案。
zx@ORCL>desct;
NameNull?Type
———————————————————————————————————————————————————————————
XNUMBER
YNUMBER
ZCHAR(23)
zx@ORCL>selectcount(*)fromt;
ExecutionPlan
———————————————————-
Planhashvalue:2966233522
——————————————————————-
|Id|Operation|Name|Rows|Cost(%CPU)|Time|
——————————————————————-
|0|SELECTSTATEMENT||1|153(1)|00:00:02|
|1|SORTAGGREGATE||1|||
|2|TABLEACCESSFULL|T|99999|153(1)|00:00:02|
——————————————————————-
zx@ORCL>altertabletmodifyynotnull;
Tablealtered.
zx@ORCL>desct
NameNull?Type
———————————————————————————————————————————————————————————
XNUMBER
YNOTNULLNUMBER
ZCHAR(23)
zx@ORCL>selectcount(*)fromt;
ExecutionPlan
———————————————————-
Planhashvalue:2371838348
———————————————————————–
|Id|Operation|Name|Rows|Cost(%CPU)|Time|
———————————————————————–
|0|SELECTSTATEMENT||1|80(0)|00:00:01|
|1|SORTAGGREGATE||1|||
|2|INDEXFASTFULLSCAN|IDX_T|99999|80(0)|00:00:01|
———————————————————————–
.
状况3:
关于一个有索引的列,做以下查询:
select*fromtwherefunction(indexed_column)=value;
却发现没有运用indexed_colum上的索引。原因是这个列上运用了函数。假如是对indexed_column的值树立了索引,而不是对function(indexed_column)的值建索引。在此不能运用这个索引。假如愿意,能够别的对函数树立索引。
zx@ORCL>select*fromtwheremod(x,999)=1;
ExecutionPlan
———————————————————-
Planhashvalue:1601196873
————————————————————————–
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
————————————————————————–
|0|SELECTSTATEMENT||1000|34000|153(1)|00:00:02|
|*1|TABLEACCESSFULL|T|1000|34000|153(1)|00:00:02|
————————————————————————–
PredicateInformation(identifiedbyoperationid):
—————————————————
1-filter(MOD(“X”,999)=1)
zx@ORCL>createindexidx_t_font(mod(x,999));
Indexcreated.
zx@ORCL>execdbms_stats.gather_table_stats(USER,’T’,cascade=>true);
PL/SQLproceduresuccessfullycompleted.
zx@ORCL>select*fromtwheremod(x,999)=1;
ExecutionPlan
———————————————————-
Planhashvalue:4125918735
—————————————————————————————
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
—————————————————————————————
|0|SELECTSTATEMENT||100|3800|102(0)|00:00:02|
|1|TABLEACCESSBYINDEXROWID|T|100|3800|102(0)|00:00:02|
|*2|INDEXRANGESCAN|IDX_T_F|100||1(0)|00:00:01|
—————————————————————————————
PredicateInformation(identifiedbyoperationid):
—————————————————
2-access(MOD(“X”,999)=1)
状况4:
考虑以下状况,现已对一个字符钱树立了索引。这个列只包含数据数据。假如运用以下语法来查询:
select*fromtwhereindexed_colum=5;
注意查询中的数字5是常数5(而不是一个字符串),此刻就没有运用INDEXED_COLUMN上的索引。这是由于,前面的查询等价于以下查询:
select*fromtwhereto_number(indexed_column)=5;
咱们对这个列隐式地运用了一个函数,如状况3所述,这就会制止运用这个索引。
zx@ORCL>createtablet2(xchar(1)constraintt2_pkprimarykey,ydate);
Tablecreated.
zx@ORCL>insertintot2values(‘5′,sysdate);
1rowcreated.
zx@ORCL>commit;
Commitcomplete.
zx@ORCL>execdbms_stats.gather_table_stats(USER,’T2′,cascade=>true);
PL/SQLproceduresuccessfullycompleted.
zx@ORCL>explainplanforselect*fromt2wherex=5;
Explained.
zx@ORCL>select*fromtable(dbms_xplan.display);
PLAN_TABLE_OUTPUT
————————————————————————————————————————————————————————————
Planhashvalue:1513984157
————————————————————————–
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
————————————————————————–
|0|SELECTSTATEMENT||1|12|3(0)|00:00:01|
|*1|TABLEACCESSFULL|T2|1|12|3(0)|00:00:01|
————————————————————————–
PredicateInformation(identifiedbyoperationid):
—————————————————
1-filter(TO_NUMBER(“X”)=5)
Note
—–
-dynamicsamplingusedforthisstatement(level=2)
能够看到,它会全面扫描表;别的即使咱们对查询给出了以下提示:
zx@ORCL>explainplanforselect/*+index(t2t2_pk)*/*fromt2wherex=5;
Explained.
zx@ORCL>select*fromtable(dbms_xplan.display);
PLAN_TABLE_OUTPUT
————————————————————————————————————————————————————————————
Planhashvalue:3365102699
————————————————————————————-
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
————————————————————————————-
|0|SELECTSTATEMENT||1|10|2(0)|00:00:01|
|1|TABLEACCESSBYINDEXROWID|T2|1|10|2(0)|00:00:01|
|*2|INDEXFULLSCAN|T2_PK|1||1(0)|00:00:01|
————————————————————————————-
PredicateInformation(identifiedbyoperationid):
—————————————————
2-filter(TO_NUMBER(“X”)=5)
在此运用了索引,可是并不像咱们想像中那样对索引完结仅有扫描(UNIQUESCAN),而是完结了全面扫描(FULLSCAN)。原因从最后一行输出能够看出:filter(TO_NUMBER(“X”)=5)。这儿对这个数据库列运用了一个隐式函数。X中存储的字符串有必要转化为一个数字,之后才能与值5进行比较。在此无法把5转化为一个串,由于咱们的NLS(国家语言支撑)设置会操控5转化成串时的详细形式(而这是不确定的,不同的NLS设置会有不同的操控),所以应当把串转为数据。而这样相同(由于运用也函数),就无法运用索引来快速地查找这一行了。假如仅仅执行串与串的比较:
zx@ORCL>explainplanforselect*fromt2wherex=’5′;
Explained.
zx@ORCL>select*fromtable(dbms_xplan.display);
PLAN_TABLE_OUTPUT
————————————————————————————————————————————————————————————
Planhashvalue:3897349516
————————————————————————————-
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
————————————————————————————-
|0|SELECTSTATEMENT||1|12|1(0)|00:00:01|
|1|TABLEACCESSBYINDEXROWID|T2|1|12|1(0)|00:00:01|
|*2|INDEXUNIQUESCAN|T2_PK|1||1(0)|00:00:01|
————————————————————————————-
PredicateInformation(identifiedbyoperationid):
—————————————————
2-access(“X”=’5′)
14rowsselected.
不出所料,这会得到咱们期望的INDEXUNIQUESCAN,并且能够看到这儿没有运用函数。一定要尽或许地防止隐式转化。
还经常出现一个关于日期的问题,假如做以下查询:
select*fromtwheretrunc(date_col)=trunc(sysdate);
并且发现这个查询没有运用DATE_COL上的索引,为了处理这个问题,能够对trunc(date_col)树立索引,或许运用区间比较运算符来查询(或许这是更简单的做法)。下面来看对日期运用大于或小于运算符的一个比如。能够认识到以下条件:
trunc(date_col)=trunc(sysdate)
与下面的条件是相同的:
date_col>=trunc(sysdate)anddate_col
假如或许的话,假使谓词中有函数,尽量不要对数据库列运用这些函数。这样做不仅能够运用更多的索引,还能减少处理数据库所需的工作。运用转化的条件查询时只会核算一次TRUNC值,然后就能运用索引来查找满足条件的值。运用trunc(date_col)=trunc(sysdate)时,trunc(date_col)则有必要对整个表(而不是索引)中的每一行核算一次。
状况5:
另一种状况,假如运用了索引,实际上反而会更慢。Oracle(关于CBO而言)只会在合理地时分才运用索引。
zx@ORCL>createtablet3(x,ynull,primarykey(x))asselectrownumx,object_nameyfromall_objects;
Tablecreated.
zx@ORCL>execdbms_stats.gather_table_stats(USER,’T3’,cascade=>true);
PL/SQLproceduresuccessfullycompleted.
zx@ORCL>setautotracetraceonlyexplain
–运转一个查询查询相对较少的数据
zx@ORCL>selectcount(y)fromt3wherex<50;
ExecutionPlan
———————————————————-
Planhashvalue:1961899233
———————————————————————————-
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
———————————————————————————-
|0|SELECTSTATEMENT||1|5|2(0)|00:00:01|
|1|SORTAGGREGATE||1|5|||
|*2|INDEXRANGESCAN|SYS_C0017451|49|245|2(0)|00:00:01|
———————————————————————————-
PredicateInformation(identifiedbyoperationid):
—————————————————
2-access(“X”<50)
–运转一个查询查询相对较多的数据
zx@ORCL>selectcount(y)fromt3wherex<50000;
ExecutionPlan
———————————————————-
Planhashvalue:463314188
—————————————————————————
|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|
—————————————————————————
|0|SELECTSTATEMENT||1|30|117(1)|00:00:02|
|1|SORTAGGREGATE||1|30|||
|*2|TABLEACCESSFULL|T3|50000|1464K|117(1)|00:00:02|
—————————————————————————
PredicateInformation(identifiedbyoperationid):
—————————————————
2-filter(“X”<50000)
这个比如显示出优化器不一定会运用索引,并且实际上,它会做出正确的挑选。对查询调优时,假如发现你以为本该运用的某个索引实际上并没有用到,就不要冒然强制运用这个索引,而应该先做个测验,并证明运用这个索引后的确会加快速度(通过耗用时间和I/O次数来评判),然后再考虑让CBO就范(强制它运用这个索引)。总得先给出个理由吧。
状况6:
有一段时间没有分析表了。这些表起先很小,但比及查看时,它们现已增长得非常大。现在索引就有很有含义(虽然原先并非如此)。假如此刻分析这个表,就会运用索引。
假如没有正确的核算信息,CBO将无法做出正确的决议。
以上介绍了6种不运用索引的状况,归根到底原因通常便是“不能运用索引,运用索引会回来不正确的成果”,或许“不应该运用,假如运用了索引,性能会变得很糟糕”。

未经允许不得转载:IT技术网站 » oracle索引(oracle索引失效的几种情况)
分享到: 更多 (0)

评论 抢沙发

评论前必须登录!

 

志在指尖 用双手敲打未来

登录/注册IT技术大全

热门IT技术

C#基础入门   SQL server数据库   系统SEO学习教程   WordPress小技巧   WordPress插件   脚本与源码下载