### Oracle索引详解
#### 一、索引介绍
##### 1.1 索引的创建语法
在Oracle数据库中，索引是一种用于提高数据检索速度的数据结构。索引的创建语法通常遵循以下格式：
```sql
CREATE [UNIQUE | BITMAP] INDEX <schema>.<index_name>
ON <schema>.<table_name> (<column_name>|<expression> ASC|DESC, <column_name>|<expression> ASC|DESC, ...)
[TABLESPACE <tablespace_name>]
[STORAGE <storage_settings>]
[LOGGING | NOLOGGING]
[COMPUTE STATISTICS]
[NOCOMPRESS | COMPRESS <nn>]
[NOSORT | REVERSE]
[PARTITION | GLOBAL PARTITION <partition_setting>];
```
- **UNIQUE | BITMAP**：指定索引类型。`UNIQUE`表示唯一值索引，用于确保索引列中的所有值都是唯一的；`BITMAP`表示位图索引，适用于包含大量重复值且值种类较少的列，如性别列（男性/女性），这样的索引非常节省空间但只适用于简单的等值查询。
- **<column_name>|<expression> ASC|DESC**：可以创建单列或复合索引（多列索引）。当使用表达式时，创建的是基于函数的索引。复合索引可以加速涉及多个列的查询。
- **TABLESPACE**：指定存储索引的表空间。为了提高效率，通常建议将索引存放在与表不同的表空间中。
- **STORAGE**：设置表空间的存储参数，例如初始大小、增量等。
- **LOGGING | NOLOGGING**：控制是否记录索引更新操作的日志信息。对于大型表，使用`NOLOGGING`可以提高效率但牺牲了一定程度的数据安全性。
- **COMPUTE STATISTICS**：创建索引时自动收集统计信息，这有助于优化器选择更高效的执行计划。
- **NOCOMPRESS | COMPRESS <nn>**：决定是否使用键压缩技术。键压缩可以减少索引空间的占用，尤其是在键值重复较多的情况下效果显著。
- **NOSORT | REVERSE**：`NOSORT`表示索引按表中的顺序存储，而`REVERSE`则按相反的顺序存储索引值，这有助于优化某些特定类型的查询。
- **PARTITION | GLOBAL PARTITION <partition_setting>**：允许在分区表上创建分区索引。局部分区索引仅存储于对应的表分区中，全局分区索引则覆盖整个表。
##### 1.2 索引的特点
- **保证唯一性**：通过创建唯一性索引，可以确保表中每一行数据的某一列或几列组合具有唯一性。
- **提高查询速度**：索引能显著提升数据检索的速度，尤其是对于大型表来说更为明显。
- **优化表间连接**：在表连接操作中，索引能够帮助加速连接过程，尤其在实现数据的引用完整性方面表现突出。
- **加速分组和排序**：在使用`GROUP BY`或`ORDER BY`进行数据检索时，索引可以有效减少处理时间。
- **系统性能提升**：通过使用索引，系统能够在查询过程中利用优化隐藏器，从而提高整体性能。
##### 1.3 索引的不足之处
- **创建和维护成本高**：索引的创建及维护都需要消耗时间和资源，这会随数据量的增长而增加。
- **占用物理空间**：每个索引都会占用一定的物理空间，对于空间有限的环境可能成为问题。
- **影响数据维护速度**：在对表进行增删改操作时，索引也需要相应地更新，这可能会降低数据维护的速度。
##### 1.4 应该创建索引的列的特点
- **常用搜索列**：在频繁用于搜索操作的列上创建索引，可以大幅提高搜索速度。
- **主键列**：主键列通常应创建唯一性索引，以强制其唯一性并优化数据的排列结构。
- **连接列**：在经常参与连接操作的列（通常是外键）上创建索引，可以加快连接的速度。
- **范围查询列**：在经常用于范围查询的列上创建索引，因为索引已预先排序，可以加速这类查询。
- **排序查询列**：在需要频繁排序的列上创建索引，利用索引的排序特性，提高排序效率。
- **条件过滤列**：在经常出现在`WHERE`子句中的列上创建索引，以加快条件判断速度。
##### 1.5 不应该创建索引的列的特点
- **极少使用的列**：对于很少被查询的列，创建索引并不会带来明显的性能提升，反而可能导致维护成本上升。
- **值种类较少的列**：如果列中值的种类很少（例如二元分类），即使创建索引也很难获得实质性的查询速度提升。
