Oracle索引介绍（简述、创建、维护等）

1.概述

索引是一种数据库对象，它用于加快查询数据的速度。索引是在表的一个或多个列上创建的，它会在后台维护一个有序的数据结构，通常是B-tree或位图索引，以便快速查找和检索数据。

常见的索引类型：

• B-tree索引：最常见的索引类型,适用于大多数查询场景。以B-tree的数据结构存储索引键和对应的行ID。
• 位图索引：适用于基数较低的列，如性别、状态等。以位图的形式存储索引键和行ID，查询效率高。
• 函数索引：对表达式或函数应用索引，而不是直接对列应用索引。
• 复合索引：在多个列上创建索引，可以加速涉及这些列的查询。
• 反向键索引：将索引键倒序存储，可以提高范围查询的性能。

优点：

• 提高数据的查询速度
• 减少磁盘IO
• 创建唯一索引还能保证表中每一行数据的唯一性

缺点：

• 当数据发生增删改时，会更新索引，产生的额外性能开销就越大，增删改的执行速度就越慢，维护操作较麻烦
• 随着数据的更改索引可能会失效
• 会额外的占用磁盘空间

2.索引的创建

## 创建普通索引
create index <index_name> on <表名>(column_name);

## online方式（允许其他会话修改索引字段）
create index <index_name> on <table_name>(column_name) online;

## 指定表空间
create index <index_name> on <table_name>(column_name) tablespace <tablespace_name>;

## 创建复合索引
create index <index_name> on <table_name>(column_name1,column_name2,column_name3);

## 创建唯一索引
create unique index <index_name> on <table_name>(column_name1,column_name2);

## 创建位图索引
create bitmap index <index_name> on <table_name>(column_name);

## 创建反向键索引
create bitmap index <index_name> on <table_name>(column_name) reverse;

## 创建基于函数的索引
create index <index_name> on <table_name>(lower(column_name));

## 创建分区索引
本地：
create index <index_name> on <table_name>(column_name) local (partition partition_name);

全局：
create index <index_name> on <table_name>(column_name) global (partition partition_name);

3.索引的管理

## 删除索引
drop index <index_name>;

## 重建索引
alter index <index_name> rebuild;
alter index <index_name> rebuild online;

## 重命名索引
alter index <current_index_name> rename to <new_index_name>;

## 使索引不可见
alter index <index_name> invisible;

## 合并索引碎片
alter index <index_name> coalesce;

## 查看表上存在的索引
select index_name from dba_indexes where table_name='<table_name>';

## 检查索引状态
普通索引：
select index_name,table_name,status,tablespace_name from user_indexes;

分区索引：
select index_name,partition_name,status,tablespace_name from user_ind_partitions;

## 分析索引
analyze index <index_name> validate structure;

## 查看分析结果
select name,height,pct_used,del_lf_rows/lf_rows,(DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 from index_stats where name='I_ID';