Oracle 索引

索引概述：

在关系型数据库中，用户查找数据与行的物理位置无关紧要。为了能够找到数据，表中的每一行均用一个 ROWID 来标识 ，ROWID 能够标识数据库中某一行的具体位置。

如果一个数据表中存有海量的数据记录，当对表执行指定条件的查询时。常规的查询方法会将所 有的记录都读取出来，然后再把读取的每一条记录与查询条件进行比对，最后返回满足条件的记录。 这样进行操作的时间开销和 I/O 开销都很大。对于这种情况，就可以考虑通过建立索引来减小系统开销。

如果要在表中查询指定的记录，在没有索引的情况下，必须遍历整个表，而有了索引之后，只需 要在索引中找到符合查询条件的索引字段值，就可以通过保存在索引中的 ROWID 快速找到表中对应 的记录。

用户可以在 Oracle 中创建多种类型的索引，以适应各种表的特点。

索引分类：

按照索引数据的存储方式可以将索引分成：B树索引、位图索引、反向索引、基于函数的索引

按照索引列的唯一性可以分为：唯一索引、非唯一索引

按照索引列的个数可以分为：单列索引、复合索引

建立和规划索引时，必须选择合适的表和列，如果选择的表和列不合适，不仅无法提高查询速度，反而会极大的降低 DML 操作的速度，所以建立索引必须注意：

• 索引应该建立在 WHERE 子句频繁引用表列上，如果在大表上频繁使用某列或某几个列作为条件执行索引操作，并且检索行数低于总行数 15%，那么应该考虑在这些列上建立索引。
• 如果经常需要基于某列或某几个列执行排序操作，那么在这些列上建立索引可以加快数据排序速度。
• 限制表的索引个数。索引主要用于加快查询速度，但会降低 DML（数据库操作语言） 操作的速度。索引越多， DML 操作速度越慢，尤其会极大地影响 INSERT 和 DELETE 操作的速度。因此，规划索引时， 必须仔细权衡查询和 DML 的需求。
• 指定索引块空间的使用参数。基于表建立索引时，Oracle 会将相应表列数据添加到索引块。 为索引块添加数据时，Oracle 会按照 PCTFREE 参数在索引块上预留部分空间，该预留空间是为将来的 INSERT 操作准备的。如果将来在表上执行大量 INSERT 操作，那么应该在建立索引时设置较大的 PCTFREE。
• 将表和索引部署到相同的表空间，可以简化表空间的管理；将表和索引部署到不同的表空间， 可以提高访问性能。 （将索引和对应的表分别放在同的表空不同硬盘的不间中能够提高查询的速度，因为 Oracle 能够并行读取不同硬盘的数据，这样可以避免产生 I/O 冲突。）
• 当在大表上建立索引时，使用 NOLOGGING 选项可以最小化重做记录。使用 NOLOGGING 选项可以节省重做日志空间、降低索引建立时间、提高索引并行建立的性能。
• 不要在小表上建立索引。
• 为了提高多表连接的性能，应该在连接列上建立索引。

创建索引

在创建索引时，Oracle 首先对将要建立索引的字段进行排序，然后将排序后的字段值和对应记录的 ROWID 存储在索引段中。

B树索引

B 树索引是 Oracle 数据库最常用的索引类型（也是默认的），它是以 B 树结构组织并存放索引数据的。默认情况下，B 树索引中的数据是以升序方式排列的。如果表包含的数据非常多，并且经常在 WHERE 子句中引用某列或某几个列，则应该基于该列或这几个列建立 B 树索引。B 树索引由根块、分支块和叶块组成，其中主要数据都集中在叶子节点

根块：索引顶级块，它包含指向下一级节点的信息。

分支块：它包含指向下一级节点（分支块或叶块）的信息。

叶块：通常也称为叶子，它包含索引入口数据，索引入口包含索引列的值和记录行对应的物理地址 ROWID。

在 B 树索引中无论用户要搜索哪个分支的叶块，都可以保证所经过的索引层次是相同的。Oracle 采用这种方式的索引，可以确保无论索引条目位于何处，都只需要花费相同的 I/O 即可获取它，这就 是为什么被称为 B 树索引（B 是英文 BALANCED 的缩写）。

--create index Index_Name on table(字段名)

SQL> create index emp_deptno_index on emp(deptno)
 2 pctfree 25
 3 tablespace users;
索引已创建

--子句 PCTFREE 指定为将来 INSERT 操作所预留的空闲空间，子句 TABLESPACE 用于指定索引段所在的表空间。
--假设表已经包含了大量数据，那么在建立索引时应该仔细规划 PCTFREE 的值，以便为以后的 INSERT 操作预留空间。

位图索引

