已索引

官方文档

Oracle: Concepts ---> 3 Indexes and Index-Organized Tables

什么是索引

索引是与表相关的一个可选结构，在逻辑上和物理上都独立于表的数据，能优化查询。数据库通常自动维护索引。
如果 SQL 语句仅访问被索引的列，那么只需从索引中读取数据，而不用读取表，如果该语句同时还要访问除索引列之外的列，那么，会使用索引块中存放的指针(ROWID)来查找相应的表块。

在不考虑其它因素干扰（如表和索引的高水位线）的情况下，从一张大表中找出一条记录：

  • 通过 ROWID 访问记录（不会过索引），性能是最好的；
  • 通过索引访问次之，索引的本质是一个小表（从小表找到 ROWID，再据此回表）；
  • 全表扫描最糟糕。


下面是 Oracle 关于索引的描述：

• Is a schema object
• Can be used by the Oracle server to speed up the retrieval of rows by using a pointer
• Can reduce disk input/output (I/O) by using a rapid path access method to locate data quickly
• Indexes are logically and physically independent of the table that they index. This means that they can be created or dropped at any time, and have no effect on the base tables or other indexes.
• Is used and maintained automatically by the Oracle server

Oracle 索引结构

Oracle B-tree 索引结构：

btree_index_oracle.gif

上图来自于 Oracle 官方文档，这个索引是在 employees 表的 department_id 列上创建的非唯一索引。

索引的生成过程可以从最底层开始理解比较容易，将索引列的键值与 ROWID 有序地存放在第一个索引块中，当第一个索引块存满后，继续存放第二个索引块，此时，这两个索引块需要有上一级的一个索引块来做指向，产生 branch blocks（最上面的索引块我们又称为 ROOT blocks）。

Oracle 位图索引结构：

Oracle Database uses a B-tree index structure to store bitmaps for each indexed key. For example, if jobs.job_title is the key column of a bitmap index, then the index data is stored in one B-tree. The individual bitmaps are stored in the leaf blocks.

Assume that the jobs.job_title column has unique values Shipping Clerk, Stock Clerk, and several others. A bitmap index entry for this index has the following components:

  • The job title as the index key
  • A low rowid and high rowid for a range of rowids
  • A bitmap for specific rowids in the range：

Conceptually, an index leaf block in this index could contain entries as follows:

Shipping Clerk,AAAPzRAAFAAAABSABQ,AAAPzRAAFAAAABSABZ,0010000100
Shipping Clerk,AAAPzRAAFAAAABSABa,AAAPzRAAFAAAABSABh,010010
Stock Clerk,AAAPzRAAFAAAABSAAa,AAAPzRAAFAAAABSAAc,1001001100
Stock Clerk,AAAPzRAAFAAAABSAAd,AAAPzRAAFAAAABSAAt,0101001001
Stock Clerk,AAAPzRAAFAAAABSAAu,AAAPzRAAFAAAABSABz,100001

The same job title appears in multiple entries because the rowid range differs.

Assume that a session updates the job ID of one employee from Shipping Clerk to Stock Clerk. In this case, the session requires exclusive access to the index key entry for the old value (Shipping Clerk) and the new value (Stock Clerk). Oracle Database locks the rows pointed to by these two entries—but not the rows pointed to by Accountant or any other key—until the UPDATE commits.

The data for a bitmap index is stored in one segment. Oracle Database stores each bitmap in one or more pieces. Each piece occupies part of a single data block.

索引三大特性

1.索引高度低：所以可以从海量数据中快速获取少量数据

2.索引本身存储列值，如果查询只取索引列的值，则不用回表，可优化类似 count(*)、SUM(索引列)、AVG(索引列) 等聚合语句。

注意：
1、因为索引不能存储空值，对索引列进行聚合运算，聚合运算本身就忽略空值，所以在索引列上不需要 NOT NULL 约束或者在语句中使用 "where 索引列 is not null"。但奇怪的是，对于MAX/MIN 怎么都可以用到索引，但是对于 SUM/AVG 则需要非空才能用到索引，记住就好，应该是优化器的缺陷导致。
2、因为索引不能存储空值，所以在 select count(*) 中要使用 "where 索引列 is not null"，或者将索引列的属性设置为 "not null"，这样，通过索引统计出的行数才与 count(*) 等价，Oracle 才会走索引。

