ORACLE ---索引

ORACLE —索引

索引：

数据库中对象的一种，它记录了索引列中的数据以及对应的物理位置-ROWID。
索引建立时会获取指定列的数据，及其对应的ROWID，并自动地按照某种规则进行排序，索引与表是完全不同的两种对象，两者只是在使用上会有所关联。

索引的分类：

按照存储形式（即索引存储的内容） 1.B-TREE索引 （索引列原始数据+ROWID） 2.位图索引 （位图+ROWID） 3.反向键索引 （索引列原始数据的反向存储+ROWID） 4.基于函数的索引 （将索引列的原始数据经函数处理后存储+ROWID）

命名规范：

IND_TBNAME_COLNAME

按照存储形式

B-TREE索引（默认索引）

语法：

CREATE INDEX IND_NAME ON TB_NAME(COL_NAME);

例子：

CREATE INDEX IND_EMP_ENAME ON EMP(ENAME);

适用场景：列基数比较大的时候使用（行业、身高）
列基数：该列不重复数据的个数 COUNT(DISTINCT COL)

位图索引

语法：

CREATE BITMAP INDEX IND_NAME ON TB_NAME(COL_NAME) 

例子：
在EMP_20210330表中建立JOB的索引

CREATE BITMAP INDEX IND_JOB ON EMP_20210330(JOB) ;

适用场景：列基数比较小的时候使用（性别、婚姻状况）

反向键索引（反向索引）

语法：

CREATE INDEX IND_NAME ON TB_NAME(COL_NAME) REVERSE;

可以视作一种特殊的B-TREE索引，存储索引列的反向值
适用场景：原始数据分支不明显但反向数据分支明显的列（身高：集中在一米七一米八）

基于函数的索引

语法：

CREATE INDEX IND_NAME ON TB_NAME(FUNCTION(COL_NAME));

可以视作一种特殊的B-TREE索引，存储函数处理后的数据
适用场景：对某列进行筛选时经常需要配合函数使用（例如查找姓名中的首字母）

按唯一性

按唯一性 1.唯一索引 2.非唯一索引

唯一索引

索引列中不可能出现重复值
语法：

CREATE UNIQUE INDEX IND_NAME ON TB_NAME(COL_NAME);

注意点：

1）B-TREE索引可以建立唯一索引，位图索引不能建立唯一索引
2）如果在某列上建立了唯一约束或主键约束，ORACLE会自动在该列上建立一个同名的唯一索引

非唯一索引

索引列中可能出现重复值
语法：

CREATE INDEX IND_NAME ON TB_NAME(COL_NAME);

按列的个数

按列的个数 1.单列索引 2.复合索引

### 单列索引

基于一个列建立的索引
语法：

CREATE INDEX IND_NAME ON TB_NAME(COL_NAME);

复合索引(也称为联合索引）

基于两个或两个以上列建立的索引
语法：

CREATE INDEX IND_NAME ON TB_NAME(COL_NAME1,COL_NAME2...);

简单的执行计划使用方法：

将要执行的SQL语句放到explain plan窗口运行，
INDEX即为索引扫描，FULL为全盘扫描

删除索引

语法：

DROP INDEX IND_NAME;

禁用索引

语法：

ALTER INDEX IND_NAME UNUSABLE;

重建索引

语法：

ALTER INDEX IND_NAME REBUILD;

注意

【插入完成后统一维护索引】比【一边插入一边维护】的速度要快！

数据字典

所有索引

SELECT INDEX_NAME,     --索引名称
       INDEX_TYPE,     --索引类型
       TABLE_NAME,     --表名
       UNIQUENESS,     --是否唯一
       STATUS,         --索引状态  VALID 可用的 UNUSABLE 不可用的
       TABLESPACE_NAME,--表空间
       LOGGING         --是否记录日志
  FROM USER_INDEXES
 WHERE 1=1
   AND INDEX_NAME = 'IND_EMP_DEPTNO'
   AND TABLE_NAME = 'EMP';

索引列

SELECT INDEX_NAME,   --索引名称
       TABLE_NAME,   --表名
       COLUMN_NAME,  --列名
       COLUMN_POSITION, --字段在索引中的位置
       DESCEND       --排序方式  默认ASC
  FROM USER_IND_COLUMNS
 WHERE INDEX_NAME ='IND_EMP_UPENAME';

索引函数

SELECT INDEX_NAME,
       TABLE_NAME,
       COLUMN_EXPRESSION
  FROM USER_IND_EXPRESSIONS
 WHERE INDEX_NAME ='IND_EMP_UPENAME';