首页 > 代码库 > 41.oracle索引,分析索引,索引碎片整理

41.oracle索引,分析索引,索引碎片整理

概述

索引分为B树索引和位图索引。我们主要研究B树索引,B树索引如下图(图片源自网络):技术分享

  索引是与表相关的一个可选结构,在逻辑上和物理上都独立于表数据,索引能优化查询,不能优化DML,oracle自动维护索引,频繁的DML操作反而会赢钱大量的索引卫华。

  如果sql语句仅仅访问被索引的列,那么数据库只需从索引中读取数据,而不会读取表;如果该语句还要访问未被索引的列,那么数据库会使用rowid来查找表中的行,通常,为检索表数据,数据库以交换方式先读取索引块,然后读取对应的表。

  索引的目的是减少IO

  1. 大表,返回的行数<5%
  2. 经常使用where子句查询的列
  3. 离散度高的列
  4. 更新键值代价低
  5. 逻辑AND、OR效率高
  6. 查看索引建在哪表哪列
    select * from user_indexes;
    select * from user_ind_columns;

索引的使用

  1. 唯一索引
    create unique index empno_idx on emp(empno);
  2. 一般索引
    create index empno_idx on emp(empno);
  3. 组合索引
    create index job_deptno_idx on emp(job,deptno);
  4. 函数索引:查询时必须用到这个函数才会使用到。
    create index fun_idx on emp(lower(ename));

索引问题

查看执行计划:set autotrace traceonly explain;

  索引碎片问题:由于基表做DML操作,导致索引表块的自动更改操作,尤其是基表的delete操作会引起index表的index_entries的逻辑删除,注意只有当一个索引块中的全部index_entry都被删除了,才会把这个索引块删除,索引对基表的delete、insert操作都会产生索引碎片问题。

  在oracle文档中并没有清晰的给出索引碎片的量化标准,oracle建议通过segment advisor(片段顾问)解决表和索引的碎片问题(后面的课程会提及),如果你想自行解决,可以通过查看 index_stats视图,当以下三种情形之一发生时,说明积累的碎片应该整理了(经验之谈)

  1. height >= 4 (概述中图示索引树的高度是3)
  2. pct_used < 50%
  3. del_lf_rows/lf_row > 0.2

实操:

先建表,建索引

技术分享

然后插入1000000条数据

技术分享

 

分析索引:analyse index t_idx validate structure;

查看分析结果:select name,height,pct_used,del_lf_rows/lf_rows from index_stats;

技术分享

 

然后我们来破坏一下索引,重新查看一下分析结果

技术分享

可以看到最后一个指标变了。说明产生一些碎片了。那么需要进行整理:

技术分享

技术分享

可见最后一个指标正常了(低于0.2)。

41.oracle索引,分析索引,索引碎片整理