oracle数据库，索引

文章所涉及的表只有oracle数据库自带的emp表

一，索引

当表的数据量比较大时，查询操作会比较耗时。建立索引是加快查询速度的有效手段。

索引的分类

1，按存储形式分（索引中存储的内容不同）

1）B+树索引(索引列原始数据+ROWID)

语法：CREATE INDEX IND_NAME ON TB_NAME(COL_NAME);

CREATE INDEX IND_EMP_SAL ON EMP(SAL);

适用场景：列基数比较大的时候使用（行业、身高）

列基数：该列不重复数据的个数

2）位图索引（位图+ROWID）(BITMAP)

说明：位图索引在创建时，会扫描整张表，为索引列的每个取值建立一个不重复的位图（BITMAP）来描述该取值

语法：CREATE BITMAP INDEX IND_NAME ON TB_NAME(COL_NAME);

CREATE BITMAP INDEX IND_EMP_DEPTNO ON EMP(DEPTNO);

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

3）反向键索引(索引列原始数据的反向存储+ROWID)(REVERSE)

说明：可以视作一种特殊的B-TREE索引，存储索引列的反向值

背景：为防止B-TREE索引在某叶上数据量占比过高而使用的一种索引

语法：CREATE INDEX IND_NAME ON TB_NAME(COL_NAME) REVERSE;

CREATE INDEX IND_EMP_SAL ON EMP(SAL) REVERSE;

适用场景：原始数据分支不明显但反向数据分支明显的列（身高：集中在一米七一米八）

4）基于函数的索引(将索引列原始数据经函数处理后存储+ROWID)

说明：可以视作一种特殊的B-TREE索引，存储函数处理后的数据

背景：在某个字段上以原值建立了索引，但是在筛选时在该字段上经常加函数，导致索引无法生效

语法：CREATE INDEX IND_NAME ON TB_NAME(FUNCTION(COL_NAME));

CREATE INDEX IND_EMP_ENAME ON EMP(LENGTH(ENAME));

适用场景：对某列进行筛选时经常需要配合函数使用（例如查找姓名中的首字母）

2,按唯一性（索引列中的数据是否有重复值）

1）唯一索引（索引列中不能出现重复值）(UNIQUE)

语法：CREATE UNIQUE INDEX IND_NAME ON TB_NAME(COL_NAME);

CREATE UNIQUE INDEX IND_EMP_EMPNO ON EMP(EMPNO);

注意点：

1.B-TREE索引可以建立唯一索引，位图索引不能建立唯一索引（因为位图索引上有很多

重复值 )

2.如果在某列上建立了唯一约束或主键约束，ORACLE会自动在该列上建立一个同名的唯一索引

2）非唯一索引（索引列中可以出现重复值）

语法：CREATE INDEX IND_NAME ON TB_NAME(COL_NAME);

CREATE INDEX IND_EMP_SAL ON EMP(ENAME);

3.按列的个数（索引覆盖的列的个数）

1）单列索引（基于一个列建立的索引）

语法：CREATE INDEX IND_NAME ON TB_NAME(COL_NAME);

CREATE INDEX IND_EMP_SAL ON EMP(ENAME);

2）复合索引（也叫联合索引）（基于两个或两个以上列建立的索引）

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

CREATE INDEX IND_EMP_SC ON EMP(SAL,NVL(COMM,1));

4.索引的删除(DROP)

语法：DROP INDEX IND_NAME;

DROP INDEX IND_EMP_SC;

5.索引的禁用与重建

1）索引的禁用(UNUSABLE)

语法：ALTER INDEX IND_NAME UNUSABLE;

ALTER INDEX IND_EMP_SC UNUSABLE;

2）索引的重建(REBUILD)

语法：ALTER INDEX IND_NAME REBUILD;

ALTER INDEX IND_EMP_SC REBUILD;

6.索引相关数据字典

1）所有索引

--所有索引

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'; --表名

2）索引列

--索引列

SELECT INDEX_NAME,   --索引名称

       TABLE_NAME,   --表名

       COLUMN_NAME,  --列名

       COLUMN_POSITION, --字段在索引中的位置

       DESCEND       --排序方式  默认ASC

  FROM USER_IND_COLUMNS

 WHERE INDEX_NAME ='IND_EMP_UPENAME';

3）索引函数

SELECT INDEX_NAME,

       TABLE_NAME,

       COLUMN_EXPRESSION

  FROM USER_IND_EXPRESSIONS

 WHERE INDEX_NAME ='IND_EMP_UPENAME';

7.索引建立或使用的规则的建议

1）如果对某大表进行筛选时，某列或某几列频繁出现在WHERE子句中，并且检索出的数据低于总行数的15%（50%），应考虑在这些列上建立索引。

2）如果对某大表进行排序时，某列或某几列频繁出现在ORDER BY子句中，应考虑在这些列上建立索引。

3）小表不要建立索引。

4）对于含有空值的列，如果经常在查询时查询非空值，建议在该列上建立索引；如果经常在查询时查询空值，建议在该列上建立基于函数的索引。

CREATE INDEX IND_COMM ON EMP(COMM);--经常在查询时查询非空值，建立索引后排序，空值会排在最前或者最后

CREATE INDEX IND_COMM ON EMP(NVL(COMM,-1));

--经常在查询时查询空值，这样就可以把空值判断COMM IS NULL时导致的索引失效情况避开了，也避开了与表中原有数据0冲突情况

SELECT * FROM EMP WHERE NVL(COMM,-1)=-1;

5)为了提高表连接的性能，应在连接列上建立索引（建立一般普通的索引即可）

--连接条件也相当于筛选，索引有助于提高筛选效率

6)索引是数据库的一种实体对象，级别类似于表，会占用内存空间，ORACLE会自动进行索引维护，表和索引可以建立在不同的表空间。

7)通过索引可以提升数据的查询速度，但是会相对地降低DML语句的操作速度，尤其是插和改的速度，ORACLE会花费时间在索引维护上，所以说要把握好索引的数量--按需建立

8)对于列基数比较大的列，适合B-TREE索引，列基数比较小的列，适合位图索引。

9）对于复合索引，至少要引用到索引列中的第一个列才会使用该索引。

--面试会问 （该点为早期说法，现行理论有时非第一列查询也能引用，以实际为准）

10）某列可以出现在多个索引中，但相同的某列或某几列无法多次建立索引。

--就是可以有多个组合，但不能有重复组合，不同顺序的相同几列视为不同组合

11）索引建立后并不一定会被引用，ORACLE会分析整个SQL后做出最优的执行方式。

12）ORACLE会自动在主键约束和唯一约束列上建立唯一索引。

13）对于一般的B-TREE索引，通配符出现在搜索词的首位时不会引用索引

CREATE INDEX IND_ENAME ON EMP(ENAME);

SELECT * FROM EMP WHERE ENAME LIKE'S%';

SELECT * FROM EMP WHERE ENAME LIKE'%S';--通配符%在首位时，索引失效

14）在索引列上使用<> !=号时，或对空值进行判断（IS NULL）时，索引会失效

CREATE INDEX IND_COMM ON EMP(COMM);

SELECT * FROM EMP WHERE COMM<>500;--索引失效

SELECT * FROM EMP WHERE COMM IS NULL;--索引失效

15）应尽量避免在 where子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描

CREATE INDEX IND_ENAME ON EMP(ENAME);

SELECT * FROM EMP WHERE ENAME='SMITH' OR JOB='MANAGER';