2023年11月29日发(作者：)

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));

5. 。。。

索引问题

查看执⾏计划：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）。