什么是索引
索引的主要作用就是加速表的查询。
索引可以显示创建，执行索引命令创建。
索引也可以隐式创建，主键和唯一键都是隐式创建索引。
索引的特征
- 索引是模式Schema Object，高于衍生数据对象。和表一样，索引也有自己的段结构。
衍生数据对象：视图、同义词都是从表衍生出来的。
表和索引都具有自己的物理对象，段所对应的数据文件。 - Oracle使用索引来进行检索加速，索引可以提升SELECT语句的查询速度。
- 使用快速路径访问的方法快速查找数据，减少磁盘IO操作。快速访问实际上就是用ROWID加速的。
- 索引与索引所在的表无关，索引都是对象，索引和他所在的表都是独立的。视图是表衍生出来的，视图和表是有关系的。
- 索引是由Oracle自身进行更新和维护的。当表插入数据的时候，会自动更新表中列对应的索引数据。不需要人工干预。
索引的原理
索引的数据包含了索引列和Rowid。
rowid是每条记录的唯一ID，rowid和索引列对应起来，找到索引列的rowid，然后查找这一行的内容。
SELECT rowid, employee_id FROM employees;
ROWID EMPLOYEE_ID
------------------ -----------
AAAR6BAADAAALWLAAA 100
AAAR6BAADAAALWLAAB 101
AAAR6BAADAAALWLAAC 102
索引的创建。
隐式创建索引
定义了主键或者唯一键约束的时候，会自动创建一个唯一索引。
显示创建
用户可以手工通过创建索引的命令，在列上创建唯一或者非唯一索引，加速对数据的访问。
默认创建的索引是B平衡树索引，采用的BTREE算法。MYSQL使用的B+TREE索引。
Oracle索引的类型
- 唯一索引： 既可以隐式创建，也可以显式创建。
- B*TREE索引： 默认值
- 位图索引： 数据仓库用的比较多
- 复合索引： 将多个列做成一个索引
- 函数索引： 当索引里需要函数转换的时候，可以预先创建一个函数索引。
- 反向索引： 为了避免热块争用(批量Insert的时候)
- 分区索引： 分区表中使用的索引。本地前缀分区索引、本地无前缀分区索引、全局前缀分区索引
创建索引的场合
不建议表中的索引过度，增加维护成本，影响查询速度。尤其是大量插入或更新的表，索引越多越危险。
- 列中包含的较大范围的值
- 列中包含了大量的值。
- 在where子句或者联结条件中频繁使用的一个列或者多个列。
使用多个列的时候，创建符合索引或者给每个列创建索引。 - 表很大，但一般检索的数据不到表的2%至4%时。
- 按照列中数据的重复度去选择：
- 重复度较低或者无重复，选择B*TREE索引或者唯一索引。
- 数据仓库的数据有一定重复度的话， 一般创建位图索引。
- 加快分区表的查询速度的话，一般创建分区索引。
- 为了避免热块争用，一般创建反向索引。
不适合创建索引的场合
- 表比较小，全盘扫描的速度快于索引参与查询的速度。
- 查询中不常使用的列。
- 语句检索的数据超过表的2%至4%时。
- 表更新比较频繁，造成索引会跟着频繁更新。
- 被索引的列将被函数引用，比如对这个列进行函数操作，函数会造成索引失败。
如果列被函数操作的话，建议使用函数索引。
创建索引的命令
CREATE （索引种类） INDEX 索引名[表名_列名_IDX] ON [模式SCHEMA.]表名(列名);
主键索引和唯一键索引
主键约束和唯一键约束是逻辑上控制的约束，而唯一键索引是具备物理结构的对象。
唯一键索引对字段进行唯一性检查，禁止该字段出现重复值，但是允许null插入。
create table test_index (id number);
CREATE UNIQUE INDEX TEST_INDEX_ID_IDX ON HR.TEST_INDEX(ID);
INSERT INTO test_index VALUES (1);
COMMIT;
INSERT INTO test_index VALUES (1);
ORA-00001: 违反唯一约束条件 (HR.TEST_INDEX_ID_IDX)
创建函数索引
正常来说，当查询的时候，对一个列的数据进行函数转换的时候，将不会走索引。
如果做了函数索引，对应的函数出现时，仍将走索引查询。
-- 创建一个depts表
CREATE TABLE depts AS SELECT * FROM departments where 1=1;
-- 创建一个大写字段名的函数索引
CREATE INDEX upper_depts_department_name ON depts(UPPER(department_name));
-- 创建一个大写字段名的函数索引
CREATE INDEX upper_depts_department_name ON depts(UPPER(department_name));
-- 以下查询将通过索引查找
SELECT * FROM depts WHERE UPPER(department_name) = 'SALES';
创建复合索引
用户可以在多个列上建立索引，这种索引叫作符合索引。符合索引在数据库操作期间所需的开销更小，可以替代多个单列索引。
窄索引： 索引列为1~2列的索引。
宽索引： 索引列超过两列的索引。
设计索引的一个重要原则就是能用窄索引不用宽索引，拥有更多的窄索引，能给优化程序更多的选择余地，有助于提高性能。
CREATE INDEX 索引名 ON 表名(列名1，列名2，列名3)
列名1 是前导列； 列名2，列名3是非前导列。
尽量把关系紧密的列创建为复合索引。
CREATE INDEX DEPTS_DEPTID_DEPTNAME_IDX ON depts(DEPARTMENT_ID, DEPARTMENT_NAME);
-- Index DEPTS_DEPTID_DEPTNAME_IDX 已创建。
确认索引
USER_INDEXES数据字典视图包含了索引的名称,表名和索引的唯一性。
USER_IND_COLUMNS 数据字典视图包含了索引名、表名和列名。
-- 创建实验表
CREATE TABLE EMP1 AS SELECT * FROM EMPLOYEES;
-- 创建基于employee_id的索引
CREATE INDEX EMP1_EMPLOYEE_ID ON EMP1(EMPLOYEE_ID);
--
SELECT IDX.INDEX_NAME, IDX.TABLE_NAME, IDXCOL.COLUMN_NAME,
IDX.UNIQUENESS
FROM USER_INDEXES IDX INNER JOIN USER_IND_COLUMNS IDXCOL
ON IDX.INDEX_NAME = IDXCOL.INDEX_NAME AND IDX.TABLE_NAME = IDXCOL.TABLE_NAME
WHERE IDX.TABLE_NAME = 'EMP1';
INDEX_NAME TABLE_NAME COLUMN_NAME UNIQUENESS
-------------------- -------------------- -------------------- --------------------
EMP1_EMPLOYEE_ID EMP1 EMPLOYEE_ID NONUNIQUE
删除索引
DROP INDEX 索引名字 ;
SELECT INDEX_NAME, TABLE_NAME
FROM USER_INDEXES WHERE TABLE_NAME = 'DEPTS';
-- UPPER_DEPTS_DEPARTMENT_NAME DEPTS
DROP INDEX UPPER_DEPTS_DEPARTMENT_NAME;
-- Index UPPER_DEPTS_DEPARTMENT_NAME已删除。
删除表的时候，索引也同样被删除。
DROP TABLE 表名 -> 会删除索引。
原文地址：http://www.cnblogs.com/slqleozhang/p/16883910.html