3.索引本身有序：可以优化类似 MAX/MIN 及 ORDER BY 等语句。

注意，Oracle 不能同时在索引树的两段寻找最大值和最小值，可以通过两个内联视图的方式来分别查询出最大值和最小值。

索引的不足：开销和失效

开销：

  1. 热块竞争：最新的数据一般是在最右边，查询时一般也是查询的最新的数据
  2. 回表开销：尽可能避免回表，如果不可避免，则需要关注聚合因子（集群因子）是否过大
    --- 表和索引两者的排列顺序相似度很高，则聚合因子比较低；表和索引两者的排列顺序相似度差异明显，则聚合因子比较高。
  3. 建立开销： 索引不仅会消耗存储空间，其创建和rebuild都会有内存消耗和排序的开销，且会锁表（online 方式可以避免）。
    --- 注意：drop 索引时也会锁表，12c 在 drop 索引时也可以使用 online 关键字。
  4. 更新开销：对索引列的 DML 会导致索引的维护开销。其实，插入一般不会随着记录的增加越来越慢，但是有索引的时候就会表越大维护越困难。所以，只有在有索引的情况下，才会考虑插入速度的优化。分区表的局部索引由于只需要更新局部分区的索引，所以索引开销会比全局索引和普通索引小。

索引过多对三种更新语句的影响：

  • 对INSERT语句负面影响最大，有百害而无一利，只要有索引，插入就慢，越多越慢！
  • 对DELETE语句来说，有好有坏，在海量数据库定位删除少数记录时，这个条件列是索引列显然是必要的，但是过多列有索引还是会影响明显，因为其他列的索引也要因此被更新。在经常要删除大量记录的时候，危害加剧！
  • 对UPDAET语句的负面影响最小，快速定位少量记录并更新的场景和DELETE类似，但是具体修改某列时却有差别，不会触及其他索引列的维护。

失效：

1.索引本身未失效但是被抑制了或者走索引代价更高（逻辑失效）

  • 走索引代价比走全表扫描代价还大
  • 索引列发生类型转换
  • 索引列使用了函数和运算。例如典型的是时间类型列查询通病 trunc(hire_date)。

2.索引本身失效了（物理失效），例如 move 表，某些分区操作等

  • move 操作会导致索引失效，如果是外键上的索引失效，会进一步导致主外键更新困难，父表更新频繁被锁。
  • shrink 表不会导致索引失效，但索引可能会被弃用而走全表扫描，因为索引没有被 shrink，其高水位没有被释放。
  • 从回收站还原的表的索引名称会保存回收站中的名称（约束会丢失），不过，即使 SQL 语句中使用了 hint，Oracle 还是会识别到的。

查询失效的普通表索引及分区表的全局索引

select index_name, table_name, tablespace_name, index_type
from dba_indexes
where status='UNUSABLE';

查询失效的分区表局部索引

select t1.index_name, t1.partition_name, t1.global_stats, t2.table_name, t2.table_type
from dba_ind_partitions t1, dba_indexes t2
where t2.index_name=t1.index_name
and t1.status='UNUSABLE';

索引的分类

Oracle 官方文档上将索引分为 B-tree 索引、Bitmap and bitmap join 索引、Function-based 索引、Application domain 索引。
B-tree 索引有如下子类型：Index-organized tables、Reverse key indexes、Descending indexes、B-tree cluster indexes。

我们经常把索引比作字典目录，其实就是在说 B树 或者 B+树 索引，我们打交道的 90% 以上都是 B树 或者 B+树索引。

创建/重建索引

Oracle 的主键和唯一约束会自动创建 unique index，我们也可以建表时创建或者建表后手动创建索引。

Oracle在建表后创建索引的语法：

SQL> CREATE [UNIQUE] [BITMAP] INDEX index
ON table(column[, column]...) tablespace xxx;

Oracle也可以在建表时创建索引：

SQL> CREATE TABLE NEW_EMP
(employee_id NUMBER(6) PRIMARY KEY USING INDEX
(CREATE INDEX emp_id_idx ON NEW_EMP(employee_id)),
first_name VARCHAR2(20),
last_name VARCHAR2(25));

