Oracle索引介绍——关于Oracle索引的作用、具体分类、查看和修改

5 索引

5.1 作用

（1）快速存取数据。
　　（2）既可以改善数据库性能，又可以保证列值的唯一性。
　　（3）实现表与表之间的参照完整性
　　（4）在使用orderby、groupby子句进行数据检索时，利用索引可以减少排序和分组的时间。
Tips：
Oracle每次进行操作都会对要操作的数据块加锁。以防止多人操作容易产生的数据库锁等待甚至死锁现象。

5.2 分类

按照索引数据的存储方式分为：B树索引、位图索引、反向索引和基于函数的索引；
按照索引的唯一性分为：唯一索引和非唯一索引；
按照索引的个数分为：单列索引和复列索引。

5.2.1 建立B树索引

B树索引是Oralce数据库中最常用的索引类型（也是默认的），它是以B树结构组织并且存放索引数据的。默认情况下，B树索引中的数据是以升序方式排序的。
如果表包含的数据非常多，并且经常在WHERE字句中引用某列或某几个列，则应该基于该列或这几个列建立B树索引。
B树索引由根节点块、分支节点块和叶子节点块组成。其中主要数据都集中在叶子节点块所指向的数据行。

• 根节点块：索引顶级块，它包含指向下一级节点的信息。

• 分支节点块：它包含指向下一节点的信息。

• 叶子节点块：通常也称为叶子，它包含索引入口数据，索引入口包含索引列的值和记录行对应的物理地址ROWID。

在B树索引中无论用户要搜索哪个分支的叶块，都可以保证所经过的索引层次是相同的。Oracel采用这种方式的索引，可以确保无论索引条目位于何处，都只需花费相同的I/O即可获取它，这就是为什么被称为B树索引。

5.2.2 位图索引

参考百度百科：

1. 位图索引是一种使用位图的特殊数据库索引。
2. 主要针对大量相同值的列而创建(例如：类别，操作员，部门ID,库房ID等) ,
3. 索引块的一个索引行中存储键值和起止Rowid,以及这些键值的位置编码,
4. 位置编码中的每一位表示键值对应的数据行的有无.一个块可能指向的是几十甚至成百上千行数据的位置.
5. 这种方式存储数据,相对于B*Tree索引,占用的空间非常小,创建和使用非常快.
6. 当根据键值查询时,可以根据起始Rowid和位图状态,快速定位数据.
7. 当根据键值做and,or或 in(x,y,…) 查询时,直接用索引的位图进行或运算,快速得出结果行数据.
8. 当 select count(XX) 时,可以直接访问索引就快速得出统计数据.
9. 创建语法很简单,就是在普通索引创建的语法中index前加关键字bitmap即可,例如:

create bitmap index 位图索引名称 on 表名    ;

再次参考《索引：位图索引理解》

5.2.3 反向索引

参考《Oracle 反向索引（反转建索引） 理解》

语法：
create index PK_REV_TEST02 on TEST02(EMPNO) REVERSE;

5.2.4 基于函数的索引

参考《基于函数的索引+创建基于函数的索引》

1. 可以提高在查询条件中使用函数和表达式时查找的执行速度
2. 如果用户在自己模式中创建基于函数的索引，那么必须具有query rewrite系统权限；
   如果用户想要在其他模式中创建基于函数的索引，需要create any index和global query rewrite权限
3. 创建基于函数的索引时，oracle会首先对包含索引列的函数值或者表达式值进行运算求值，然后对求值后的结果进行排序，最后存储到索引中

通过执行计划查看使用函数索引前后的差别

5.2.5 唯一索引

创建唯一索引：
create unique index Idename on dept(dname) tablespace users;
唯一索引unique index和一般索引normal index最大的差异是在：
索引列上增加一层唯一约束。添加唯一索引的数据列可以为空，但是只要存在数据值，就必须是唯一的。

5.3 查看索引

select index_name,index_type,table_name,uniqueness from user_indexes where owner ='SCOTT';
索引是用于加速数据存储的数据库对象。

1. 所有索引
   dba_indxes 数据库的所有索引
   all_indexes 当前用户的可访问的所有索引
   user_indexes 当前用户的索引信息
2. 索引列
   dba_ind_columns
   all_ind_columns
   user_ind_columns
3. 索引位置与大小
   user_degements 索引数据保存在索引段中，索引段名与索引名相同
4. 函数索引
   DBA_IND_EXPRESSIONS
   USER_IND_EXPRESSIONS

5.4 删除索引

• 不需要时
• 大规模输入时，删后再重建

drop删除不适用于通过参数PRIMARY KEY或UNIQUE约束创建的索引，也不适用于删除系统表中的索引；

如果删除表，基于该表的所有索引自动删除