索引
索引
索引的起名规则：索引名_表名_列名
说明：
1）索引是数据库对象之一（数据库对象有6个：用户，表，视图，索引，序列，触发器），用于加快数据的检索，类似于书籍的目录。
在数据库中索引可以减少数据库程序查询结果时需要读取的数据量，
类似于在书籍中我们利用目录可以不用翻阅整本书即可找到想要的信息。
2）索引是建立在表上的可选对象;–索引的关键在于通过一组排序后的索引键来取代默认的全表扫描检索方式，从而提高检索效率
3）索引在逻辑上和物理上都与相关的表和数据无关，当创建或者删除一个索引时，不会影响基本的表
4）索引一旦建立，在表上进行DML操作时（例如在执行插入、修改或者删除相关操作时），oracle会自动管理索引
5）索引对用户是透明的，无论表上是否有索引，sql语句的用法不变
6）oracle创建主键时会自动在该列上创建B-TREE索引
1.六个数据库对象：用户，表，视图，索引，序列，触发器；
2.索引类似于字典上目录，是建立在一列或多列上的辅助对象；
3.索引可以减少查询结果时读取的数据，从而加快数据的检索；
4.当创建或删除索引时，不会对表产生影响；
5.创建索引后，会加快查询效率，但是，进行DML操作上，ORACLE会自动管理索引（大部分情况都会降低效率）；
6.索引对用户时透明的，无论表上是否有索引，SQL语句不会变；
7.ORACLE创建主键时，会自动在该列上创建索引==B-TREE索引==。
全盘扫描：将表中的每一条数据都挨个扫一遍，默认的扫描方式
索引扫描：在表中的列上建立索引，先对数据进行排序（默认从小到大），将该数据以及对应的 rowid 存为一条索引条目（数据+rowid）
检索数据时，会先对索引列进行检索，然后直接定位到数据所在的 rowid。
冷知识：虽然ROWID对我们没什么太大作用，但它的检索速度确实很快
索引的分类
按照存储形式
B-TREE索引
位图索引
反向键索引
基于函数的索引
按照唯一性
唯一索引 非唯一索引
按照列个数
单列索引 复合索引(联合索引)–多列索引
一. B-TREE索引（B树索引）–默认索引类型，最常见，最常用的索引类型根块–索引中的顶级块（包含了指向下一个节点的信息）
分支块–指向下一个节点的信息（指向分支块或者叶块）
叶块–索引的入口（能够适应精确查找，模糊查询，比较查询）
特点：
1.适用于大部分情况
2.不适用于包含OR的语句
语法：CREATE INDEX 索引名 ON 表名(需要添加索引的字段1,字段2,…);
–sql语句先看有没有符合条件的索引，有就使用索引，没有就全盘扫描
二：位图索引
- 主要用于值的范围相对固定或只有少数几个值的列中
- 适用于包含OR的语句
语法：CREATE BITMAP INDEX 索引名 ON 表名(需要添加索引的字段1,字段2,…);
三：反向键索引
- 将数据反过来存储
- 不常见，但是部分有奇效
10001 10002 10005 10033 10016
10001 20001 50001 33001 61001
语法：CREATE INDEX 索引名 ON 表名(需要添加索引的字段1,字段2,…) REVERSE;
–REVERSE 颠倒
四：基于函数的索引
用于经常对某个字段做查询的时候带函数操作的
语法：CREATE INDEX 索引名 ON 表名(FUNCTION 需要添加索引的字段1,字段2,…);
五：唯一索引
索引列中不允许出现重复的值
语法：CREATE UNIQUE INDEX 索引名 ON 表名(需要添加索引的字段1,字段2,…);
提问：B-TREE索引一定是唯一索引吗？位图索引呢？
回答：B-TREE索引不一定是唯一索引，位图索引一定不是唯一索引
六：单列索引
只覆盖一个列的索引（只引用一个列时会生效）
语法：CREATE INDEX 索引名 ON 表名(需要添加索引的字段1);
七：复合索引
覆盖多个列的索引（引用到第一个列时会生效）
语法：CREATE INDEX 索引名 ON 表名(需要添加索引的字段1,字段2,…);
删除索引：
语法：DROP INDEX 索引名;
修改索引：
1.重命名索引
语法：ALTER INDEX 旧索引名 RENAME TO 新索引名;
2.合并索引
–占物理空间，会随着数据的增加而变大
–将更新、删除等操作产生的空闲空间进行合并
语法：ALTER INDEX 索引名 COALESCE DEALLOCATE UNUSED;
3.禁用索引
ALTER INDEX 索引名 UNUSABLE;
4.启用索引（重建索引）
ALTER INDEX 索引名 REBUILD;
紧急提问：索引越多越好吗？
不是。创建索引耗费时间，占物理空间，会随着数据的增加而变大，会拖慢效率
创建索引的原则：
1.如果在一列上同时存在唯一索引和非唯一索引，ORACLE会自动引用唯一索引；
2.如果索引建立在多个列上时，只有他的第一个列被引用时，优化器才会使用该索引；
3.数据量小的表不建议创建索引；
4.列中有很多空值，不建议创建索引，因为索引容易失效；但是如果该列的非空值经常被查询，还是应当创建索引；
5.经常进行WHERE查询或表连接的列，应当创建索引；
6.在创建索引时，要将最常查询到的列放在最前，索引的第一个列也应该放在最前；
7.数据类型：NUMBER CHAR VARCHAR2 VARCHAR DATELONG (变长，字符串数据，最长2G)–不能创建索引LONG RAW (变长，二进制数据，最长2G)–不能创建索引
INT–这个是什么？
8.要限制索引的数量。
8.1 不重要的列不建议创建索引
8.2 一张表内不建议创建太多的索引
原因：创建索引耗费时间，占物理空间，会随着数据的增加而变大，会拖慢效率
索引失效：
1.通配符：* _ %
当通配符处于搜索词的首位 ‘%A%’
2.在索引列上使用不等于时
3.索引判断空值时
4.未引用复合索引的第一个列时
1.索引本身失效
2.隐式转换导致索引失效
3.查询的数据量为一个表的大部分数据
4.对小表查询
5.B-TREE索引 IS NULL 不走索引 IS NOT NULL 走索引
ORACLE索引失效原因归纳
1．隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.
由于表的字段tu_mdn定义为varchar2(20),但在查询时把该字段作为number类型以where条件传给Oracle,这样会导致索引失效.
错误的例子：select * from test where tu_mdn=13333333333;
正确的例子：select * from test where tu_mdn=’13333333333’;
2. 对索引列进行运算导致索引失效,我所指的对索引列进行运算包括(+，-，*，/，! 等)
错误的例子：select * from test where id-1=9;
正确的例子：select * from test where id=10;
3. 使用Oracle内部函数导致索引失效.对于这样情况应当创建基于函数的索引.
错误的例子：select * from test where round(id)=10; 说明，此时id的索引已经不起作用了
正确的例子：首先建立函数索引，create index test_id_fbi_idx on test(round(id));然后 select * from test where round(id)=10; 这时函数索引起作用了
4. 以下使用会使索引失效，应避免使用；
- 使用 <> 、not in 、not exist、!=
- like “%_” 百分号在前（可采用在建立索引时用reverse(columnName)这种方法处理）
- 单独引用复合索引里非第一位置的索引列.应总是使用索引的第一个列，如果索引是建立在多个列上, 只有在它的第一个 列被where子句引用时，优化器才会选择使用该索引。
- 字符型字段为数字时在where条件里不添加引号.
- 当变量采用的是times变量，而表的字段采用的是date变量时.或相反情况。
5. 不要将空的变量值直接与比较运算符（符号）比较。
如果变量可能为空，应使用 IS NULL 或 IS NOT NULL 进行比较，或者使用 ISNULL 函数。
6. 不要在 SQL 代码中使用双引号。
因为字符常量使用单引号。如果没有必要限定对象名称，可以使用（非 ANSI SQL 标准）括号将名称括起来。
7. 将索引所在表空间和数据所在表空间分别设于不同的磁盘chunk上，有助于提高索引查询的效率。
8. Oracle默认使用的基于代价的SQL优化器（CBO）非常依赖于统计信息，一旦统计信息不正常，会导致数 据库查询时不使用索引或使用错误的索引。
一般来说，Oracle的自动任务里面会包含更新统计信息的语句，但如果表数据发生了比较大的变化（超过 20%）,可以考虑立即手动更新统计信息，例如：analyze table abc compute statistics，但注意，更新统计 信息比较耗费系统资源，建议在系统空闲时执行。
9. Oracle在进行一次查询时，一般对一个表只会使用一个索引.
因此，有时候过多的索引可能导致Oracle使用错误的索引，降低查询效率。例如某表有索引1（Policyno） 和索引 2 （ classcode），如果查询条件为 policyno = ‘ xx ’ and classcode = ‘ xx ’ ，则系统有可能会使用索引 2，相较于使用索引1，查询效率明显降低。
10. 优先且尽可能使用分区索引。