重建索引的方法：

SQL> ALTER INDEX index_name REBUILD [ONLINE]
[NOLOGGING] [COMPUTE STATISTICS] [parallel n];

SQL> alter index xxx noparallel;

其中：

  • ONLINE 使得在重建索引过程中，用户可以对表进行 DML，但是如此会导致创建索引时间变长（更新操作会阻塞索引创建操作）
  • NOLOGGING 表示在重建过程中产生最少的重做条目
  • COMPUTE STATISTICS 表示在重建过程中就生成 oracle 优化器所需的统计信息，避免了索引重建之后再进行 analyze 或 dbms_stats 来收集统计信息
  • 为了加块创建索引或者重建索引的速度，我们一般会在语句的最后加上 parallel n 来使用并行，但注意这样会把并行设置到索引中去，导致后续的查询都调用并行，引起资源争用，引发性能故障，所以使用并行地方式创建或者重建索引完成后，要去掉其并行属性

注意：创建索引前检查一下表的统计信息是否陈旧，如果表的统计信息不准确，在创建完索引后，一定要及时收集统计信息，否则，添加索引后，尽管解决了问题 SQL 的性能问题，但可能因为陈旧的统计信息和新的索引而导致其它 SQL 语句走错误的执行计划。

rename table 和 remap_table 对索引的影响

对表重命名后，索引名不会发生变化，但索引仍然有效。
从管理规范的角度，建议表重命名后，索引也做一个重命名，索引重命名后，仍然有效。

remap_table 只会修改表名，不会修改索引名，impdp 没有针对索引的 remap 语法。
在使用 impdp 导入表时要注意索引是否冲突的问题，如果索引冲突，有如下几种方法避免冲突：
1、impdp 之前先将原表的索引重命名
2、在 expdp 之前，先将索引重命名
3、impdp 时，排除索引，然后手动创建索引
4、直接导入，报索引已经存在的错误，不管它，然后手工创建一个索引。

什么时候创建索引(PPT)

A column contains a wide range of values：建议创建索引
A column contains a large number of null values(索引不包含空值)：建议创建索引
One or more columns are frequently used together in a WHERE clause or a join condition：建议创建索引
The columns are not often used as a condition in the query：不建议创建索引
The table is large and most queries are expected to retrieve less than 2% to 4% of the rows in the table：建议创建索引
The table is small or most queries are expected to retrieve more than 2% to 4% of the rows in the table：不建议创建索引
The table is updated frequently：不建议创建索引
The indexed columns are referenced as part of an expression：不建议创建索引
索引个数需控制：超过5个以上的索引，在表的记录很大时，将会极大地影响该表的更新。
无任何索引的表需加以关注

如下语句查询索引个数超过5个的表

select table_name, count(*)
from user_indexes
group by table_name
having count(*) >=5
order by count(*) desc;

如下语句查询没有创建索引的表

select table_name from user_tables 
where table_name not in (
select table_name from user_indexes);

外键列上要考虑创建索引

外键未建索引，将有可能导致两个严重的问题：

1.两表关联查询时性能低下

利用父表查询子表是相当常见的。如果频繁地运行以下查询，就会发现没有索引会使查询速度变慢：

select * 
from departments d,employees e 
where e.department_id=d.department_id 
and d.department_id=:x;

另外，如果外键有 on delete cascade 且没有加索引，那么删除父表中的每一行时都会对子表做一次全表扫描。这个全表扫描可能是不必要的，而且如果从父表删除多行，父表中每删除一行就要扫描仪一次子表。此时的故障现象一般表现为：查询指定条件的父表记录很快，删除指定条件的父表记录却很慢。

2.容易产生（死）锁

据Tomas 的经验，导致死锁的头号原因是外键未加索引（第二号原因是表上的位图索引遭到并发更新）
如果外键上没有索引，当更新了父表的主键或删除了父表中的一行时，子表会被锁住。

我们可以通过如下查询来找出未建索引的外键（请使用使用业务用户查询）：

