详解Oracle表的类型（一）

1.引言：

Oracle数据库提供了多种表类型，以满足不同的数据存储和管理需求。本博文将对Oracle中常见表类型及使用场景进行详细介绍。

2. Oracle的表类型

2.1 普通表（Regular Table/Heap Table）

2.1.1 定义：

普通表是Oracle中最常见的表类型，也是默认的表类型。它是一种没有特殊属性或结构的表，数据按照插入的顺序存储，这种类型的表也是堆组织表。

2.1.2 特点：

1. 存储快、读取慢。
2. 数据记录在表中的位置是随机的。
3. 简单易用，创建和维护方便。
4. 可以存储任意类型的数据，并支持各种查询和操作。

2.2 索引组织表（Index-Organized Table, IOT）

2.2.1 定义：

索引组织表是一种特殊类型的表，数据存储在索引结构中，而不是普通堆表的数据块中。

2.2.2 特点：

1. 存储慢、读取快。数据存储在索引结构中，可以更快地定位和访问数据，避免了回表操作。
2. 数据紧凑，减少了数据块之间的碎片，提高了存储效率。
3. 适用于需要频繁根据索引进行查询的表，以及数据更新较少的场景。

2.3 分区表（Partitioned Table）

2.3.1 定义：

分区表是一种将表数据分割成多个逻辑部分的表。每个分区可以独立进行管理，可以根据分区键进行数据的快速查询和维护。

2.3.2 特点：

1. 提高查询性能：可以根据查询条件仅扫描相关分区，实现有效的分区消除，减少数据的访问量。
2. 管理灵活性：可以独立管理每个分区，包括备份、恢复和维护操作。
3. 数据划分：可以根据业务需求将数据按照时间、地理位置等规则进行划分。
4. 适用于大型表和历史数据的存储，可以显著提高查询性能和管理效率。

2.4 临时表（Temporary Table）

2.4.1 定义：

临时表用于存储临时数据或中间结果。

2.4.2 特点：

1. 数据在会话结束后会自动清空。
2. 提供临时存储，避免在应用程序中创建临时表或使用其他临时存储方式。
3. 数据仅在会话期间存在，不占用永久存储空间。
4. 适用于存储临时数据或中间结果，特别是在复杂查询或大数据量的情况下。

2.5 全局临时表（Global Temporary Table）

2.5.1 定义：

全局临时表是一种特殊类型的临时表，数据在事务级别或会话级别存在。

2.5.2 特点：

1. 数据在事务提交或会话结束后会自动清空。
2. 适用于多用户环境，每个会话可以独立使用全局临时表。
3. 高效使用空间，避免在应用程序中创建临时表或使用其他临时存储方式。

2.6 簇表（Cluster Table）

2.6.1 定义：

簇表是一种特殊的存储数据方式，它将一组经常一起使用的表中相同的列存储在相同的数据块中。

2.6.2 特点：

1. 连接查询性能高：不同表中相同的列存储在一起，减少了连接表带来的磁盘I/O，降低了系统开销。
2. 节省存储空间：原来需要单独存放在多张表的列，现在作为共享列来存储。
3. 适用于关联数据、范围查询和数据紧凑要求高的场景。

2.7 外部表（External Table）

2.7.1 定义：

外部表是一种指向外部数据源的表，通过调用外部表定义文件来访问外部数据。

2.7.2 特点：

1. 无需将数据导入数据库即可进行查询操作。
2. 适用于向数据库加载数据或从数据库卸载数据的场景。

2.8 其他表类型

1. 嵌套表：Oracle对象关系扩展的一部分，是系统生成和维护的父/子关系中的子表。
2. 对象表：基于某种对象类型创建的表，拥有非对象表所没有的特殊属性，如系统会为对象表的每一行生成REF（对象标识符）。
3. 只读表：数据只能被查询，不能被修改、删除或插入新数据的表。

