不用索引的常见情况

外键是否应该加索引？
  外键未加索引可能是导致死锁的主要原因：因为无论是更新父表主键，或者是删除一个父记录，都会在子表上加一个表锁，这就会不必要地锁定更多的行，而影响并发性。在使用能自动生成SQL来修改表的某个工具时，就经常遇到这个问题。在以下情况下，外键未加索引也表现得很糟糕。
    如果有一个 on delete cascade,而且没有对子表建索引。例如。EMP 是 DEPT的子表，delete from dept where deptno=10会级联至emp，如果emp中的deptno没有加索引，就会导致对emp执行一个全表扫描，这咱完全扫描可能是不必要的，而且如果从父表删除了多行，对于删除的每一个父行，都会把子表扫描一次。
    从父表查询子表时，还是考虑EMP/DEPT表的例子，在deptno上下文查询emp表相当常见，如果频繁地执行以下查询来生成一个报告或某个结果：
   select * from dept,emp   where emp.deptno=dept.deptno and dept.dname=:x ;
会发现，如果没有索引会使查询减慢。
　什么情况下外键不需要加索引？
　　未删除父表中的行
　　不论是有意还是无意，总之未更新父表的唯一／主键值
　　不会从父表联结到子表，或者直接的讲，外键列不支持子表的一个重要的访问路径，而且你在谓词中没有使用这些外键列从子表中选择数据
满足这三条可以不加索引
　　为什么没有使用索引？
 原因很多，这里只有几种常见的原因。
情况1.
  在使用一个B*树索引，而且谓词中没有使用索引的最前列，如果是这种情况，可以假设有一个表T，在T（x,y)上有一个索引，我们要做以下查询：
select * from t where y=5;
此时，优化器就不打算使用T(x,y)上的索引，因为谓词中不涉及X列。在这种情况下，倘若使用索引，可能就必须查看每一个索引条目，而优化器通常更倾向于对T表做一个全表扫描，但这并不完全排除使用索引，如果查询是
select x,y from t where y=5
优化器就会注意到，它不必全面扫描表来得到X或Y，对索引本身做一个快速的扫描会更合适，因为这个索引一般比底层表小得多，还要注意，公CBO能使用这个访问路径。
  另一种情况下CBO也会使用T(x,y)上的索引，这就是索引跳跃式扫描，当且仅当索引的最前列，只有很少的几个不同值，而且优化器了解这一点，跳跃式扫描(skip scan) 就能很好地发挥作用，例如，考虑(gender,empno)上的一个索引，其中gender可取值有m和f,而且empno是惟一的.
  select * from where enpno=5;
可以考虑使用T上的那个索引采用跳跃式扫描方法来满足这个查询，说明从概念上讲这个查询会如下处理：
 select * from t where gender='m' and empno =5
union all
select * from t where gender='f' and empno = 5;
它会跳跃式地扫描索引，以为这是两个索引：一个对应值M，一个对应值F。在查询计划中可以很容易地看出这一点。
 create table t
as
select decode(mod(rownum,2),0,'m','f') gender,all_objects.*
from all_objects
/
create index t_idx on t(gender,object_id)
/
begin
dbms_stats.gather_table_stats
(user,'T',cascade=>true);
end;
/
set autotrace traceonly  explain
select * from t t1 where object_id=42;

execution plan
-----------------------------------------------------
0 select statement ptimizer=all_rows(cost=4 card=1 bytes=95)
1 0 table access (by index rowid) of 't' (table) (cost=4 card=1 bytes=95)
2 1 index (skip scan) of 't_idx' (index) (cost=3 card=1)

index skip scan步骤告诉oracle要跳跃式扫描这个索引，查找gender值有改变的地方，并从那里开始向下读树，然后在所考虑的各个虚拟索引中查找object_id=42，如果大幅增加gender的可取值，如下：
   update t
set gender = chr(mod(rownum,256));

begin
  dbms_stats.gether_table_stats(user,'T',cascade=>true);
end;
/
会看到oracle不再认为跳跃式扫描是一个可行的计划，优化器本可以去检查256个小索引，但是它更倾向于执行一个全表扫描来找到所需要的行：
  set autotrace traceonly explain
  select * from t t1 where object_id=42;

execution plan
-----------------------------------------------------
0  select statement ptimizer=all_rows(cost=158 card=1 bytes=95)
1 0  table access(full) of 't' (table) (cost=158 card=1 bytes=95)

情况2.
  在使用一个select count(*) from t 查询或类似查询，而且在表T上有一个B*树索引，不过，优化器并不是统计索引条目，而是在全面扫描这个表（虽然索引比表要小），在这各情况下，索引可能建立在一些允许有NULL值的列上，由于对于索引键完全为null的行不会建立相应的索引条目，所以索引中的行数可能并不是表中的行数，这里优化器的选择是对的，这个时候如果使用索引，得到的答案是会是错的。

  情况3.
   对一个有索引的列
  select * from t where f(indexed_column)=value