set lines 400;
set pages 800;
col table_name for a40;
col constraint_name for a40;
col columns for a60;
select table_name,
constraint_name,
cname1 || nvl2(cname2, ',' || cname2, null) ||
nvl2(cname3, ',' || cname3, null) ||
nvl2(cname4, ',' || cname4, null) ||
nvl2(cname5, ',' || cname5, null) ||
nvl2(cname6, ',' || cname6, null) ||
nvl2(cname7, ',' || cname7, null) ||
nvl2(cname8, ',' || cname8, null) columns
from (select b.table_name,
b.constraint_name,
max(decode(position, 1, column_name, null)) cname1,
max(decode(position, 2, column_name, null)) cname2,
max(decode(position, 3, column_name, null)) cname3,
max(decode(position, 4, column_name, null)) cname4,
max(decode(position, 5, column_name, null)) cname5,
max(decode(position, 6, column_name, null)) cname6,
max(decode(position, 7, column_name, null)) cname7,
max(decode(position, 8, column_name, null)) cname8,
count(*) col_cnt
from (select substr(table_name, 1, 30) table_name,
substr(constraint_name, 1, 30) constraint_name,
substr(column_name, 1, 30) column_name,
position
from user_cons_columns) a,
user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by b.table_name, b.constraint_name) cons
where col_cnt > ALL
(select count(*)
from user_ind_columns i
where i.table_name = cons.table_name
and i.column_name in (cname1, cname2, cname3, cname4, cname5,
cname6, cname7, cname8)
and i.column_position <= cons.col_cnt
group by i.index_name);

单列索引的设计建议

以下只是针对B*树单字段索引的设计建议，也是索引设计的第一条规范。
（1）分析SQL语句中的约束条件字段
（2）如果约束条件字段不固定，建议创建针对单字段的普通B+树索引
（3）选择可选性最高的字段建立索引
（4）如果是多表连接SOL语句，注意被驱动表（drived table)的连接字段是否需要创建索引
（5）通过多种SQL分析工具，分析执行计划并以量化形式评估效果 （set timing on & autotrace）

哪些列创建索引：
在查询中经常用到，高选择性。
生产中一个经常犯的错误是给类别、类型等列创建索引。解决方案是以类型分区，复合索引。

复合索引的设计建议

（01）分析SQL语句中的约束条件字段。
（02）如果约束条件字段比较固定，则优先考虑创建针对多字段的普通B*树复合索引。例如同时涉及到月份、纳税人识别号、税务机关代码3个字段的条件，则可以考虑建立一个复合索引。
（03）如果单个字段是主键或唯一字段，或者可选性非常高的字段，尽管约束条件字段比较固定，也不一定要建成复合索引，可建成单字段索引，降低复合索引开销。
（04）在复合索引设计中，需首先考虑复合索引的第一个设计原理：复合索引的前缀性（prefixing)。即在SQL语句中，只有将复合索引的第一个字段作为约束条件，该复合索引才会启用。
（05）在复合索引设计中，其次应考虑复合索引的可选性（Selectivity或Cardinality)。即按可选性高低，进行复合索引字段的排序。例如上述索引的字段排列顺序为；纳税人识别号、税务机关代码、月份。
（06）如果条件涉及的字段不固定，组合比较灵活，则分别为月份、纳税人识别号、税务机关代码3个字段建立索引。
（07）如果是多表连接SQL语句，注意是否可以在被驱动表（drived table）的连接字段与该表的其他约束条件字段上创建复合索引。
（08）通过多种SQL分析工具，分析执行计划并以量化形式评估效果。
（09）适用于单列查询返回多（可选性低），组合查询返回少（可选性高）的场景
（10）一般情况下，如果建索引可以避免回表，也可以考虑对多列创建组合索引，不过组合索引列不宜过超过4个，否则这个索引本身就会很大，不一定高效。
（11）组合索引最佳顺序一般是将等值查询的列置前
（12）仅等值无范围查询时，组合的顺序不影响性能
（13）如果有组合索引(a,b)，则 a 列上的单独索引则显得多余

我们可以通过如下查询将超过4个字段的组合索引查找出来：

select table_name, index_name, count(*)
from user_ind_columns
group by table_name, index_name
having count(*) >=4
order by count(*) desc;

组合索引与查询条件

SQL> create index idx_union on t(object_type,object_id,owner);
SQL> select * from t where object_type='VIEW' and OWNER='CY'
SQL> select * from t where object_type='VIEW' and OBJECT_ID IN (20,21,22) AND OWNER='CY';

