敖胤

绳锯木断，水滴石穿；聚沙成塔，集腋成裘。

导航

Oracle索引

在关系型数据库中，用户查找数据与行的物理位置无关紧要。为了能够找到数据，表中的每一行均用一个ROWID来标识，ROWID能够标识数据库中某一行的具体位置。当Oracle数据库中存储海量的记录时，就以为止有大量的ROWID标识，这时就需要使用索引对象来快速找到指定的ROWID。

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

将索引和对应的表分别放在不同硬盘的不同的表空间中能够提高查询的速度，因为Oracle能够并行读取不同硬盘的数据，这样可以避免差生I/O冲突。

索引分类

用户可以在Oracle中创建多种类型的索引，按照索引数据的存储方式可以将索引分为以下类型：

  • B/B+树索引
  • 位图索引
  • 反向键索引
  • 基于函数的索引

创建索引的注意事项

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

  1. 索引应该建立在WHERE子句频繁引用列表上，如果在大表上频繁使用某列或某几列作为条件执行索引操作，并且检索行数低于总行数15%，那么应该考虑咋这些列上建立索引。
  2. 如果经常需要基于某列或某几列执行排序操作，嘛呢在这些列上建立索引可以加快数据排序的速度。
  3. 适当限制标的索引个数。索引主要用于加快查询速度，但会降低DML操作的速度，索引越多，DML操作速度越慢，尤其会极大地影响INSERT和DELETE操作的速度。
  4. 指定索引块空间的使用参数。基于表建立索引时，Oracle会将相应表列数据添加到索引块。为索引块添加数据时，Oracle会按照PCTFREE参数在索引块上预留部分空间，该预留空间时为将来的INSERT操作准备的。
  5. 将表和索引部署到相同的表空间，可以简化表空间的管理；将表和索引部署到不同的表空间，可以提高访问效率。
  6. 当在大表上建立索引时，使用NOLOGGONG选项可以最小化重做记录。使用NOLOGGING选项可以节省重做日至空间、降低索引建立时间、提高索引并行建立的性能。
  7. 不要再小表上建立索引。
  8. 为了提高多表连接的性能，应该咋连接列上建立索引。

创建索引

在创建索引时，Oracle首相对将要建立索引的字段进行排序，然后将排序后的字段值和对应记录的ROWID存储在索引段中。建立索引可以使用CREATE INDEX语句，通常由表的所有者来建立索引。如果要以其他身份建立索引，则必须具有CREATE ANY INDEX系统权限或者相应表的INDEX对象权限。

B/B+树索引

B是英文balance的缩写。

B树索引是Oracle数据库最常用的索引类型（也是默认类型），它以B树结构组织并存放索引数据。默认情况下，B树索引中的数据是以升序方式排列的。

B树索引由根块、分支块、叶块组成。

  • 根块：索引顶级块，它包含指向下一级节点的信息。
  • 分支块：包含指向下一级节点（分支块或叶块）的信息。
  • 叶块：包含索引入口数据。索引入口数据包含索引列的值和对应行的物理地址ROWID。

在B树索引中，无论用户要搜索哪个分支的叶块，都可以保证所经过的索引层次是相同的，只需要花费相同的IO即可获取，这就是为什么称为B树索引。

B树索引创建语句：

CREATE INDEX 索引名 ON 表名(列1[,列2]...) [PCTFREE 预留大小值] [TABLESPACE 表空间名]
--如果表已经包含了大量数据，那么在建立索引时应该仔细规划PCTFREE的值，以便为以后的INSERT操作预留空间

位图索引

在B树索引中，通过在索引中保存排序的索引列的值以及记录的物理地址ROWID来实现快速查找。但对一些特殊的表，B树索引效率可能会很低。

例如，在某个具有性别列的表中，该列的所有取值只有0或1，若在此列建立B树索引，那么创建的B树只有两个分支，使用此索引查询时，可能返回接近一半的记录，这样就失去了索引的基本作用。

对于“基数低”（列中所有取值的数量远小于表中行的数量）的情况，适合建立位图索引。

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

位图索引创建语句：

CREATE bitmap INDEX 索引名 ON 表名(列) [TABLESPACE 表空间名]

反向键索引

当需要给一个值是自增的列建立索引时，若创建常规的B树索引，其优点非常明显：由于它无需再已有表项之间嵌入新的表项，因此不会发生叶子节点的数据块分割。这意味着单调递增列上的索引能够完全利用它的叶子结点，非常紧密的存放数据块，能有效利用存储空间。

然而，其缺点也很明显：新增的索引记录记录会占据最后的叶子节点，即使删除了先前的节点。这最终会导致对某一边的叶子结点的大量争夺。

为解决这种情况，Oracle提供了反向键索引。他可以将添加的数据随机分散到索引中。

