简单了解oracle索引

一、简单了解oracle索引

1、索引的组成

1、Root 跟块
2、Branch 茎块
3、Leaf 叶子块:主要存储 key column value(索引列具体值)，以及能具体定位到数据块所在位置的rowid

2、创建索引的流程

1、要建索引先排序
2、列值入块成索引
3、填满一块接一块
4、同级两块需人管

3、索引的特征

1、索引的高度比较低：高度低有利于索引范围扫描
2、索引本身能够存储列值(可以优化聚合)：count、sum、avg
3、索引本身有序(可以优化排序):order by、max、min

4、组合索引

1、适合单列查询返回多、组合查询返回少的场景
2、组合查询的组合顺序，要考虑单独的前缀查询
3、仅等值无范围查询时，组合的顺序不影响性能

5、索引相关优化案例
(1) 三大特性的相关案例

1、分区表各类聚合优化玄机
2、啥时分区索引性能反而低
3、同时取最大最小值的案例

(2) 组合索引的经典案例

1、组合索引需要考虑单列索引
2、组合索引与排序相关细节
3、组合查询和in有关的优化

6、oracle的其它索引类型
(1) 位图索引

1、原理：存储健值为0和1的比特值，占用空间极小
2、优势：高效即席查询、快速统计条数
3、陷阱：列重复度低慎建、更新列容易锁死

(2) 函数索引

1、原理：存储rowid和列的函数值
2、优势：让索引变得短小、减少递归调用
3、陷阱：30533的错误,要在创建索引时如使用了自定义函数指定 deterministic、各种列的运算形式、各种列的函数转换、各种列的类型转换、函数变列与影响，如变更函数，则对应的函数索引要重建，否则值不对

(3) 反向键索引

1、原理：反转的键值加上rowid
2、优势：减少热块竞争
3、陷阱：不能范围查询

(4) 全文索引

1、原理：通过oracle词法分析器分析并记录在 dr$打头的表中
2、优势：能查询模糊匹配
3、陷阱：数据更新完后要执行同步命令 exec ctx_ddl.sync_index(’’,’’),否则更新的记录查不到

(5) 虚拟索引

1、创建：创建虚拟索引，create index 索引名 on 表名(列名) nosegment
使用 explain plan for 查看是否会用到索引 explain plan for 查询语句
select * from table( dbms_xplan.display());
2、优势：用于预判待加入的索引是否能起作用
3、特点：
（1）虚拟索引无法执行alter index选项
SQL> alter index idx_wxw rebuild
ERROR at line 1:
ORA-08114: can not alter a fake index

（2）使用回收站特性的时候，虚拟索引必须显式drop，才能创建同名的索引 SQL> create index idx_wxw on test(name)
ERROR at line 1:
ORA-00955: name is already used by an existing object

（3）不能创建和虚拟索引同名的实际索引
（4）可以创建和虚拟索引包含相同列但不同名的实际索引
（5）使用回收站特性的时候，虚拟索引必须显式drop，或者在drop table后purge table后，才能创建同名的索引
（6）虚拟索引分析并且有效，但是数据字典里查不到结果，估计是oracle内部临时保存了分析结果

7、使用索引的不足之处
(1) 索引的各种开销

1、热块竞争：访问集中导致热块竞争
2、回表开销：什么是回表? 简单来讲就是根据数据块上的rowid来查询数据、回表性能取决于聚合因子
3、建立开销：建立索引过程会产生全表锁、建索引过程会产生全表排序

(2) 索引失效

1、逻辑失效
（1）如应用索引后检索数据，返回全部记录，则索引失效，索引适合返回少量数据的情况
（2）发生索引列的类型转换
（3）对索引列进行了各种运算

2、物理失效
（1）long列调整导致索引失效
（2）move操作导致索引失效
（3）分区表导致索引失效：所有的全局索引，只要用到update global indexes,都不会失效，其中add分区甚至不需要增加 update global indexes都可以生效
（4）分区表导致索引失效：局部索引的操作都不会失效，除了split分区。split分区时，要将局部索引进行rebuild.

(3) 索引使用时的取舍

1、避免表交叉重复建立索引. (比如组合索引前缀与单列索引相同时，则单列索引可以不用设置)
2、删除系统从没有用到过的索引
查询索引使用情况 select * from v$object_usage;
开启索引监控 alter index 索引名 monitoring usage;
3、组合列索引列数不宜过多

8、索引不足之处案例
(1) 关于索引的开销

1、设置索引并行属性引起性能下降
（1）语法: create index 索引名 on 表名(列名) parallel 并行度数;
以上做法会导致所有对这个表的操作都会并行，最终必然会产生争用，建议采用HINT的方式来给查询加并行

2、分区表与插入性能的提升
如表上有大量索引，对于分区表的局部索引由于只需要更新局部分区的索引，所以索引的开销会比较小，所以插入性能比有相同记录数，列与索引的普通表更快.

3、没有使用online建立索引时，会导致对该表的更新操作无法执行，需要等待索引建立完. 但online建立索引的动作会被更新操作阻塞

(2) 关于索引失效

1、like与% 使用时的小技巧
（1）like ‘%张三%’ 不能用到索引
（2） like ‘张三%’ 可以用到索引
（3）like ‘%张三’ 不能用到索引,但变通一下 where reverse( 列名 ) like reverse(’%张三’),就可以用到索引了

2、 move表导致索引失效
（1）alter table move的方法使表的空间释放,但它会导致索引失效
（2）所以要重建索引 alter index 原索引名 rebuild

3、move导致索引失效引发锁等待
由于move外键所在的表，导致外键所在的表的索引失效，导致主键表更新起来频频被锁

4、使用 ‘alter table 表名 move ’ 降低高水平位会导致索引失效，那么用 ‘alter table 表名 shrink’ 的方式来降低高水平位的话，索引不会失效，但会出现使用索引还会更慢的情况，因为索引块的高水平位无法释放，会产生大量的逻辑读

5、如果使用 drop 误删除表了，这时索引已经被重命名了. 这里可以通过 ’ flashback table 表名 to before drop’ 将表取回，但索引需要找回 ‘select * from user_indexes where table_name=‘表名’, 找回索引名，然后再用 ‘alter index ‘索引名’ rename to 原索引名