三列组合索引的特点是，第二列脱离第一列无意义，第三列脱离第二列无意义。
上面这种 X AND Y AND Z 的表达式，只要有 索引第一列参与，就会走索引。此即复合索引的第一个原理：前缀性。
前缀性决定了是否会使用到索引。

复合索引的第二个原理：可选性
可选性决定了复合索引中如何排列字段顺序：Oracle建议将字段值多的排在前面。
字段值多，可选性强，定位的记录越少，查询效率越高。

组合索引与排序

SQL> create index idx_x on cyt(owner,object_type);
SQL> create index idx_y on cyt(owner desc, object_type asc)
SQL> select * from cyt order by owner desc, object_type asc;

如果是创建的第一个索引，用不上索引，走全表索引
如果是创建的第二个索引，能用上索引，使用了索引的排序特性

like 模糊匹配与索引

like CY%是可以用到索引的，而 %CY 或者 %CY% 则使用不上索引。
对于 %CY% ，我们可以使用Oracle全文检索技术。
对于 %CY，我们可以 reserver() 函数来变通处理：

SQL> create index idx_x on cyt(reverse(last_name));
SQL> .... where reverse(last_name) like reverse('%CY');

索引监控

使用 alter index xxx monitoring usage 打开索引的使用监控，通过查询 v$object_usage 来分析索引是否有使用。一般说来，在2个月内从未使用到的索引是多余的索引，可以考虑删除。

可以使用虚拟索引，在不真正创建索引的情况下分析创建索引的话，是否会用上。

虚拟索引

虚拟索引就是创建一下索引，但是并不真正为其分配段空间，然后运行 SQL 语句以判断是否会使用该索引。

方法很简单，打开一个隐藏参数，然后在平时正常创建索引的语句后面加上 nosegment 即可：

  1. alter session set "_use_nosegment_indexes"=true;
  2. create index idx_x on cyt(object_id) nosegment;

接下来用 explain plan for 的方式查看是否用到索引（并不实际执行 SQL）：

SQL> explain plan for select * from t where object_id=1;
SQL> select * from table(dbms_xplan.display());

上面的分析结果会显示使用了索引。

不过虚拟索引毕竟不是真实的，SQL 语句在实际运行的时候是不会使用该索引的：

SQL> alter session set statistics_level=all;
SQL> select * from t where object_id=1;
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

虚拟索引的几个重要特点：

  1. 无法执行 alter index
  2. 不能创建和虚拟索引同名的真实索引
  3. 可以创建和虚拟索引包含相同列但不同名的真实索引

位图索引

位图索引可以存放空值。

即席查询：一种多维度的报表查询。
例如，在人口普查中，我们经常会需要做一些关于性别、年龄范围、出生地等多维度的分析统计：

SQL> select*from t where coll=xxx and col2=xxx and col3=xxxx and col4=xxx ...)

位图索引存储的是 bit 值 0 和 1，故非常适合与或非的运算（即席查询场景）以及 count(*) 统计。

位图索引一般创建在重复度高的列上，例如性别列：

SQL> select * from cyt where col1=xxx and col2=yyy and col3=zzz; 
--- 可以分别测试不创建索引，创建普通组合索引，每个列上创建位图索引时，上面语句的执行效果
SQL> select count(*) from cyt;

再如，员工表的 job 列的值很少，在其上创建位图索引：

SQL> create bitmap index job_bitmap on emp(job);
SQL> select count(*) from emp where job='CLERK' or job='MANAGER'

使用 autotrace 就可以看到优化器使用了位图索引。

另外，频繁更新的列上也不适合建位图索引，会产生严重的锁等待（死锁）。

因此位图索引的适合场景要满足两个条件：
第一，位图索引列大量重复；
第二，该表极少更新。
这两个条件非常重要，切记。

B 树索引和位图索引的适用环境：

B 树适合情况位图适合情况
大表，返回行数<5%同左
经常使用 where 子句查询的列同左
离散度高的列离散度低的列
更新键值代价低更近键值代价高
逻辑AND效率高逻辑OR效率高
用于OLTP用于OLAP

