oracle数据库对象——索引（详）

1.概念

• 索引是数据库对象之⼀，用于加快数据的检索。在数据库中索引可以减少数据库程序查询结果时需要读取的数据量。
• 索引是建立在表上的可选对象；索引的关键在于通过⼀组排序后的索引键来取代默认的全表扫描检索方式，从而提高检索效率。
• 索引在逻辑上和物理上都与相关的表和数据无关，当创建或者删除⼀个索引时，不会影响基本的表；
• 索引⼀旦建立，在表上进行DML操作时（例如在执行插入、修改或者删除相关操作时）， oracle会自动管理索引，索引删除，不会对表产生影响索引对用户是透明的，无论表上是否有索引，sql语句的用法不变
• oracle创建主键时会自动在该列上创建索引
  【analyze table 表名 compute statistics】 手动收集表统计信息
  【analyze index 索引名 compute statistics】 手动收集索引统计信息

2.查看索引信息

【select index_name from user_indexeswhere table_name=‘T1’】

• 索引的数据字典：
  【user_indexes】
  【user_ind_columns】
  通过desc命令可看到两个数据字典的结构，可看到有哪些列比如：索引名字、类型、表名、是否唯⼀索引
  user_ind_columns 数据字典比 user_indexes多出列的信息
  重点列：
  **distinct_keys 列：**索引键值列去重后的条数
  num_rows列：索引键值列行数两者的比值越接近于1表示选择性越高
  column_position列：索引包含的列数以及顺序
• 表的数据字典
  【user_tables】
  【user_tab_columns】
  重点列：
  num_distinct：唯一值，唯一值与表的总列数越相近表示选择性越高
  数据字典表的数据字典：dict
  【select * from dict】可以查看到系统中所有的数据字典表

2.索引原理

• 若没有索引，搜索某个记录时需要搜索所有的记录
• 若在name上建立索引，会构建索引条目（name和rowid），存储到索引段中，查询name为wish时即可直接查找对应地方
• 创建了索引并不⼀定就会使用，oracle自动统计表的信息后，决定是否使用索引，表中数据很少时使用全表扫描速度已经很快，没有必要使用索引
  分析一条命令：
  【explain plan for select * from t1 where object_id=371】
  调用脚本查看执行分析结果：
  【@?/rdbms/admin/utlxplp.sql】
  
  从首行缩进的行开始看：
  2.索引范围扫描，cost（%cpu）代表占用的cpu即维护代价
  1.通过索引查询出来的rowid进入table
  0.返回查询结果

3.索引类型

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

4.索引种类

• B-tree索引（默认）:
  适合用于事务型系统OLTP
  事务型：事务小，处理的数据量少，需要很快的处理速度，读一致
  对表执行select 操作时，会先到索引里去查询键值列，进而得到rowid确定要输出的行
  例如：name sex idcard
  b-tree索引——————————————>idcard（选择性高）
  
  叶节点中键值排列默认为升序
  可以手动设置
  【create index deptno_job_idx on emp1(deptno desc, job asc); 】
• 位图索引：
  适合用于分析型系统OLAP
  分析型：数据量大，不要求快速度
  对位图索引来说，update和delete操作会造成很大的维护代价，需要锁上整张表，因此要小心使用
  例如:name sex idcard
  bitmap索引——————————————>sex（选择性低）
  男1，0
  女0，1
• 单列索引和复合索引
• 函数索引：
  类似于select avg(sal） from…查询函数列时系统不会使用索引
  可手动创建函数索引
  【create index ind_name
  on 表名 (upper(object_name))】
• 反键索引：
  经常被使用的数据块叫热块，容易出现资源的争抢
  反键索引是将B-tree键值列变成反向数据的，例如110变成011
  即打乱了数据的排序，避免热块的出现
  缺点： 不适用于范围扫描（ between、<、>
  【create index ind_name
  on 表名（列名）reverse】
• 压缩索引
  重复键值只存储一次，就是说重复的键值在叶块中就存一次，后跟所有与之匹配的 rowid 字符串。
  【create index ind_name
  on 表名（列名）compress】
  节约了空间，但占用时间，因为在使用的时候需要解析行的位置

5.索引维护

• 索引的数据是跟着基表的DML活动而经常发生变化的。
• 基表删除数据后，对应的索引叶节点中空间不会被释放、不会被重用。
• 在DML操作十分频繁的表上的索引，有可能会变得非常庞大。
  索引变得庞大之后，查询操作速度会变慢
  解决方法1：重新建立索引
  【drop …然后create…】
  【alter index 索引名 rebuild】重建过程中会锁掉这个表避免insert操作产生新的数据
  【alter index 索引名 rebuild online】在线重建索引，不锁表但耗费时间较多
  【alter index 索引名 rebuild online nologging】在线重建不记录日志
  解决方法2：合并索引（更好）
  【alter index 索引名 coalesce】时间长一些

6.索引重命名

【 alter index 索引原名 rename to 新索引名】

7.隐藏索引

主要是对优化器隐藏索引
【create index 索引名 on 表名(列名) invisible; 】
【alter index 索引名 invisible;】
【alter index 索引名 visible;】
查看索引是否为隐藏模式：
【select index_name , status , visibility from user_indexes;】

8.监控索引的使用

监控索引的有效性，可请求Oracle对索引进行监控
【alter index 索引名 monitoring usage 】
终止监控:
【 alter index 索引名 nomonitoring usage】
查看某索引的监控信息:
【select * from v$object_usage where index_name= ‘索引名’】

9.创建索引的准则

选择性高。因为使用索引教唆数据需要进行两次数据读取的操作，即读索引和读表。
相对较小的表可以不使用索引。
为所有表创建主键，在指定一列作为主键时，Oracle自动创建索引。
对包括使用多表连接操作中使用的列，建立索引
在where子句中频繁使用的列上创建索引
在包括order by 、group by操作的列，以及涉及排序操作的 union、distinct操作中涉及到的列上 创建索引
被频繁更改的列由于涉及开销问题，理论上不创建索引

10.注意事项

通配符在搜索词首出现时，oracle不能使用索引
不要在索引列上使用not，可以采用其他方式代替如下：
【select * from emp where score>100 or score <100】
索引上使用空值比较将停止使用索引