Oracle数据库索引组织表概述

Oracle数据库索引组织表概述索引组织表介绍索引组织表是存储在 B-树索引结构的一种变体中的表。在索引组织表中表中的数据行被存储在表的主键索引中与之相对的默认的堆组织表中行被插入到适合他们的位置。表必须定义主键。在 B-树中的每个索引条目也存储非键列值。因此索引即是数据数据也是索引。同堆组织表一样应用程序使用 SQL 语句操作索引组织表。有些数据库默认就是以索引组织表方式组织存放数据例如MySQL数据库。通过主键或有效的前缀索引组织表提供了对表行更快的访问。非键列存在于索引叶块中避免了额外的数据块 I/O。例如雇员 100 的薪酬存储在索引行本身中。此外因为行是以主键顺序存储的通过主键或前缀进行范围访问只涉及最少的块 I/O。另一个好处是避免一个单独的主键索引的空间开销。当有关的数据片断必须存储在一起或数据必须按特定的顺序物理地存储时索引组织表很有用。这种类型的表通常用于信息检索、 空间 、和 OLAP 应用程序简单创建案例create table t_iot1( ID varchar2 ( 10 ), NAME varchar2 ( 20 ), constraint pk_id primary key ( ID ) ) organization index tablespace users;索引组织表特征数据库系统通过操作 B-树索引结构来在索引组织表上执行所有操作。下面总结了索引组织表和堆组织表之间的差异堆组织表 索引组织表 由 rowid 唯一地标识行。定义主键约束是可选选项。 主键唯一地标识行。必须定义主键约束。 允许在 ROWID 伪列中的物理 rowid上创建辅助索引。 允许在 ROWID 伪列中的逻辑rowid 上创建辅助索引。 可直接由 rowid 访问单个行。 可间接通过主键访问单个行。 顺序全表扫描按一定顺序返回所有行。 完全索引扫描或快速完全索引扫描按一定顺序返回所有行。 可与其他表一起存储在表簇中。 不能存储在表簇中。 可以包含一个 LONG 数据类型的列和多个 LOB 数据类型的列。 可以包含 LOB 列但不能包含LONG 列。 可以包含虚拟列 只支持关系型堆表。 不能包含虚拟列。下图显示了一个索引组织表 departments 的结构。叶块包含的表行按主键顺序排序。例如在第一个叶块中的第一个值显示其部门 ID 20部门名称为 Marketing、经理 ID 为 201、 位置 ID 为 1800。索引组织表将所有数据都存储在相同的结构中且不需要存储 rowid。如上图索引组织表中的叶块 1 可能包含如下按主键排序的条目20,Marketing,201,180030,Purchasing,114,1700索引组织表中的叶块 2 可能包含如下所示的条目50,Shipping,121,150060,IT,103,1400按主键顺序对索引组织表行的扫描依照如下顺序读取块块1------块2对比索引组织表和堆组织表中的数据访问假定 departments 堆组织表段的块 1 包含的行如下所示50,Shipping,121,150020,Marketing,201,1800同一个表中块 2 包含的行如下所示30,Purchasing,114,170060,IT,103,1400此堆组织表的 B-树索引中的一个叶块包含如下条目其中的第一个列值是主键第二个列值是 rowid20,AAAPeXAAFAAAAAyAAD30,AAAPeXAAFAAAAAyAAA50,AAAPeXAAFAAAAAyAAC60,AAAPeXAAFAAAAAyAAB按主键顺序对表行的扫描依照如下顺序读取块块1 ------块2------块1------块2因此在此示例中的块 I/O 数目是在索引组织表示例中的 2 倍。索引组织表的行溢出区在创建一个索引组织表时可以指定一个单独的段为行溢出区。索引组织表的 B-树索引条目可能比较大因为它们包含整个行因此用一个单独的段来包含这些条目是很有用的。相比之下常规 B-树条目则通常很小因为它们仅包含键和 rowid。如果指定了行溢出区那么数据库可以将索引组织的表中的行分成以下两个部分**索引条目:**本部分包含所有主键列的值、指向该行溢出部分的物理 rowid、 或可选的几个非键列的值。这部分存储在索引段中。**溢出部分:**此部分包含剩余的非键列的值。这部分存储在溢出存储区段中。两种溢出选项PCTTHRESHOLD n 制定一个数据块的百分比当行数据占用大小超出时该行的其他列数据放入溢出段INCLUDING column_name 指定列之前的列都放入索引块之后的列都放到溢出段注意当行中某字段的数据量无法确定时使用PCTTHRESHOLD。若所有行均超出PCTTHRESHOLD规定大小则考虑使用INCLUDING。案例create table t_iot( ID varchar2 ( 10 ), NAME varchar2 ( 20 ), constraint pk_id primary key ( ID ) ) organization index PCTTHRESHOLD 20 overflow tablespace users INCLUDING name ; 如上例所示name及之后的列必然被放入溢出列而其他列根据 PCTTHRESHOLD 规则。索引组织表的辅助索引辅助索引是一个建立在索引组织表上的索引。在某种意义上它是一个索引的索引。辅助索引是一个独立的模式对象并与索引组织表分开存储。如Rowid 数据类型中所述数据库使用叫做逻辑 rowids 的行标识符来访问索引组织表。逻辑的 rowid 是表主键的 base64 编码表示形式。逻辑rowid 的长度取决于主键长度。由于插入操作索引叶块中的行需要在块内或块之间移动保持主键顺序索引组织表中的行像堆组织表那样迁移行。因为索引组织表中的行并没有永久的物理地址数据库使用基于主键的逻辑 rowids。例如假设 departments 表是索引组织表。location_id 列中存储每个部门的 ID。表像如下所示这样存储行最后一个值是是位置 ID 10,Administration,200,1700 20,Marketing,201,1800 30,Purchasing,114,1700 40,Human Resources,203,2400 在 location_id 列上的辅助索引可能有类似如下的索引条目逗号之后的值是逻辑 rowid 1700,*BAFAJqoCwR/ 1700,*BAFAJqoCwQv 1800,*BAFAJqoCwRX 2400,*BAFAJqoCwSn辅助索引对索引组织表提供快速、 高效的访问但使用的列既不是主键也不是主键前缀。例如查询其 ID 大于 1700 的部门的名称可以使用辅助索引以加快数据访问。逻辑 Rowids 和物理猜想辅助索引使用逻辑 rowids 来查找表行。逻辑的 rowid 包括一个物理猜测是索引条目第一次被创建时的物理 rowid 。Oracle 数据库可以使用物理猜测直接探入索引组织表的叶块以绕过主键搜索。当行的物理位置更改了即使它包含的物理猜测已经过时其逻辑 rowid 仍然保持有效。因此逻辑rowids不一定每次准确对于堆组织表按辅助索引访问涉及对辅助索引的扫描和一个读取行所在数据块的额外 I/O。对于索引组织表按辅助索引访问取决于物理猜测的使用及其准确性不用物理猜测则访问包括两个索引扫描 先是一个对辅助索引的扫描然后是一个对主键索引的扫描。使用物理猜测则访问取决于其准确性如果物理猜测准确则访问包括一个辅助索引扫描和一个读取行所在数据块的额外 I/O。如果物理猜测不准确则访问包括一个辅助索引扫描和一个读取错误的数据块 I/O 即猜测所指出的)再是一个按主键值对索引组织表的唯一索引扫描。索引组织表上的位图索引索引组织表上的辅助索引可以是位图索引。位图索引为每个索引键存储一个位图。当索引组织表上存在位图索引时所有位图索引都使用堆组织映射表。映射表将存储索引组织表的逻辑 rowids。每个映射表行会为相应的索引组织表行存储一个逻辑 rowid。数据库使用搜索键来访问位图索引。如果数据库找到了该键则该位图条目被转换为一个物理 rowid。对堆组织表数据库使用物理 rowid 来访问基表。对索引组织表数据库使用物理 rowid 来访问映射表这样会生成一个逻辑 rowid 以被数据库用来访问索引组织表。下图演示了一个通过索引访问 departments_iot 表的查询。注意索引组织表中的行移动不会使建立在该索引组织表上的位图索引变得不可用。