我们可以通过 dba_indexes 的 index_type 字段值是否为 "BITMAP" 来监控是否存在位图索引。

函数索引

我们可以使用函数索引来实现只对列的部分记录创建索引。

例如，100万员工的公司，大部分员工都是没有分红的（N），只有少数几个人有分红（Y），我们只想对记录是Y的情况创建索引：

SQL> create index id_status on  t (Case when status= 'Y' then 'Y' end);
SQL> select * from t where (case when status='Y' then 'Y' end)='Y';
     --- 写法不能变,如果是select * from t where status='N'将无效

再来一个例子：
假如一个师范学院的学生表，其中95%左右的学生是女性，如果我们要在gendar上建立索引，用常规的方法创建索引是不科学的，
因为如果查询女生信息，由于绝大多数学生是女生，所以一般应用都是全表扫描，既然如此为何还要在索引中保存女生的信息呢？

如果建立以下索引
create index idx_student_gendar_fun on student_info(case when gendar =0 then null else gendar end);
因为btree索引不保存null信息，男学生人数有很少，所以这个索引又小，又能在查询难学生信息的时候用上索引。

如果要基于自定义函数建索引，需要在函数定义中使用 DETERMINISTIC 关键字，否则会报 "ORA-30553：函数不能确定" 错误。

例如：

create or replace function f_minus1(i int)
    return int DETERMINISTIC
    ....

在自定义函数代码更新时，这个对应的函数索引也要重建，否则数据无法随着自定义函数代码的变化而变化，查询出来的数据可能是错误的。

关于函数索引的几点注意事项：

  • The Oracle Server uses the index only when that particular function is used in a query.
  • The QUERY_REWRITE_ENABLED initialization parameter must be set to TRUE for a function-based index to be used.
  • The Oracle Server treats indexes with columns marked DESC as function-based indexes.(叶节点中的键值排列默认是升序的)
  • 表达式中不能出现聚合函数
  • 不能在LOB类型的列上创建

  • 建议尽量少用函数索引，原因如下：

    • 函数索引是需要维护的（维护成本），其维护成本高于普通索引。
    • 函数索引的计算值可能大于原字段值，将消耗更多的索引存储空间。

我们可以通过 dba_indexes 的 index_type 字段值是否为 "FUNCTION-BASED NORMAL" 来监控是否存在函数索引。
遇到函数索引时，我们应该尽量想一想，能否转换成等价的其它 SQL 写法。例如：

下面是非常典型的时间查询通病，会普通索引被抑制：

select * from employees 
where trunc(hiredate)>=TO_DATE('2013-12-14', 'YYYY-MM-DD')
and trunc(hiredate)<=TO_DATE('2013-12-15', 'YYYY-MM-DD');

改写成如下写法，就能使用上索引，变得高效:

select * from employees 
where hiredate>=TO_DATE('2013-12-14', 'YYYY-MM-DD')
and hiredate<TO_DATE('2013-12-15', 'YYYY-MM-DD')+1;

反向键索引(Reverse)

反向键索引将字节倒置后组织键值。可以防止叶节点出现热块现象（考点）。
反向索引就是将正常的键值头尾调换后再进行存储，比如原值是“1234”，将会以“4321”形式进行存储，这样做可以高效地打散正常的索引键值在索引叶块中的分布位置。对于反向键索引（可用来避免索引热点块问题），范围查询索引会失效，等值查询不会失效。通常，使用数据时（常见于批量插入操作）都比较集中在一个连续的数据范围内，那么在使用正常的索引时就很容易发生索引叶子块过热的现象，严重时将会导致系统性能下降。当RAC环境中几个节点访问数据的特点是集中和密集，索引热点块发生的几率就会很高。如果系统对范围检索要求不是很高的情况下可以考虑使用反向索引技术来提高系统的性能。因此该技术多见于RAC环境，它可以显著的降低索引块的争用。

SQL> create index idx_t on t(x) reverse;
SQL> alter index idx_t rebuild noreverse;

在 dba_indexes 视图中，其索引类型为 NORMAL/REV

压缩索引（11g新）

压缩（Compress)：重复键值只存储一次，就是说重复的键值在叶块中就存一次，后跟所有与之匹配的rowid字符串。
SQL> create index comp_idx on emp1(sal) compress;