却发现没有使用 f(indexed_column)的值建索引，在此不能使用索引，如果愿意可以另外对函数建立索引(查建见基本函数的索引).

 情况4.
   我们已经对一个字符列建立了索引，这个列只包含数据值数据
select * from t where indexed_column=5
此时没有使用indexed_column上的索引，因为隐式的应用了一个转换函数，会禁止索引，使用dbms_xplan，这个包在9.2及以上版本中可用（使用autotrace可能很容易地查看计划，但是看不到谓词）
  create table t(x char(1) constraint t_pk primary key,y date);
insert into t values('5',sysdate);
delete from plan_table;
explain plan for select * from t where x=5;
select * from table(dbms_xplan.display);

plan_table_output
--------------------------------
plan hash value:749696591
-------------------------------------------------------------------------
| id  | operation         | name | rows | bytes | cost(%cpu) | time     |
-------------------------------------------------------------------------
| 0   | select statement  |      | 1    | 12    | 2 (0)      | 00:00:01 |
| * 1 | table access full | T    | 1    | 12    | 2 (0)      | 00:00:01 |
--------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
1 - filter(to_number("x"=5)
可以看到执行了全表扫描：即使用对查询给出如下提示：
explain plan for select /*+ INDEX(t t_pk) */ * from t where x=5;
select * from table(dbms_xplan.display);

plan_table_output
--------------------------------
plan hash value:749696591
-------------------------------------------------------------------------
| id  | operation                   | name | rows | bytes | cost(%cpu) | time     |
-------------------------------------------------------------------------
|  0  | select statement            |      | 1    | 12    | 34 (0)      | 00:00:01 |
|  1  | table access by index  rowid| T    | 1    | 12    | 34 (0)      | 00:00:01 |
| * 2 | index full scan             | T_PK | 1    |       | 26 (0)      | 00:00:01 |
--------------------------------------------------------------------------
predicate information (identified by operation id):
---------------------------------------------------
2 - filter(to_number("x"=5)
这里对索引并没有完成我们想像中的惟一扫描（unique scan）,而是使用了全表扫描(full scan).可以从最后一句看出: filter(to_number("x"=5)，数据库应用了一个隐式函数，X中存储的字符串必须转换为一个数字，之后才能进行比较，在此无法将5转换为一个字符，因为NLS（国家语言支持）设置会控制5转换成串时的具体形式，所以应当把串转换为数字，这样一来便无法使用索引来快速查找这一行了。
  还经常出现关于日期的问题
-- find all records for today
  select * from t where trunc(date_col) = trunc(sysdate);
而且发现这个查询没有使用date_col上的索引，为了解决这个问题，可以对trunc(date_col)建立索引，或者使用区间比较运算符来查询。来看看对日期使用大于和小于运算符的一个例子。
 trunc(date_col) = trunc(sysdate)
与下面的条件是一样的:
 select * from t
   where date_col >= trunc(sysdate)
    and date_col < trunc(sysdate+1)
这就把所有函数都移到等式的右边，这样我们就能使用date_col上的索引了（而且与where trunc(date_col）=trunc(sysdate)的效果完全一样).
如果可能的话，倘若谓词中有函数，尽量不要对数据库列应用这些函数，这样做不权可以使用更多的索引，还能减少处理数据库所需的工作，在上一种情况下，使用以下条件时：
where date_col >= trunc(sysdate)
and date_col < trunc(sysdate+1)
查询只会计算一次trunc值，然后就能使用索引来查找满足条件的值。使用trunc(date_col)=trunc(sysdate)时，trunc(date_col)则必须对整个表（而不是索引）中的每一行计算一次。
 
情况5.
  此时如果用了索引，实际上反而会更慢，索引总会使查询更快吗？？？？？oracle(对CBO而言)只会在合理地时候才使用索引
    create table t(x,y,primary key (x))
as
select rownum x,object_name from all_objects
/
begin
dbms_stats.gether_table_stats(user,'T',cascade =>true);
end;
/
set autotrace on explain
select count(y) from t where x < 50;

Execution plan
------------------------------------------------------------
0  select statement timizer=all_rows (cost=3 card=1 bytes=28)
1 0  sort(aggregate)
2 1   table access (by index rowid) of 't' (table) (cost=3 card=41 bytes=1148)
3 2    index (range scan) of ]Sys_c009167' (index (unique)) (cost=2 card=41)
此时优化器会很乐意地使用索引，不过，如果估计通过索引获取的行数超过了一个阈值就会观察到优化器将执行全表扫描：
select count(y) from t where x < 15000;
Execution plan
------------------------------------------------------------
略
显示优化器不一定会使用索引，事实上它会做出正确的选择，采用跳跃式索引，对查询调优时，如果发现该用索引的地方没有用索引，应该先做测试，证明使用这个索引会加快速度。

情况6.
  一段时间没有分析表，这些表开始很小，查看时已经增长得很大，如果此时分析表就会使用索引，如果没有正确的统计信息，CBO将无法做出正确的决定。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8750110/viewspace-198286/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/8750110/viewspace-198286/