注意： Oracle数据库提供了多种表类型以满足不同的数据存储和管理需求。在选择表类型时，应根据具体的应用场景、性能要求和数据特性进行综合考虑。

下面详细介绍最常用的两种类型的表，即索引组织表和分区表。

3. 索引组织表

3.1 结构特点

1. 索引组织表（Index - Organized Tables，IOT）是一种特殊的表结构。在传统的表（堆表）中，数据和索引是分开存储的，数据以无序的方式存放在表的数据段中，索引则是另外存储用于加速数据的访问。而 IOT 的数据本身是按照索引结构来存储的，没有独立的数据段。
2. 它以 B - 树索引结构来组织数据，表中的行数据按照主键值的顺序存储在索引的叶子节点中。这就意味着主键列的值不仅用于唯一标识行，还用于确定数据在表中的物理存储顺序。例如，在一个以学生学号为主键的学生信息索引组织表中，学生记录会根据学号从小到大（或者定义的其他主键排序规则）依次存储在表中。

3.2 存储方式细节

1. 非键列数据存储：除了主键列外，其他非键列的数据也存储在索引结构中。在 B - 树索引的叶子节点中，首先存储主键列的值，然后是其他非键列的值。这种存储方式使得通过主键访问数据时，能够同时获取到其他相关列的数据，而不需要额外的数据查找操作。
2. 溢出存储：如果非键列的数据量比较大，导致一个索引块无法存储完整的行数据，Oracle 会使用溢出存储区域。部分非键列的数据会被存储到溢出段中，而在索引叶子节点中会存储一个指针，用于指向溢出段中的数据。这样可以在一定程度上平衡索引结构的紧凑性和数据存储的完整性。

3.3 创建方式

3.3.1 语法：

基本的创建语句如下：

CREATE TABLE [schema_name.]table_name
(column1 datatype [constraint],
column2 datatype [constraint],
…
CONSTRAINT pk_name PRIMARY KEY (column_list))
ORGANIZATION INDEX;

3.3.2 例如，创建一个简单的员工索引组织表，以员工编号（employee_id）为主键：

CREATE TABLE iot_employees
(employee_id NUMBER(10),
employee_name VARCHAR2(100),
department_id NUMBER(5),
CONSTRAINT pk_iot_employees PRIMARY KEY (employee_id))
ORGANIZATION INDEX;

3.3.3 注意事项：

1. 必须定义主键： 由于数据是按照主键顺序存储的，所以主键是索引组织表不可或缺的一部分。没有主键，就无法确定数据的存储顺序。
2. 考虑数据大小和列类型： 在设计表结构时，要考虑非键列的数据大小。如果有大对象（LOB）类型的列，如 CLOB（字符大对象）或 BLOB（二进制大对象），可能会导致更多的溢出存储情况，需要合理规划存储。

3.4 查询性能优势

1. 主键查询高效：由于数据是按照主键顺序存储在索引结构中，当通过主键进行查询时，只需要很少的 I/O 操作就能定位到目标数据。例如，查询特定员工编号的员工信息，数据库可以直接沿着 B - 树索引的分支快速找到对应的叶子节点，获取员工记录。
2. 范围查询性能：对于基于主键的范围查询，如查询员工编号在某个区间内的所有员工信息，索引组织表也能表现出很好的性能。因为数据在物理存储上已经按照主键顺序排列，数据库可以顺序读取索引叶子节点中的数据，避免了大量的随机 I/O 操作。

3.5 更新操作影响

3.5.1 插入操作：

当插入新数据时，如果插入的主键值符合当前表中的主键顺序，插入操作相对简单。数据库只需要找到合适的位置，在 B - 树索引的叶子节点中插入新行即可。但如果插入的主键值不符合顺序，可能会导致索引结构的调整。例如，插入一个较小主键值的记录到一个已经有较大主键值存储的表中，可能会引起索引节点的分裂，这会消耗一定的系统资源。

3.5.2 更新操作：