索引的作用简单地说就是能够通过给定的索引列值，快速地找到对应的记录。在 B 树索引中，通过在索引中保存排序的索引列的值以及记录的物理地址 ROWID 来实现快速查找。但是对于一些特殊的表，B 树索引的效率可能会很低。

例如，在某个具有性别列的表中，该列的所有取值只能是男或女。如果在性别列上创建 B 树索引， 那么创建的 B 树只有两个分支，如图 10.2 所示。那么使用该索引对该表进行检索时，将返回接近一半 的记录，这样也就失去了索引的基本作用。

 当列的基数很低时，为其建立 B 树索引显然不合适。“基数低”表示在索引列中，所有取值的数量比表中行的数量少。 如“性别”列只有两个取值；再比如某个拥有 10000 行的表， 它的一个列包含 100 个不同的取值，则该列仍然满足低基数的 要求，因为该列与行数的比例为 1%。Oracle 推荐当一个列的基数小于 1%时，这些列不再适合建立 B 树索引，而适用于位图索引。

位图索引介绍：https://www.cnblogs.com/LBSer/p/3322630.html

在表中低基数的列上建立位图索引时，系统将对表进行 一次全面扫描，为遇到的各个取值构建“图表”。

SQL> create bitmap index emp_salary_bmp
 2 on employees(salary)
 3 tablespace users;
索引已创建

初始化参数 CREATE_BITMAP_AREA_SIZE 用于指定建立位图索引时分配的位图区大小，默认值 为 8MB，该参数值越大，建立位图索引的速度越快。为了加快创建位图索引的速度，应将该参数设置 为更大的值。因为该参数是静态参数，所以修改后必须重新启动数据库才能生效。

反向键索引 

 在 Oracle 中，系统会自动为表的主键列建立索引，这个默认是普通的 B 树索引。通常，用户会希 望表的主键是一个自动增长的序列编号，这样的列就是所谓的单调递增序列编号列。当在这种顺序递 增的列上建立普通的 B 树索引时，如果表的数据量非常大，将导致索引数据分布不均。

分析：常规的B树索引

这是一个典型的常规 B 树索引。如果现在要为其添加新的数据，用于主键列的单调递 增性，很明显不需要重新访问早先的叶子节点。接下的数据获得的主键为 700，下一组数据的主键为 800，以此类推。

这种方法在某些方面是具有优势的，由于它不存在在已经存在的表项之间嵌入新的表项这一现象， 所以不会发生叶子节点的数据块分割。这意味着单调递增序列上的索引能够完全利用它的叶子节点， 非常紧密地存放数据块，可以有效利用存储空间。然而这种优势是需要付出代价的，每条记录都会占 据最后的叶子节点，即使删除了先前的节点，也会导致同样的问题。这最终会导致对某一边的叶子节 点的大量占用。

所以就需要设计一个规则，阻止用户在单调递增序列上建立索引后使用叶子节点偏向某一个方向。 遗憾的是，序列编号通常是用来做表的主键的，每个主键都需要建立索引，如果用户没有建立索引， Oracle 也会自动建立。但是，Oracle 提供另一种索引机制，即反向键索引，它可以将添加的数据随机 分散到索引中。

反向键索引是一种特殊类型的 B 树索引，在顺序递增列上建立索引时非常有用。反向键索引的工 作原理非常简单，在存储结构方面它与常规的 B 树索引相同。然而，如果用户使用序列在表中输入记 录，则反向键索引首先指向每个列键值的字节，然后在反向后的新数据上进行索引。例如，如果用户输入的索引列为 2011，则反向转换后为 1102；9527 反向转换后为 7259。需要注意，刚才提及的两个 序列编号是递增的，但是当进行反向键索引时却是非递增的。这意味如果将其添加到子叶节点，可能 会在任意的子叶节点中进行。这样就使得新数据在值的范围上的分布通常比原来的有序数更均匀。

SQL> connect scott/1qaz2wsx
已连接
SQL> create index emp_job_reverse
 2 on emp(job) reverse
 3 tablespace users;
索引已创建

--如果在该列上已经建立了普通 B 树索引，那么可以使用 ALTER INDEX…REBUILD 将其重新建立为反向键索引
SQL> connect scott/1qaz2wsx
已连接
SQL> alter index emp_deptno_index
 2 rebuild reverse;
索引已更改

 基于函数的索引

Oracle 提供了一种新的索引类型——基于函数的索引。基于函数的索引只是常 规 B 树索引，但它存放的数据是由表中的数据应用函数后所得到的，而不是直接存放表中的数据本身。

SQL> create index emp_job_fun
 2 on emp(lower(job));
索引已创建

在创建该函数索引之后，如果在查询条件中包含相同的函数，则系统会利用它来提高查询的执行效率。