Oracle INDEX 的几种类型
发布网友
发布时间：2022-04-22 05:25
共1个回答
热心网友
时间：2022-04-09 02:47
下面的情况，可以考虑使用索引：1） 大表2） 主键（自动索引）
3） 单键列（自动索引）
4） 外键列（自动索引）
5） 大表上WHERE子句常用的列
6） ORDER BY 或者GROUP BY子句中使用的列。
7） 至少返回表中20%行的查询
8） 不包含null值的列。
Oracle中的索引包含有如下几种类型：
B*树索引：这是Oracle中最常用的索引，它的构造类似于二叉树，能根据键提供一行或一个行集的快速访问，通常只需要很少的读操作就能找到正确的行。B*树索引由两列组成，第一列是ROWID, 它是行的位置；第二列是正被索引列的值。
图:典型的B*树索引布局
这个树底层的块称为叶子节点(leaf node) 或(leaf block),其中分别包含各个索引键以及一个rowid（它是指向所索引的行）。叶子节点之上的内部块称为分支块（branch block），这些节点用于实现导航。例如，如果想在索引中找到值20，要从树顶开始，找到左分支，我们检查这个块，并发现需要找到范围"20..25"的块，这个块将是叶子块，其中会指示包含数20的行。索引的叶子节点实际上构成了一个双向链表。一旦发现要从叶子节点中的那里开始，执行值的有序扫描（index range scan)就会很容易，我们就不必再在索引结构中导航：而只需根据叶子节点向前或向后扫描就可以了。
B*树的特点之一是：所有叶子块都应该在树的同一层上，这一层称之为索引的高度, 它说明所有从索引的根块到叶子块的遍历都会访问同样数目的块。也就是说，对于形如"SELECT INDEX_column FROM TABLE WHERE INXDEX_column =:X"的索引，要达到叶子块来获取第一行，不论使用的:X值是什么，都会执行同样数目的I/O,由此可见B*树的B代表的是balanced，所谓的"Height balanced"。大多数B*树索引的高度都是2或3，即使索引中有数百万行记录也是如此，这说明，一般而言，在索引中找到一个键只需要2到3次I/O , 这确实不错。
B*树是一个极佳的通用索引机制，无论是大表还是小表都很适用，随着底层表大小增长，获取数据的性能仅会稍有恶化。
比如，我们为customers表建立一个常见的B*树索引:
CREATE INDEX IDX_Cus_City on customers(city)
B*树索引有以下子类型：复合索引复合索引也是一种B*树索引,它由多列组成。当我们拥有使用两列或超过两列的频繁查询时，就使用B*树复合索引，而其所使用的两列或多列在where子句中and逻辑操作符连接。
因为复合索引中列的顺序很重要，所以确信以最有效的索引能排列他们，可以参考用作列排序的下面的两个准则 ：
1） 前导列应该是查询中使用最频繁的列。
2） 前导列应该是选择最多的列，这意味着它比后面的列具有更高的基数。
复合索引在下列情况中具有优势：1）假定在WHERE子句中频繁使用下面的条件：order_status_id = 1 和order_date = ‘dd-mon-yyyy’。如果为每一列创建一个索引，那么为了搜索列的值，两个索引都要被读取，但是如果为两列都创建一个复合索引，那么只有一个索引被读取，这样无疑比两个索引要求更少的I/O。
2) 使用前面例子中同样的条件，如果创建一个复合索引，将更快地检索行，因为你正在排除了所有order_status_id 不是1的行，从而减少了搜索order_date的行数。
反向键索引B*树索引的另一个特点是能够将索引键“反转”。首先，你可以问问自己“为什么想这么做？” B*树索引是为特定的环境、特定的问题而设计的。实现B*树索引的目的是为了减少“右侧”索引中对索引叶子块的竞争，比如在一个Oracle RAC 环境中，某些列用一个序列值或时间戳填充，这些列上建立的索引就属于“右侧”（right-hand-side）索引。RAC 是一种Oracle 配置，其中多个实例可以装载和打开同一个数据库。如果两个实例需要同时修改同一个数据块，它们会通过一个硬件互连（interconnect）来回传递这个块来实现共享，互连是两个（或多个）机器之间的一条专用网络连接。如果某个利用一个序列填充，这个列上有一个主键索引 ，那么每个人插入新值时，都会视图修改目前索引结构右侧的左块（见本文图一，其中显示出索引中较高的值都放在右侧，而较低的值放在左侧）。如果对用序列填充的列上的索引进行修改，就会聚集在很少的一组叶子块上。倘若将索引的键反转，对索引进行插入时，就能在索引中的所有叶子键上分布开（不过这往往会使索引不能得到充分地填充）。
反向键索引创建语句语法如下：CREATE INDEX index_name on table_name(column_name) REVERSE ;复制代码降序索引降序索引（descending index）是oracle 8i引入的，用以扩展B*树索引的功能，它允许在索引中以降序（从大到小的顺序）存储一列。在oracle8i及以上版本中，DESC关键字确实会改变创建和使用索引的的方式。
我们可以这样创建降序索引:
CREATE INDEX IDX_jobs_title on hr.jobs (job_title DESC); SET autotrace traceonly EXPLAIN;SELECT * FROM hr.jobs WHERE job_title Between 'a' AND 'ZZZZZZZZZZZ '; Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=33) 1 0 FILTER 2 1 TABLE ACCESS (BY INDEX ROWID) OF 'JOBS' (Cost=1 Card=1 B ytes=33) 3 2 INDEX (RANGE SCAN) OF 'IDX_JOBS_TITLE' (NON-UNIQUE) (C ost=2 Card=1) SQL> SELECT * from hr.jobs 2 WHERE job_title between 'a' and 'ZZZZZZZZZZZ '; Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=33) 1 0 FILTER 2 1 TABLE ACCESS (FULL) OF 'JOBS' (Cost=2 Card=1 Bytes=33) SQL> DROP INDEX IDX_jobs_title ;SQL> CREATE INDEX IDX_jobs_title on hr.jobs (job_title ); SQL> Select * FROM hr.jobs 2 Where job_title between 'a' and 'ZZZZZZZZZZZ '; Execution Plan---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=33) 1 0 FILTER 2 1 TABLE ACCESS (FULL) OF 'JOBS' (Cost=2 Card=1 Bytes=33)复制代码位图索引位图索引（bitmap index）是从Oracle7.3 版本开始引入的。目前Oracle 企业版和个人版都支持位图索引，但标准版不支持。位图索引是为数据仓库/在线分析查询环境设计的，在此所有查询要求的数据在系统实现时根本不知道。位图索引特别不适用于OLTP 系统，如果系统中的数据会由多个并发会话频繁地更新，这种系统也不适用位图索引。
位图索引是这样一种结构，其中用一个索引键条目存储指向多行的指针；这与B*树结构不同，在B*树结构中，索引键和表中的行存在着对应关系。在位图索引中，可能只有很少的索引条目，每个索引条目指向多行。而在传统的B*树中，一个索引条目就指向一行。
B*树索引一般来讲应当是选择性的。与之相反，位图索引不应是选择性的，一般来讲它们应该“没有选择性“。如果有大量在线分析查询，特别是查询以一种即席方式引用了多列或者会生成诸如COUNT 之类的聚合，在这样的环境中，位图索引就特别有用 。