索引碎片

由于对基表做DML操作，便导致对索引表块的自动更改操作，尤其是基表的delete操作会引起index表的index entries 的逻辑删除，注意，只有当一个索引块中的全部 index entry都被删除了，这个块才会被收回。如果update 基表索引列，则索引块会发生entry delete，再entry insert，这都些动作都可能产生索引碎片。

频繁对索引字段进行DELETE、UPDATE操作，会对索引造成大量碎片，从而极大地影响索引的使用效率，并造成索引/O的增加。

1.索引碎片分析

执行如下语句可监测索引的碎片情况：

SQL> analyze index ind_1 validate structure;
SQL> select name,HEIGHT,PCT_USED,DEL_LF_ROWS/LF_ROWS from index_stats

analyze index <索引名> validate structure online;
select name,del_lf_rows_len,lf_rows_1en,(de1_1f-rows_1en/1f_rows_len)*100 from index_stats;

表中：索引碎片率（%）=（de1_1f-rows_1en/1f_rows_len)*100。如果索引碎片率超过20%，则Oracle认为索引碎片已经非常严重。建议DBA编写一个检测所有索引碎片率的脚本，定期运行该脚本，保持对索引碎片率的监测。

在 Oracle 文档里并没有清晰的给出索引碎片的量化标准，Oracle 建议通过 Segment Advisor（段顾问）解决表和索引的碎片问题（053课程会涉及），如果你想自行解决，可以通过查看 index_stats 视图，当以下三种情形之一发生时，说明积累的碎片应该整理了（仅供参考）。
1、HEIGHT >=4
2、PCT_USED< 50%
3、DEL_LF_ROWS/LF_ROWS>0.2

2.索引碎片整理

Oracle进行索引碎片的处理包括两种策略。

（1）重建索引（Rebuild)

SQL>alter index <索引名>rebuild online；
SQL> alter index ind_1 rebuild [online] [tablespace name];

联机重建索引通常比删除后再重建要更实用，Oracle9i和10g一直提供联机索引重建功能，但由于涉及到使用表的排他锁，通常一段时间内其他用户的DML操作需要等待。在 Oracle11g 中有了改进，实现了最终意义上的联机索引重建(rebuild index online)的特性，它重新设计了锁的模式，因此容许DML操作不受干扰。

（2）压缩索引（Coalesce)

SQL> alter lindex <索引名> coalesce；

索引融合，比rebuild动作轻，可以合并一些块中的index entries。

如前所述，索引碎片是在日常的运行过程中产生的，应用软件开发人员是无法在开发环境下进行索引碎片分析和整理的。该工作应是DBA日常运行维护中的工作之一。建议采取定期索引重建（Rebuild)的策略，例如可在每个周末或者每天夜晚对删除操作频繁表的索引进行在线重建工作。

索引不可见（invisible)（11g 新特性）

在 11g 里，Oracle提供了一个新特性来降低直接删除索引或禁用索引的风险，那就是索引不可见（Index Invisible)。
我们可以在创建索引时指定 invisible 属性或者用 alter 语句来修改索引为 invisible(visible)（考点）

SQL> create table test (id int,name char(10));
SQL> create index test_idx on test(id)
SQL> alter index test_idx invisible;
SQL> select index_name,status,VISIBILITY from user_indexes;

注意：索引被设定为 invisible 后，视图 user_indexes 中 status 字段仍然是 VALID，实际上就是指该索引对于优化器不可见，而索引的正常更新仍然会由有 Oracle 自动完成的。而索引 unusable，保存索引定义，索引段被回收（非sys用户），不删除索引，也不再更新索引。

SQL> alter index test_idx unusble;
SQL> select index_name,status,VISIBILITY from user_indexes;

索引被设定为 unusable 后，视图 user_indexes 中 status 字段是 unusable(考点）。

分区索引

见文章《分区表与分区索引》

索引相关视图

USER_INDEXES;
USER_IND_PARTITIONS
USER_IND_COLUMNS

-- By 许望（RHCA、OCM、VCP）
最后修改：2024 年 01 月 12 日 11 : 42 AM
© 允许规范转载
如果觉得我的文章对你有用，请随意赞赏