如果更新操作涉及主键列，那么实际上是对数据的物理存储位置进行重新排序。这可能会导致大量的数据移动和索引结构的重新调整。如果更新操作只涉及非键列，且不会导致数据大小超过索引块的限制，那么影响相对较小，只需要在原索引叶子节点中更新数据即可。

4. 索引组织表适用场景

4.1 数据字典和代码表

4.1.1 场景描述

在企业级应用系统中，数据字典表用于存储各种代码和对应描述，如国家代码表（国家代码为主键，包含国家名称、国家简称等信息）、产品类别代码表（产品类别代码为主键，包含类别名称、上级类别代码等）。这些表中的数据相对稳定，更新频率较低。

4.1.2 优势

1. 高效的主键查询：索引组织表以主键为索引存储数据，在查询国家代码对应的国家名称或者产品类别代码对应的类别名称等操作时，能够快速定位数据。因为数据按照主键顺序存储，通过主键查询时可以直接沿着索引结构定位到目标数据，减少磁盘 I/O 操作。
2. 维护数据一致性：由于更新频率低，不用担心频繁插入或更新对索引组织表结构造成的不良影响。而且，这种存储方式有助于保持数据的有序性，方便数据的管理和维护。

4.2 信息检索系统中的分类目录表

4.2.1 场景描述

在信息检索系统中，如文档管理系统的文档分类目录表。以文档分类 ID 为主键，存储分类名称、分类描述、父分类 ID 等信息。用户经常需要根据分类 ID 查找特定分类的详细信息，或者按照分类层次进行导航。

4.2.2 优势

1. 快速的分类信息查询：当用户查询特定文档分类的详细信息时，索引组织表能够提供快速响应。例如，查询文档分类 ID 为 “1001” 的分类名称和描述，系统可以高效地定位到存储该分类信息的索引叶子节点。
2. 便于层次结构查询：对于文档分类的层次结构查询，如查找某个分类的所有子分类或者父分类，由于数据是有序存储的，通过适当的 SQL 查询结合索引组织表的存储特性，可以更方便地实现这种层次结构的检索。

4.3 基于主键频繁访问的业务表

4.3.1 场景描述

在一些业务场景中，存在某些关键业务表，其大部分操作是基于主键进行的。例如，在一个在线票务系统中，票务详情表以票务编号为主键，存储票务状态、演出场次、座位号等信息。系统在售票、验票、退票等操作过程中，经常需要根据票务编号查询或更新票务详情。

4.3.2 优势

1. 高性能的主键操作：对于基于票务编号的查询操作，索引组织表可以快速定位到相应的票务详情。在高并发的售票场景下，能够快速响应用户的查询请求，如查询某张票的座位号或者票务状态，提高用户体验。
2. 优化的更新操作（非主键）：虽然票务详情表可能会有更新操作，但如果更新操作主要涉及非主键列（如票务状态），且不会导致数据大小超出索引块限制，那么更新操作的影响相对较小，只需要在原索引叶子节点中更新数据即可。

4.4 系统配置参数表

4.4.1 场景描述

系统配置参数表用于存储应用系统的各种配置参数，如数据库连接参数（以参数名称为主键，包含参数值、参数说明等）、系统日志级别设置（以日志级别名称为主键，包含对应的日志输出格式、存储路径等）。这些参数在系统运行过程中可能会被读取，但更新频率相对较低。

4.4.2 优势

1. 高效的参数读取：当系统启动或者运行过程中需要读取特定配置参数时，如获取数据库连接的用户名和密码，索引组织表能够快速提供这些信息。由于数据是按照主键存储的，通过主键查询可以快速定位到存储参数值的索引叶子节点。
2. 稳定的存储结构：因为参数更新不频繁，索引组织表的结构不会因为频繁的插入或更新而频繁调整，能够保持良好的性能，并且有助于确保参数存储的有序性，方便系统管理和维护。

未完待续。
关注作者，您将获得更多OCP考试及Oracle DB方面的实战经验。后续不定期分享DB核心知识和排障案例及经验、性能调优等