Oralce 专门索引

Oralce 专门索引

Oracle中为了应对一些特殊场景，设立了一些专门的索引。

不可见索引

特点： 数据库的查询优化器不会考虑不可见索引，但数据库会在对应表数据变化时继续维护该索引。

用途：
不可见索引最主要用途是在删除某个索引之前测试去除这个索引对其他查询性能的影响。防止删除索引后重建索引的巨大开销。

语法：
创建不可见索引
create index test_idx1 on table(column) invisible;

调整索引为不可见/可见
alter index test_idx invisible/visible;

其它：
可以让优化器使用不可见索引
alter session set optimizer_use_invisible_indexes=true;

基于函数的索引

特点：
Oracle支持将单列或多列的值进行函数计算后的值进行存储，并作为相应的索引进行使用。

用途：
一般情况下，在查询时使用使用函数计算数据列时，会跳过原数据列的索引，从而执行全表扫描。在创建基于函数的索引之后，数据库可以提前将对应的函数计算并存储。从而在以相应计算函数作为查询条件时，数据库会基于该索引进行查询。

语法：
创建基于函数的索引
create index test_idx1 on table(Function(column));

也可以是基于表达式的索引
create index test_idx1 on table((column1 + column2));

其它：
基于函数的索引要求索引对应的函数或表达式返回的值必须是确定的，即对于相同的输入返回的值也必须是相同的。
同时由于维护索引的复杂度增加，插入或更新的允许速度会很慢，但相对条件下的查询会加快很多。
创建基于函数的索引会在对应的表内增加对应的隐藏虚拟列用于存储函数结果值。需要进行相应的统计信息收集后，索引才会生效。

虚拟列索引

特点：
oralce数据库允许表创建虚拟列。虚拟列的数据不会存储，仅会在虚拟列被查询时进行计算并返回。用虚拟列作为索引列，与上述的基于函数的索引效果类似。

语法：
创建虚拟列的语法
column_name [datatype] [GENERATED ALWAYS] AS (expression) [VIRTUAL]
其中[datatype]不填时，默认为表达式返回的值类型。
[GENERATED ALWAYS]表示该列的值是基于它的引用列值产生的。
如 salay NUMBER GENERATED ALWAYS AS (ROUND(salary*(1+comission)/100), 2)

其它:
虚拟列索引与基于函数的索引原理大致相同，只是基于函数的索引生成的虚拟列是隐藏的。

键压缩索引

特点：
压缩索引列，减少存储空间，减少查询时叶块访问，提高查询性能。

用途：
当复合索引的前缀列存在大量重复值时，能够通过创建键压缩索引，压缩索引前缀列用于共享，从而减少索引存储空间，同时减少叶块访问提高性能。

语法：
创建键压缩索引
create index test_idx on table(column1,column2) compress [number]
其中[number]选填，表示压缩的键列数。不填时，对非唯一索引会压缩所有列，对唯一索引，压缩除最后一列外的所有列。

其它:
对于单列唯一性索引，若压缩则会报错。
创建复合索引后，索引块的内容：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-c3yDTM1E-1615949763331)(./1523951750323.png)]
创建键压缩索引后，索引块内容：
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-zpby9atu-1615949763333)(./1523951788791.png)]
[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-gyXqwQzS-1615949763336)(./1523951794864.png)]

复合索引

特点:
由多个列组成的索引，可以是B-tree索引，也可以是位图索引。当查询访问复合索引，且查询的所有字段均为索引的键列，则数据部不再访问表数据块。
跳跃式扫描：
在复合B-tree索引中，若查询时为指定前导列作为条件，则Oracle就可以执行索引跳跃式扫描。
一般通过逻辑子索引消除或跳过一个复合索引来实现。
一般前导列只有少量不同值，而非前导列包含大量不同值时适合使用跳跃式扫描。
例如，员工信息表中，若以gender,email为索引时，若未指定前导列gender的限定条件而指定email的条件，则Oracle会将查询拆分为

select * from employee where gender = 'F' and email = 'a' 
union all
select * from employee where gender = 'M' and email = 'a'

虚假索引

特点：
与不可见索引不同，数据库只会为虚假索引增加字典，但是不会真正维护该索引。可以通过参数来设置虚假索引是否对优化器可见。

用途：
一般在为一张表增加索引来优化查询前，可以先新增对应索引列的虚假索引来查看优化器的执行计划，从而判断目标索引是否有维护的价值。

语法：
创建虚假索引
create index virtual_idx on table(column) nosegment;

使虚假索引对优化器可见
alter session set “_use_nosegment_indexes” = true;

反向键索引

特点：
反向键索引会将索引列键值按字节反转存储。

用途：
在索引列按顺序插入时，若列值以递增/减序列填充，则这些新的索引值将被插入到同一索引块。若多个会话同时操作数据时，则可能因为争用同一索引块而降低性能。
若使用反向键索引，则生成的索引列值会由12345和12346反转为54321和64321，从而使索引值插入不同的索引块，减少瞬时对同一索引块的争用。

语法：
创建反向键索引
create index reverse_idx on table(column) reverse;

将普通索引重建为反向键索引
alter index reverse_idx rebuild reverse;

将反向键索引重建为普通索引
alter index reverse_idx rebuild noreverse;

其他：
反向键最大的缺点在于无法使用区间查询，所以对于 < ,> , between等区间查询条件无法使用反向键索引。