oracle 索引使用场景

Oracle  索引使用总结
前段时间公司在严查慢sql,总结一下索引的优缺点，使用场景,以及之前踩过的坑。
一、好处
1.提高查询速度
2.利用索引的唯一性来控制记录的唯一性
3.降低查询中分组和排序的时间
二、坏处
1.存储索引占用磁盘空间
2.执行数据修改操作(INSERT、UPDATE、DELETE)产生索引维护
索引是提高数据查询最有效的方法，也是最难全面掌握的技术，因为正确的索引可能使效率提高10000倍，而无效的索引可能是浪费了数据库空间，甚至大大降低查询性能。
3.存储索引的磁盘空间
4.执行数据修改操作(INSERT、UPDATE、DELETE)产生的索引维护
5.在数据处理时回需额外的回退空间。
三、索引按功能分类
1.唯一索引
  唯一索引有两个作用，一个是数据约束，一个是数据索引，其中数据约束主要用来保证数据的完整性，唯一索引产生的索引记录中每一条记录都对应一个唯一的ROWID。
2.主关键字索引
   主关键字索引产生的索引同唯一索引，只不过它是在数据库建立主关键字时系统自动建立的。
3.一般索引
  一般索引不产生数据约束作用，其功能主要是对字段建立索引表，以提高数据查询速度。
4.索引按索引对象分类
  单列索引(表单个字段的索引)
  多列索引(表多个字段的索引)
  函数索引(对字段进行函数运算的索引)
  建立函数索引的方法:
   create index 收费日期索引 on GC_DFSS(trunc(sk_rq))
   create index 完全客户编号索引 on yhzl(qc_bh||kh_bh)
  在对函数进行了索引后，如果当前会话要引用应设置当前会话的query_rewrite_enabled为TRUE。
   alter session set query_rewrite_enabled=true
  注:如果对用户函数进行索引的话，那用户函数应加上 deterministic参数，意思是函数在输入值固定的情况下返回值也固定。例:
   create or replace function trunc_add(input_date date)return date deterministi
   as
   begin
   return trunc(input_date+1);
   end trunc_add;
5.应用索引的扫描分类
  INDEX UNIQUE SCAN(按索引唯一值扫描)
   select * from zl_yhjbqk where hbs_bh='5420016000'
  INDEX RANGE SCAN(按索引值范围扫描)
   select * from zl_yhjbqk where hbs_bh>'5420016000'
   select * from zl_yhjbqk where qc_bh>'7001'
  INDEX FAST FULL SCAN(按索引值快速全部扫描)
   select hbs_bh from zl_yhjbqk order by hbs_bh
   select count(*) from zl_yhjbqk
   select qc_bh from zl_yhjbqk group by qc_bh
6.什么情况下应该建立索引
 （1）表的主关键字
      自动建立唯一索引
        如ctoc_order esc_orderid
（2）表的字段唯一约束
     ORACLE利用索引来保证数据的完整性
    如return_order的RORDER_ID       
（3）直接条件查询的字段
     在SQL中用于条件约束的字段
       如zl_yhjbqk(用户基本情况)中的qc_bh(区册编号)
       select * from zl_yhjbqk where qc_bh=’7001’
（4）查询中与其它表关联的字段
     字段常常建立了外键关系
       如zl_ydcf(用电成份)中的jldb_bh(计量点表编号)
       select * from zl_ydcf a,zl_yhdb b where a.jldb_bh=b.jldb_bh and b.jldb_bh=’540100214511’
（5）查询中排序的字段
     排序的字段如果通过索引去访问那将大大提高排序速度
       select * from zl_yhjbqk order by qc_bh(建立qc_bh索引)
       select * from zl_yhjbqk where qc_bh='7001' order by cb_sx(建立qc_bh+cb_sx索引，注:只是一个索引，其中包括qc_bh和cb_sx字段)
（6）查询中统计或分组统计的字段
       select max(hbs_bh) from zl_yhjbqk
       select qc_bh,count(*) from zl_yhjbqk group by qc_bh
7.什么情况下应不建或少建索引
（1）表记录太少
     如果一个表只有几十条记录，采用索引去访问记录的话，那首先需访问索引表，再通过索引表访问数据表，一般索引表与数据表不在同一个数据块，这种情况下ORACLE至少要往返读取数据块两次。而不用索引的情况下ORACLE会将所有的数据一次读出，处理速度显然会比用索引快。
     如表zl_sybm(使用部门)一般只有几条记录，除了主关键字外对任何一个字段建索引都不会产生性能优化，实际上如果对这个表进行了统计分析后ORACLE也不会用你建的索引，而是自动执行全表访问。如:
     select * from zl_sybm where sydw_bh='5401'(对sydw_bh建立索引不会产生性能优化)
（2）经常插入、删除、修改的表
     对一些经常处理的业务表应在查询允许的情况下尽量减少索引，如zl_yhbm，gc_dfss，gc_dfys，gc_fpdy等业务表。
（3）数据重复且分布平均的表字段
     假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。
（4）经常和主字段一块查询但主字段索引值比较多的表字段
     如gc_dfss(电费实收)表经常按收费序号、户标识编号、抄表日期、电费发生年月、操作标志来具体查询某一笔收款的情况，如果将所有的字段都建在一个索引里那将会增加数据的修改、插入、删除时间，从实际上分析一笔收款如果按收费序号索引就已经将记录减少到只有几条，如果再按后面的几个字段索引查询将对性能不产生太大的影响。