Oracle索引简介

一、rowid

1.解释
  rowid的值在一行数据插入到数据库的表中时即被确定且唯一，通过它可以访问数据库表中的单行数据。rowid是一个伪列，它实际并不存在于表中，它是Oracle在读取表中数据是，根据没一行数据的物理地址信息编码而成的一个伪列。使用rowid来做单位记录定位速度最快。

2.rowid的构成
  共18位，基于base64编码，分为四个部分

• OOOOOOFFFBBBBBBRRR
• OOOOOO——6位，对象编号，该行数据所在的数据对象的 data_object_id；
• FFF——3位，文件编号，该行数据所在的相对数据文件的id，用于确定datafile的编号；
• BBBBBB——6位，块编号，是相对于datafile的编号，是该行所在数据块的编号；
• RRR——3位，行编号；

3.如何从rowid计算得到obj#,rfile#,block#,row#

• rowid用A~Z a~z 0~9 + /共64个字符表示。
• A表示0，B表示1，……
• a表示26，b表示27，……
• 0表示52，……
• +表示62，/表示63 可以将其视为64进制的数。
• obj#=AAAGbE=6*64^2+27*64+4=26308 rfile#=AAH=7 block#=AAAABB8=64+60=124

Oracle 表空间和数据文件之间的关系

4.rowid的内部存储格式
  虽然我们从rowid伪列中select出来的rowid是以base64字符显示的，但是再oracle内部存储的时候还是以原值的二进制形式存储的。
  一个扩展rowid用10byte来存储，共8*10bit，obj#32b，rfile#10b，block#22b，row#16b。所以一个数据库内不能有超过2^32=4G个Object，相对文件号不能超过2^10-1个（不存在文件号为0的文件）， 一个datafile只能有2^22=4M个块，一个块中不能超过2^16行数据。

5.Index中存储的rowid
  索引就是保留了rowid后三个部分，可以快速地定位到数据行。

二、索引的概念及语法

1. 索引概述
  索引与表一样，属于段（segment）的一种，里面存放了用户的数据，跟表一样需要占用磁盘空间。

  在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。 ——“百度百科”

  它独立于表的对象，可以存放于不同的表空间中，索引直接指向行数据的物理存储，减少磁盘 i/o ，Oracle自动使用并维护索引，插入、删除、更新表后，自动跟新索引。

2.索引的用法

• 创建索引

 create index <index_name> on <table_name> (<col_name>)[tablespace<tablespace_name>];

• 重置索引

 alter index <index_name> rebuild;
--REBUILD 是根据原来的索引结构重新建立索引，实际是删除原来的索引后再重新建立。

• 删除索引

drop index <index_name>;

三、索引的分类

1.B树索引（默认）
  B树索引可以是单列索引也可以是组合索引，B树索引最多可以包括32列。索引列的值都存储在索引表中，对应了一个物理位置，从而不用访问表就可以查询到行。

特点：

• 适合大量增、删、改
• 适合高基数的列，即列的值重复率低
• 不能用包含or操作的查询
• 是树状结构，每个节点都是数据块
• 叶子块数据从左往右递增
• 在分支块和根块中放的是索引的范围

create index <index_name> on <table_name>(<col_name>);

2.位图索引
  位图索引非常适合于DSS和数据仓库，但不适用于OLTP访问的表。位图索引使用位图作为键值，对于表中的每一数据行，位图包含了TRUE/FALSE，0/1，NULL值。

特点：

• 适合较低基数的列，如：性别
• 适合含OR操作符的查询
• update的代价高，因为要更新所有的bitmap
• 位图以一种压缩格式村反复，因此占用的磁盘空间比标准索引要小得多

create bitmap index <index_name> on <table_name>(<col_name>)

3.唯一索引
  当某列任意两行值都不等时，当建立主键或者唯一约束时唯一索引将会被创建。

create unique index <index_name> on <table_name>(<col_name>);

4.基于函数的索引
  可以在表中创建基于函数的索引，如果没有基于函数的索引，任何地方在列上执行了函数的查询都不能使用这个列的索引。因为索引保存的值只有是查询的值时才有用，索引只是键值对的形式存储，并不会去做函数运算。
例如：

--它不会使用job列上的索引
select * from emp where UPPER(job)='MGR';

--它会使用job列上的索引，但是查询的条件又不符合要求
select * from emp where job='MRG'

--此时可以用函数索引,这是对表达式UPPER(job)建立的索引，保存的值是UPPER(job)的结果
create index EMP$UPPER_JOB on emp(UPPER(job));

  函数包括：算术表达式、PL/SQL函数、程序包函数、SQL函数、自定义函数

CREATE INDEX <index_name> ON <table_name>(<function_name>(<column_name>))

