• 微信公众号:美女很有趣。 工作之余,放松一下,关注即送10G+美女照片!

【看懂执行计划】Oracle索引

开发技术 开发技术 1周前 (05-02) 7次浏览

Oracle 索引

简介

  索引是建立在表上的可选数据库对象,是一组排序后的的索引键。主要用于加快数据的检索,类似于书籍的目录,快速定位到目标数据。索引在物理上和逻辑上都是独立的,创建或删除索引对基表不会有影响。当对基表进行DML操作时Oracle会自动管理索引,无需手动处理。

索引分类

结构分类

  • 分区索引

      索引按范围(Range)或散列(Hash,Oracle 10g中引入)进行分区
      一个分区索引可能指向任何(或全部的)表分区。
    
  • B-Tree索引

      B-Tree索引是一个典型的树结构,通常包含根节点、分支节点、叶子节点
      包括正常索引或反转关键字索引
    
  • 位图索引

      位图索引主要针对大量相同值的列而创建
      位图索引不直接存储ROWID,而是存储字节位到ROWID的映射
      位图索引的空间占用明显小于B-Tree索引
      位图索引不适合经常更新的表
      关键字BitMap
    

逻辑分类

  • 单列索引

      索引列为单个字段
    
  • 组合索引

      索引列为多个字段,最多为32列,顺序自定义
    
  • 唯一索引

      索引列的值唯一,Oracle会自动在表的主键列上创建唯一索引,关键字UNIQUE INDEX
    
  • 非唯一索引

      索引列的值允许重复
    
  • 函数索引

      一列或多列上的基于函数表达式所创建的索引
      表达式不能出现聚合函数
      不能在LOB类型的列上创建
      创建时必须具有 QUERY REWRITE 权限
    
  • 反向键索引

      反向键索引反转索引列键值的每个字节,实现索引的均匀分配
      通常建立在值是连续增长的列上,使数据均匀地分布在整个索引上
      关键字REVERSE
    

创建索引原则

  1. 权衡索引个数与DML之间关系

     建立索引的目的是为了提高查询效率的
     但建立的索引过多,会影响插入、删除数据的速度
    
  2. 尽量将表和索引放在不同的表空间

     在读取数据时表与索引是同时进行的。
     表与索引在一个表空间里就会产生资源竞争,放在不同的表空间最佳。
    
  3. 创建索引会产生Redo信息和占用磁盘空间

     索引是数据库对象之一,需要分配磁盘空间去存储。
     创建索引会产生Redo信息,对于大表创建索引时可以设置不产生日志信息。
    
  4. 创建索引需根据具体的业务SQL

     Oracle根据具体的情况判断是否走索引。
     索引建在Where限制条件、表连接、需排序字段上。
    
  5. 唯一索引优先

     如果同时存在唯一性索引和非唯一索引,oracle将使用唯一性索引而忽略非唯一索引
    
  6. 经常用的字段放组合索引第一列

     组合索引只有它的第一列被where子句引用时,优化器才会使用该索引
    
  7. 限制表中索引的数量

     索引会占用物理空间,会随基表数据量的增大而增大;
     当对表中的数据进行DML时,索引也要动态的维护,降低了数据的维护速度
    
  8. 小表不要建索引

  9. 对于基数大的列适合建立B树索引,对于基数小的列适合建立位图索引

  10. 列中有很多空值,但经常查询该列上非空记录时应该建立索引

  11. LONG(可变长字符串数据,最长2G)和LONG RAW(可变长二进制数据,最长2G)列不能创建索引

索引可选项

  • NOSORT

    建立索引时会先对表记录排序再建立索引,当表数据量较多是会占用较多的时间。
    特殊情况下,我们就可以使用该参数加快建索引的速度。

CREATE INDEX IDX_TEMP_CHR_D_NOSORT ON TEMP_CHR_D (LIST_ID) NOSORT;
  • ONLINE

    数据库系统默认是不允许DML与创建索引同时进行的,ONLINE选项可以避免此类问题,但会延长建索引时间。

CREATE INDEX IDX_TEMP_CHR_D_NOSORT ON TEMP_CHR_D (LIST_ID) ONLINE;
  • NOLOGGING

    是否需要记录日志信息,一般用在在大型表上建索引,使用该参数,默认是记日志。

CREATE INDEX IDX_TEMP_CHR_D_NOSORT ON TEMP_CHR_D (LIST_ID) NOLOGGING;
  • COMPUTE STATISTICS

    参数会提示数据库建索引的同时,更新对应的统计信息。
    当数据修改量比较大的情况下,使用该选项有可能导致执行计划的不稳定。

CREATE INDEX IDX_TEMP_CHR_D_NOSORT ON TEMP_CHR_D (LIST_ID) COMPUTE STATISTICS;
  • PARALLEL

    增加并发,多服务进程创建索引,通常针对大表建索引使用

CREATE /*+ PARALLEL(2)*/ INDEX IDX_EMP_ENAME ON EMP (ENAME);

索引失效分析

  1. Where条件有不等于操作符(<>, !=)

  2. 限定条件中有对空值的判断(Null或Not Null)

  3. 非函数索引,Where条件中对索引列使用了函数

  4. 不匹配的索引数据类型

  5. 全模糊查询(’like ‘%aa’)

  6. Union替换Or

  7. 用EXISTS替代IN、用NOT EXISTS替代NOT IN

索引相关视图

视图 说明
dba_indexes all_indexes user_indexes 这类视图显示索引的基本信息,如索引名称、索引是否压缩存储、索引段的存储等信息以及使用dbms_stats包或analyze语句生成的统计信息
dba_ind_columns all_ind_columns user_ind_columns 这类视图显示了被索引列的信息
dba_ind_expressions all_ind_expresions user_ind_expressions 这类视图显示函数索引的函数语句
dba_ind_statistics all_ind_statistics user_ind_statistics 这类视图显示对索引的优化统计信息
index_stats index_histogram 显示最近一次使用analyze index… validate structure语句生成的统计信息
v$object_usage 存储由alter index … monitoring usage语句生成的索引使用信息

【看懂执行计划】Oracle索引


程序员灯塔
转载请注明原文链接:【看懂执行计划】Oracle索引
喜欢 (0)