索引

索引

索引是建立在数据库表中的一列或多列用来加速访问表中数据的辅助对象。
使用索引有如下优点：

• 索引可以大大加快检索数据的速度
• 使用唯一索引可以保证数据库表中每一行数据的唯一性
• 通过索引可以加快表与表之间的连接
• 在使用分组和排序子句进行数据检索时，使用索引可以显著的减少查询分组和排序的时间

索引原理

在Oracle数据表中，每一张表都有一个ROWID伪列，这个ROWID是用为唯一标志一条记录所在物理未知的id号，每一列对应的ROWID值是固定而且唯一的。一旦数据存入数据库就确定，不会在对数据库表操作的过程中发生改变，只有在表发生移动或表空间变化等操作产生物理位置变化是，才会发生改变。
检索数据时，比如使用WHERE子句按指定条件检索数据时，Oracle将首先对索引中的列进行快速搜索，由于索引列已经过排序，因此可以使用各种快速的搜索算法，这样就可以避免对全表进行扫描。在找到所要检索的数据后，通过ROWID在表中读取具体的记录值,

创建索引

索引的创建分为两种方式：

• 自动创建：在定义主键约束或唯一约束是，Oracle自动在相应的约束列上建立唯一索引，Oracle不推荐人为的创建唯一索引
• 手动创建：用户可以在其它列上创建非唯一索引

在oracle中，索引根据其组织形式又可以分为多种类型

• 单列索引：索引基于单个列所创建
• 复合索引：索引基于多个列所创建
• Btree索引：这是Oracle默认使用的索引，Btree索引可以是单列索引或复合索引、唯一索引或非唯一索引，索引按Btree结构组织并存放索引数据
• 位图索引：为索引列的每个取值创建一个位图，对表中每行使用1位（bit，取值为0或1）来表示该行是否包含该位图的索引列的取值
• 函数索引：索引的取值不直接来自列，而是来自包含有列的函数或表达式

CREATE [UNIQUE] | [BITMAP] INDEX index_name ON table_name
ON table_name ([column1 [ASD|DESC],column2
[ASC|DESC],...]|[express])
[TABLESPACE tablespace_name]
[PCTFREE n1]
[STORAGE (INITIAL n2)]
[nologging]
[noline]
[nosort];

这些参数的含义：

• UNIQUE：表示唯一索引，默认情况下，不适用该项
• BITMAP：表示创建位图索引，默认情况下，不适用该项
• PCTFREE：指定索引在数据块中的空闲空间。对于经常插入数据的表，英爱为表中索引指定一个较大的空闲空间
• NOLOGGING：表示在创建做阴的过程中不产生任何重做日志信息。默认情况下，不适用该选项
• ONLINE：表示在创建或重建所饮食，允许对表进行DML操作。默认情况下，不适用该选项
• NOSORT：默认情况下，使用该选项。Oracle在创建所饮食对表中记录进行排序。如果表中数据已经是按该索引顺序排序的，则可以使用该选项

要使用CREATE INDEX语句创建索引，需要具有以下两种权限

• CREATE INDEX：当在用户所在的方案中创建索引时需要具备的权限
• CREATE ANY INDEX：要在其它用户方案中创建索引时需要具备的权限

在创建所饮食，会对全表进行扫描，对索引列的数据进行排序，为索引分配存储空间，将索引的定义信息保存到数据字典中。
如果在CREATE INDEX时，不指定任何索引类型参数，默认创建的就是标准的Bree索引，例如：

CREATE INDEX idx_emp_empnoname ON emp_index(ename, empno); --Btree索引
CREATE INDEX idx_emp_job ON emp_index(job);  				--Btree索引
CREATE BITMAP INDEX idx_emp_job_bitmap ON emp_index(job);  --位图索引
CREATE INDEX idx_emp_name ON emp(UPPER(ename));            --函数索引

注：由于索引的创建会带来一定的性能开销，因此必须注意创建索引的一些基本原则。