注意： &emps&emps基于函数的索引非常有用，但在实现时必须小心。在表上创建的索引越多，INSERT、UPDATE 和 DELETE
语句的执行就会花费越多的时间。 注意:对于优化器所使用的基于函数的索引来说，必须把初始参数 QUERY_REWRITE _ ENABLED
设定为 TRUE。

5.组合索引
  当两个或者多个列经常一起出现在where条件中时，在这些列上同时创建组合索引。

特点：

• 组合索引的列的顺序是任意的
• 如果where子句中引用了组合索引的大多数列，则可以提高效率
• 建议将访问最频繁的列放在组合索引列的最前面

CREATE INDEX <index_name> ON <table_name>(<col_name1>,<col_name2>);

6.索引组织表(IOT)
  索引组织表会把表的存储结构改成 B 树结构，以表的主键进行排序，ROWID 并没有被关联到表的行上。这种特殊的表和其他类型的表一样，可以在表上执行所有的 DML 和 DDL 语句。
  对于一些涉及精确匹配和范围搜索的语句，索引组织表提供了一种基于键的快速数据访问机制。
  基于主键值的 UPDATE 和 DELETE 语句的性能也同样得以提高，这是因为行在物理上有序。由于键列的值在表和索引中都没有重复，存储所需要的空间也随之减少。
  如果不会频繁地根据主键列查询数据，则需要在索引组织表中的其他列上创建二级索引。
  必须指定主键。插入数据时，会根据主键列进行B树索引排序，写入磁盘。
  对于总是通过对主键的精确匹配或范围扫描进行访问的表，就需要考虑使用索引组织表，可以在索引组织表上建立二级索引 。
7.反向键索引
  当载入一些有序数据时，建立普通索引肯定会碰到与 I/O 相关的一些瓶颈。为了解决这个问题， 可以把索引表空间存放在能够把文件物理分割在多个磁盘上的磁盘体系结构上。详见：ORACLE索引的作用及用法

缺点：

• 由于反向索引结构自身的特点，如果系统中经常使用范围扫描进行读取数据的话（例如在where子句中使用“between and”语句或比较运算符“>”“<”等），那么反向索引将不适用，因为此时会出现大量的全表扫描的现象，反而会降低系统的性能。
• 有时候可以通过改写sql语句来避免使用范围扫描，例如where id between 12345 and 12347，可以改写为where id in(12345,12346,12347)，CBO会把这样的sql查询转换为where id=12345 or id=12346 or id=12347,这对反向索引也是有效的。
• 不可以将反转键索引与位图索引或索引组织表结合使用。因为不能对位图索引和索引组织表进行反转键处理。

create index <index_name> on <table_name>(column_name) reverse;

8.HASH 索引
  使用 HASH 索引必须要使用 HASH 集群。建立一个集群或 HASH 集群的同 时，也就定义了一个集群键。这个键告诉 Oracle 如何在集群上存储表。在存储数据时，所有与这个集群键相关的行都被存储在一个数据库块上。HASH 索引在有限制条件(需要指定一个确定的值而不是一个值范围)的情况下非常有用。

特点：

• 如果不能为集群的未来增长分配好附加的空间，HASH 集群可能就不 是最好的选择；
• 如果应用程序经常在集群表上进行全表扫描，HASH集群可能也不是最好的选择；
• 通常，HASH 对于一些包含有序值的静态数据非常有效；

四、总结

比较适合建立索引的列的特点：

1. 经常需要搜索的列上
2. 主键，一般建立唯一性索引，保持数据的唯一性
3. 外键，提高表与表之间连接的速度 需要排序的列上
4. where子句后边经常出现的字段
5. 经常需要根据范围进行搜索的列上，比如日期

不适合建立索引的列的特点：

1. 很少进行搜索的列
2. 列取值比较少的列上
3. blob类型的列上 修改频率比较高的列上

使用索引的限制：

1. 不能使用不等于<> 、 != ，（不等于操作符一定会进行全表扫描）
2. 使用is null 、 is not null（只要索引中出现一个null，那么这个索引就报废了。所以在建立索引的时候，一定要将准备建立索引的列设置为not null）
3. 使用函数（where子句中含有trunc()、add_months()之类）的时候，sql优化器会自动忽略掉索引
4. where子句中，进行了数据类型不匹配的比较，比如(where row_num = ‘1’)的时候，会限制索引的使用

索引查询：

• dba_indexes
• user_indexes
• uesr_ind_columns

参考文章：
1.https://www.cnblogs.com/xqzt/p/4449184.html Oracle中的rowid
2.http://www.cnblogs.com/wang-junxi/p/8111663.html Oracle索引详解（一）
3.http://www.cnblogs.com/wang-junxi/p/8120510.html Oracle索引详解（二）
4.https://www.cnblogs.com/sopost/p/4292895.html
5.https://blog.youkuaiyun.com/zq9017197/article/details/7321604 oracle 反向键索引的原理和用途
6.https://blog.youkuaiyun.com/qq_34895697/article/details/52425289 ORACLE索引的作用及用法