索引

什么事索引
1、索引是数据库对象之一，用于加快数据的检索（最重要的数据关系）。在数据库索引可以减少数据库成粗查询结果时需要读取的数据量。
2、索引是建立在表上的可选对象；索引的关键在于通过一组排序后的索引键来取代默认的全表扫描检索方式，从而提高检索效率。
3、索引在逻辑上和物理上都与相关的表和数据无关，当创建或者删除一个索引时，不会影响基本的表。
4、索引一旦建立，在表上进行DML操作室（例如咋执行插入、修改或者删除相关操作时），oracle会自动管理所引，索引删除，不会对表产生影响。
5、oracle创建主键时会自动在该列上创建索引。

索引的原理
1、若没有索引，搜索某个记录时（例如查找name=‘SCOTT’ 需要搜索所有的记录，因为不能保证只有一个scott，必须全部的搜索一遍
2、若在name上建立索引，oracle会对权表进行一次搜索，将每条记录的name值哪找升序排序，然后建立索引项目，存储到索引段中，查询name为wish时即可直接查找到相应的地方
3、创建了索引并不一定就会使用，oracle自动统计表的信息后，决定是否使用索引，表中数据很少时使用权标扫描速度已经很快，没有必要使用索引。

索引的类型
1、唯一和非唯一索引：唯一索引是基于唯一列的索引。当使用了唯一约束时，由oracle自动创建。
2、主索引和次索引：主索引是表的唯一索引，它是非空且唯一的。次索引是表中其他列上的索引，不是唯一索引。
3、组合索引：是包含了表中的两个列或者更多列，还成为拼接索引。

索引的种类
1.B-tree索引
1.oracle中最常⽤用的索引;B树索引就是⼀一颗⼆二叉树;叶⼦子节点(双向链表)包含索引列列和指向表中每个匹配⾏行行的ROWID值
2.所有叶⼦子节点具有相同的深度，因⽽而不不管查询条件怎样，查询速度基本相同
3.能够适应精确查询、模糊查询和⽐比较查询
分类：unique,no-unique(默认），reserve key

create index ind_pbj_id on test_object('OBJECT_ID")
适合应用的场景：列基数（列不重复值得个数）大时适合使用B树索引

索引相关视图
user_indexes 查看索引名字、类型、表明、是否唯一索引
user_ind_columns 查看索引名、表明、列名

select ic.index_name,table_name,ic.column_name,ix.uniqueness
from user_indexes ix user_ind_columns ic
where ic.index_name=ix.index_name
and ic.table_name='EMP';

查看执行计划

explain plan for select * from emp where lower(ename)='SCOTT';
@?/rdbms/admin/utlxplp.sql

创建索引

create index ind_obj_id on test_objects(object_id)
tablespace tbs_ind_emp;

创建索引时选择收集优化统计信息

create index ind_obj_id on test_objects(object_id)
tablespace tbs_ind_emp
compute statistics

创建索引

创建唯一索引
create union index ind_obj_id on test_objects(object_id)
tablespace tbs_ind_emp

创建非唯一索引索引
create index ind_obj_id on test_objects(object_id)
tablespace tbs_ind_emp;

创建组合索引
create index ind_obj_id on test_objects(object_id,object_name)
tablespace tbs_ind_emp;
创建唯一约束时、创建主键约束时，自动创建唯一索引

单列索引和复合索引
即如果索引建立在多个列上，只有他的第一个列被列为where子句引用时，优化器才会使用该索引，即至少要包含组合索引的一个列
create index ind_no_name on emp(empno,ename);

位图索引
创建位图索引时，oracle会扫描整张表，并为索隐列的每个取值建立一个位图（位图中，对表中每一行使用一位（bit，0或者1）来标识该行是否包含该位图的索隐列的取值，如果为1，表示对应的rowid所在的记录包含该位图索隐列值），最后通过位图索引中的映射函数完成位到行的rowid的转换
create bitmap index index_sno on emp(sno);

函数索引
1、当经常要方为一些函数或者表达式时，可以将其存储在索引中，这样下次访问时，该值已经计算出来了，可以加快查询速度
2、函数索引既可以使用B树索引，也可以使用位图索引；当函数结果不确定时采用B树索引，结果是固定的某几个值时使用位图索引
3、函数索引中可以使用用len，trim，substr，upper（每行返回独立结果），不能使用sum，max，min，avg等

create index fbi on emp(upper(name));
select * from emp where upper(name)='WISH';

压缩索引
重复键值只存储一次，就是说重复的键值在叶块中就存一次，后跟所有与之匹配的rowid字符串

SQL> create index comp_idx on emp1(sal) compass;

升序或降序
叶节点中的键值排列默认是升序的

SQL> create index deptno_job_idx on emp1(deptno desc,job asc);

查看索引
查看表上具有哪些索引

select index_name,index_type, tablespace_name,uniqueness from all_indexes
where table_name='TABLE_NAME';

重建和维护索引

索引重命名

alter index old_name rename to new_name;

表重命名

alter table old_name rename to new_name;

收集统计信息

analyze table table_name compute statistics;

修改索引
1、合并索引（表使用一段时间后在索引中会产生碎片，此时索引效率会降低，可以选择重建索引或者合并索引，合并索引方式更好些，无需额外存储空间，代价较低）
alter index index——sno coalesce；
删除索引
drop index index——sno；

创建索引的准则
1、需要访问的数据不超过表中的4%-5%，使用索引查询数据需要进行两次数据读取的操作，即读索引和读表
2、相对较小的表可以不使用索引
3、为所有表创建主键，在指定一列作为主键时，oracle自动创建唯一索引
4、对包括使用多表连接操作中使用的列，建立索引
5、在where子句中频繁使用的列上创建索引
6、在包括order by、group by操作的列，以及设计排序操作的union，distinct操作中涉及到的列上创建索引。因为索引是已经被排序的
7、被频繁的更改的列由于涉及开销问题，理论上不建议创建索引
8、只对高选择的表建立索引，即对几乎没有相同值得表建立索引