• 小表不许需要建立索引，比如emp表只有十行记录，可以不建立索引
• 对于大表而言，如果经常查询的记录书目少于表中总记录数目的15%，可以创建索引
• 对于大部分列值不重复的列可以建立索引
• 对于基数大的列适合建立Btree索引，而杜宇基数小的列适合建立位图索引
• 对于列中有许多空值，但经常查询所有的非空值记录的列，应该建立索引
• LONG和LONG RAW 列不能创建索引
• 经常进行连接查询的列上应该创建索引
• 在使用CREATE INDEX语句创建查询时，将最常查询的列放在其他列前面
• 维护索引需要开销，特别是对表进行插入和删除操作时，因此要限制表中索引的数量。
• 在表中插入数据后创建索引。如果在装载数据之前创建了索引，那么当插入每行时，Oracle都必须更改每个索引

修改索引

在创建好索引之后，如发现索引的命名不符合命名规范，需要重命名，或者是索引在使用一段时间后需要重新索引，可以使用ALTER INDEX语句

重命名索引

当对已经创建的索引的名称不满意时，可以通过ALTER TABLE …RENAME TO 语句更改索引的名称

ALTER INDEX idx_emp_empnoname RENAME TO idx_ename_empno;

在ALTER INDEX 语句的后面，跟上索引的名称（可以使用方案名.索引名称），在RENAME TO 语句后面，跟上要进行重命名的最终名称。

合并和重建索引

表在使用一段时间后，由于频繁的对标进行操作，而每次对表的更新必然伴随着索引的改变。因此，在索引中会产生大量的碎片，从而降低索引的使用效率。可以通过以下两种方式来清理碎片

• 合并索引：合并索引并不会改变索引的物理组织结构，只是简单的将Btree叶子节点中的存储碎片合并在一起
• 重建索引：重新创建一个新的索引，删除原来的索引

重建索引和合并索引都能消除索引碎片，但二者在使用上有明显的区别

• 合并索引不能将索引移动到其他表空间，但重建索引可以
• 合并索引代价较低，无需额外存数空间，但重建索引恰恰相反
• 合并索引只能在Btree的同一子树中合并，不改变树的的高度，但重建索引重建整个Btree，可能会降低树的高度

分配和释放索引空间

在插入或加载数据时，如果表中具有索引，会同时在索引中添加数据，如果素偶因短空间不足，为了能够向索引段添加数据将导致动态的扩展索引段，从而降低了数据的装载速度。为了避免这个问题，可以在执行装载或大批量插入之前为索引段分配足够的空间。

ALTER INDEX idx_ename_empno ALLOCATE EXTENT(SIZE 200K);

上述语法将idx_ename_empno索引段的索引扩容200KB，以便能容纳所插入的索引数据
当索引段占用了过多的空间，而实际上用不了这样多的空间时，可以通过DEALLOCATE UNUSED来释放多余的空间

ALTER INDEX idx_ename_empno DEALLOCATE UNUSED;

上述语句执行后，将释放未使用的索引空间

删除索引

删除索引使用DROP INDEX语句。在当前用户中删除索引时，需要具备DROP INDEX系统权限，如果时其他用户方案中删除索引，则需要DROP ANDY INDEX系统权限。

DROP INDEX idx_ename_empno;

对于唯一性索引，如果是在定义月说是由Oracle自动建立的，则可以通用是哟个DISABLE禁用约束或删除约束的方法来删除对应的索引
当以下情况发生时，需要从数据库中移除索引

• 索引不再需要时，应该删除以释放所占用的空间
• 索引没有经常使用，只是极少数查询会使用到该索引时
• 如果索引中包含损坏的数据块，或者索引碎片过多时，应该删除索引，然后重新建立索引
• 当使用SLQ*Loader给表中装载大量数据时，系统也会给标的索引增加数据，为了加快装载速度，可以在装在前删除索引，在装载之后重新建立索引