反向键索引是一种特殊的B树索引，其原理非常简单。对于表中的记录，先将其值进行反向，再在新数据上建立索引。如索引列中的值为1234，则反向转换为4321，1073反向转换为3701。这样，对于递增的列，其反向键索引为非递增的。这样就解决了常规B树索引面临的困境。

反向键索引创建语句：

CREATE INDEX 索引名 ON 表名(列) REVERSE [TABLESPACE 表空间名]

基于函数的索引

本质上是常规的B树索引，但他存放的数据是由表中的数据应用函数后得到的，而不是表中的数据本身。

创建示例：

CREATE INDEX 索引名 ON 表名(lower(列))

修改索引

修改索引通常使用ALTER INDEX语句完成。一般情况下，修改索引是由索引的所有者完成的，如果其他用户想要修改，则必须有ALTER ANY INDEX系统权限或者在相应表上的INDEX对象权限。

为表建立索引之后，随着对表不断进行更新、插入和删除操作，索引中会产生越来越多的存储碎片，这对索引的工作效率会产生负面影响。这时可以采用两种方式来清除碎片——重建索引或合并索引。

合并操作只是将B树中叶子节点的存储碎片合并在一起，并不会改变索引的无力阻止结构。

合并操作语句：

ALTER INDEX 索引名 COALESCE DEALLOCATE UNUSED

消除索引碎片的另一个方法是重建索引，重建操作不仅可以消除存储碎片，还可以改变索引的全部存储参数设置以及存储表空间。重建实际上是在指定的表空间中创建一个新的索引，然后再删除原来的索引。因此，可以使用reverse子句修改索引反向，以及使用pctfree和tablespace修改属性。

重建操作语句：

ALTER INDEX 索引名 REBUILD [REVERSE] [PCTFREE 预留大小值] [TABLESPACE 表空间名]

删除索引

删除索引的语句是DROP INDEX。一般情况下，是由索引所有者完成，其他用户须有DROP ANY INDEX系统权限或在相应表上的INDEX权限才可操作。

需要删除索引的情形：

  • 移动了表中的数据，导致索引中包含过多的存储碎片。
  • 很少用到的索引。
  • 不再使用的索引。

索引被删除之后，它所占用的空间将返回给包含它的表空间。

删除语句：

DROP INDEX 索引名

如果索引是定义约束是由Oracle系统自动创建的，则必须禁用或删除该约束本身。另外，删除一个表时，Oracle会删除与该表有关的索引。

虽然一个表可以拥有任意数量的索引，但表中的索引的索引数据越多，维护索引所需要的开销也就越大。每次执行更新操作，都必须对所有索引进行更新。因此，用户还需要在标的查询速度和更新速度之间找一个平衡点。

查询索引

为了显示索引信息，Oracle提供了一系列的数据字典视图。通过查询这些视图，用户可以了解索引的各方面信息。

查询表的所有索引

通过查询数据字典视图dba_indexes可以显示数据库的所有索引；通过视图all_index可以显示当前用户可以访问的所有索引；查询视图user_indexes可以显示当前用户的索引。

查询索引表列信息

创建索引时，需提供相应的表列。通过查询视图dba_ind_columns可以显示所有索引的表列信息；查询all_ind_columns可以显示当前用户可以访问的所有索引的表列信息；查询user_ind_columns可以显示当前用户的索引的表列信息。

查询索引段信息

建立索引时，Oracle会为索引分配相应的索引字段，索引数据被存放在索引段中，并且段名与索引名完全相同。通过查询视图user_segments可以显示当前用户所拥有的的段分配信息。

查询函数索引

通过查询数据字典视图dba_ind_expressions可以显示数据库所有函数索引所对应的函数或者表达式；通过user_ind_expressions可以查看当前用户函数索引所对应的函数或者表达式。

索引失效

以下情形将导致索引失效：

  1. 隐式转换

    例如表的字段为varchar2，但查询时把该字段作为number类型传给了where条件。

    --err
select foo from tab where foo = 123;
--right
select foo from tab where foo = '123';

  2. 计算

    对索引列进行算术运算、取反以及函数运算。

    --err
select foo from tab where foo + 1 = 9;
select foo from tab where round(foo) = 3;
--right
select foo from tab where foo = 8;

  3. 使用了范围判断

    使用了<>、not in、not exist、!=、>、<、is not、or

    --err
select foo from tab where foo != 3;

  4. like以%开头

    模糊查询时，条件以%开头。

    --err
select foo from tab where foo like '%abc';

  5. 联合索引未引用第一位置列

    查询条件未落在联合索引建立时的第一位置列上。

posted on 2021-07-09 23:10  敖胤  阅读(579)  评论(0)  编辑  收藏  举报