Oracle 不使用索引的几种情况列举,本文介绍了几种不使用索引的情况,本文实验的数据库版本均为 11.2.0.4
情况 1:
我们在使用一个 B * 树索引,而且谓词中没有使用索引的最前列。
如果这种情况,可以假设有一个表 T,在 T(x,y) 上有一个索引。要做以下查询:select * from t where y=5。此时,优化器就不打算使用 T(x,y) 上的索引,因为谓词中不涉及 X 列。在这种情况下,倘若使用索引,可能就必须查看每个索引条目,而优化器通常更倾向于对 T 表做一个全表扫描。
- zx@ORCL > createtabletasselectrownumx,
- rownum + 1y,
- rownum + 2zfromdualconnectbylevel < 100000;
- Tablecreated.zx@ORCL > selectcount( * ) fromt;
- COUNT( * )----------99999zx@ORCL > createindexidx_tont(x, y);
- Indexcreated.zx@ORCL > execdbms_stats.gather_table_stats(user, 'T', cascade = >true);
- PL / SQLproceduresuccessfullycompleted.zx@ORCL > setautotracetraceonlyexplain--where条件使用y = 5zx@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 = 5zx@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)
但这并不完全排除使用索引。如果查询是 select x,y from t where y=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) 只有很少的几个不同值,而且优化器了解这一点,跳跃式扫描 (skip scan) 就能很好地发挥作用。例如,考虑 (GEMDER,EMPNO) 上的一个索引,其中 GENDER 可取值有 M 和 F,而且 EMPNO 是唯一的。对于以下查询:
select * from t where empno=5;
可以考虑使用 T 上的那个索引采用跳跃式扫描方法来满足这个查询,这说明从概念上讲这个查询会如下处理:
select * from t where GENDER='M' and empno=5
union all
select * from t where GENDER='F' and empno=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 > setautotracetraceonlyexplainzx@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)
INDEX SKIP SCAN 步骤告诉 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 > setautotracetraceonlyexplainzx@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:
在使用 select count(*) from t 查询 (或类似的查询),而且在表 T 上有一个 B * 树索引。不过,优化器并不是统计索引条目,而是在全面扫描这个表 (尽管索引比表要小)。在这种情况下,索引可能建立在一个允许有 NULL 值的列上。由于对于索引键完全为 null 的行不会建立相应的索引条目,所以索引中的行数可能并不是表中的行数。这里优化器的选择是对的,如若不然,倘若它使用索引来统计行数,则可能会得到一个错误的答案。
- zx@ORCL > desct;
- NameNull ? Type---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------XNUMBERYNUMBERZCHAR(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 > desctNameNull ? Type---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------XNUMBERYNOTNULLNUMBERZCHAR(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 * from t where function(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 * from t where indexed_colum=5;
注意查询中的数字 5 是常数 5(而不是一个字符串),此时就没有使用 INDEXED_COLUMN 上的索引。这是因为,前面的查询等价于以下查询:
select * from t where to_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)
在此使用了索引,但是并不像我们想像中那样对索引完成唯一扫描 (UNIQUE SCAN),而是完成了全面扫描(FULL SCAN)。原因从最后一行输出可以看出: 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.
不出所料,这会得到我们期望的 INDEX UNIQUE SCAN, 而且可以看到这里没有应用函数。一定要尽可能地避免隐式转换。
还经常出现一个关于日期的问题,如果做以下查询:
select * from t where trunc(date_col)=trunc(sysdate);
而且发现这个查询没有使用 DATE_COL 上的索引,为了解决这个问题,可以对 trunc(date_col) 建立索引,或者使用区间比较运算符来查询 (也许这是更容易的做法)。下面来看对日期使用大于或小于运算符的一个例子。可以认识到以下条件:
trunc(date_col)=trunc(sysdate)
与下面的条件是一样的:
date_col>= trunc(sysdate) and date_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 种不使用索引的情况,归根结底原因通常就是 "不能使用索引,使用索引会返回不正确的结果",或者 "不应该使用,如果使用了索引,性能会变得很糟糕"。
就爱阅读 www.92to.com 网友整理上传, 为您提供最全的知识大全, 期待您的分享,转载请注明出处。
来源: