Oracle12文档索引和索引结构表

肆思

于 2021-07-18 10:29:39 发布

阅读量1.3k

点赞数

文章标签： oracle 索引

本文链接：https://blog.csdn.net/qq_40488600/article/details/118787919

版权

原本是要看sql优化，但是给的资料一开始先讲索引，但索引的描述又不是很清楚，于是来看oracle的文档

官方英文文档索引部分
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/cncpt/indexes-and-index-organized-tables.html#GUID-797E49E6-2DCE-4FD4-8E4A-6E761F1383D1
首先看看自己使用的oracle的版本，使用plsql输入如下内容看oracle的信息

select * from v$version

关于oracle各版本间的区别情况，由于见得少，不确认。出问题去看看别人吧
由于见识太少，一切以能够使用为核心，很多专有名词不大对，发现请提醒我
部分链接指向oracle的英文文档，如一些名词，在官方文档中最后有一个词汇表用来解释，还有一些参考文档

索引和索引结构表

索引是加速访问行内容的模式对象。索引结构表是存储索引结构的表

本章包含以下主题

索引介绍
B树索引概述
位图索引概述
基于函数的索引概述
应用域索引概述
索引结构表概述

索引介绍

索引是一种可选的结构，它常常与表或者表集群相关联，有时可以加快数据索引

索引是在逻辑结构和物理结构上独立于其相关联数据的模式对象。因此，你可以创建或删除索引而不影响到被索引的表

注意： 如果你删掉了索引，那么程序依然会运行。但是其访问之前索引的数据会变慢

比方说，有一个HR经理有一个纸箱架子。包含员工信息的文件被随机放入到箱子中。员工Whalen（id200）被放到箱子1中的第10个文件，King（id100）放到箱子3中的底部。为了定位到目标文件，经理需要对每个箱子从下到上依次寻找，直到找到文件。为了加快这个过程，经理可以创建一个索引，按顺序列出员工id和其文件。

ID 100: Box 3, position 1 (bottom)
ID 101: Box 7, position 8 
ID 200: Box 1, position 10
.
.
.

同样的，经理可以为员工的名字，部门ID等创立单独的索引

本节包含一下内容

索引的好处
索引的使用和显示属性
键和列
复合索引
唯一索引和非唯一索引
索引类型
数据库如何维护索引
索引存储

索引的好处

索引的存在与否并不需要sql语句更改

一个索引是对单行数据的快速访问。它只影响执行速度。给已经存在的值添加索引，那么索引直接指向包含该值的行的位置

当在一个表上有一个或多个索引存在时，在某些情况下数据库随机获取表中的一小组信息。索引是减少磁盘I/O众多方法之一。如果一个堆结构表没有索引，那么数据库必须全表扫描以获取值。例如，数据库从没有索引的hr.departments表中检索位置为2700的数据需要扫描每一块中的每一行数据。这种访问方式不会随着数据量的提升而变得更好。

通常，在以下情况的时候会考虑为列创建索引

索引的列频繁查询并且只需要表中的一小部分数据
在索引的列上有可参照的完整性约束。索引是一种避免全表锁定的方法，否则在更新父表主键、合并父表或删除父表时需要全表锁定。事务？
需要索引的项需要唯一约束？

索引的使用和显示属性

索引，可用(默认)/不可用，可见(默认)/不可见

下面是对此属性的定义

使用属性
被优化器忽略的不可用索引不会被DML操作维护。不可用索引可以提高批量加载的性能。你可以选择让索引处于不可用状态然后重启它而不是删掉索引再创建。不可用索引和索引分区不消耗空间。当设置可用索引为不可用时，数据库将删除其索引段。
显示属性
一个不可见索引由DML操作维护，但不被优化器在默认情况下使用。使索引不可见是使其不可用或删除它的替代方法。不可见索引对于在删除索引或临时使用索引而不影响整个应用程序之前测试索引的删除的时候特别有用。

相关阅读
“优化器概述”去了解优化器如何选择执行计划

键和列

键是建立索引的时候所用的一组列或表达式

尽管这些术语经常互换使用，但索引和键是不同的。索引是存储在用户使用 SQL 语句管理的数据库中的结构。键严格来说是一个逻辑概念。

以下语句创建了一个索引添加到样例表oe.orders中的列customer_id上

CREATE INDEX ord_customer_ix ON orders (customer_id);

在前面的语句中，customer_id列是索引的键。索引本身被命名为ord_customer_ix。

注意： 主键和唯一键自动具有索引，但您需要在外键上创建索引。

相关阅读

数据完整性
Oracle 数据库 SQL 语言参考 CREATE INDEX语法和语义

复合索引

复合索引也叫关联索引，即索引使用表中的多个列

按照对检索数据的查询最有意义的顺序将列放在复合索引中。表格中的列不必相邻。

复合索引可以加快SELECT语句的数据检索，WHERE子句使用了复合索引中全部或前导部分的列。因此，定义中使用的列的顺序很重要。通常，最常访问的列放到最前面。

例如，假设一个应用程序经常查询employees中的last_name，job_id，salary列。还假设last_name具有高基数。这意味着与表行数相比，不同值的数量很大。您使用以下列顺序创建索引：

CREATE INDEX employees_ix
   ON employees (last_name, job_id, salary);

访问所有三列、仅last_name列或仅last_name和job_id列的查询会使用此索引。在此示例中，不访问last_name列的查询不使用索引。

注意： 在某些情况下，例如当前导列的数量非常低时，数据库可能会使用此索引的跳过扫描（请参阅“索引跳过扫描”）。

当多个索引满足以下任一条件时，可以在同一个表上以相同的列顺序存在多个索引：

索引有不同的类型。
例如，您可以在相同的列上创建位图和 B 树索引。
索引使用不同的分区方案。
例如，您可以创建本地分区的索引和全局分区的索引。
索引具有不同的唯一性属性。
例如，您可以在同一组列上创建唯一索引和非唯一索引。

例如，对于相同的表列，可以以相同的顺序存在非分区索引、全局分区索引和本地分区索引。任何时候只能看到一个列数相同且顺序相同的索引。

此功能使您无需删除现有索引并使用不同属性重新创建它即可迁移应用程序。此外，当索引的键不断增加时，此功能在 OLTP 数据库中很有用，因为数据库将新条目插入到同一组索引块中。为了缓解这种“热点”，您可以将索引从非分区索引演变为全局分区索引。

如果同一组列上的索引在类型或分区方案上没有不同，那么这些索引必须使用不同的列排列。

CREATE INDEX employee_idx1 ON employees (last_name, job_id);
CREATE INDEX employee_idx2 ON employees (job_id, last_name);

相关阅读
Oracle 数据库 SQL 调优指南了解有关使用复合索引的更多信息

唯一索引和非唯一索引

索引可以是唯一的或不唯一的。唯一索引保证表的两行在一个或多个键列中没有重复的值。

例如，您的应用可能要求没有两个员工具有相同的员工 ID。在唯一索引中，每个数据值都存在一个行号。叶块中的数据仅按键排序。

非唯一索引允许索引列中有重复值。例如，表的first_name列employees可能包含多个Mike值。对于非唯一索引，行号按排序顺序包含在键中，因此非唯一索引按索引键和行号（升序）排序。

Oracle 数据库不会索引所有键列都为null 的表行，位图索引或集群键列值为 null 时除外。

索引类型

Oracle 数据库提供了多种索引方案，它们提供了互补的性能功能。

B树索引是标准的索引类型。它们非常适合高选择性索引（每个索引条目对应的行数很少）和主键索引。用作关联索引，B树索引可以按索引列排序的数据进行检索。B树索引具有下表所示的子类型。

表 3-1 B树索引子类型

B树索引子类型	描述	了解更多
索引结构表	索引结构表不同于堆结构，因为数据本身就是索引。	“索引组织表概述”
反向键索引	在这种类型的索引中，索引键的字节是颠倒的，例如，103 存储为 301。字节的颠倒将插入索引分散到许多块上。	“反向键索引”
降序索引	这种类型的索引以降序存储特定列或列上的数据。	“升序和降序索引”
B树集群索引	这种类型的索引以降序存储特定列或列上的数据。	“升序和降序索引”

下表显示了不使用 B 树结构的索引类型。

表 3-2 不使用 B树结构的索引

类型	描述	了解更多
位图和位图连接索引	在位图索引中，索引条目使用位图指向多行。相比之下，B树索引条目指向单行。位图连接索引是用于连接两个或多个表的位图索引。	“位图索引概述”
基于函数的索引	这种类型的索引包括由函数（如UPPER函数）转换或包含在表达式中的列。B树或位图索引可以是基于函数的。	“基于函数的索引概述”
应用域索引	用户为特定于应用程序的域中的数据创建这种类型的索引。物理索引不需要使用传统的索引结构，可以作为表存储在Oracle数据库中，也可以作为文件存储在外部。	《应用域索引概览》