Oracle数据库表：索引组织表与索引集群表详解

### Oracle数据库表：索引组织表与索引集群表详解 #### 1. 索引组织表（IOT）的索引 在索引组织表（IOT）中，PCTTHRESHOLD 是一个重要参数。例如，若设置为 5（即 5%），则索引叶子块上每行数据的每个部分占用的块空间不应超过 5%。 IOT 的索引有其独特之处，存在二级索引，类似于在索引上再建索引。普通索引包含指向行的物理地址（rowid），但 IOT 的二级索引不能这样做，因为 IOT 中的行可能会频繁移动，它基于主键值处于索引结构的特定位置，移动往往是由于索引本身的大小和形状改变。为解决此问题，Oracle 引入了逻辑 rowid，它基于 IOT 的主键，可能包含对行当前位置的猜测，但此猜测通常不准确，因为 IOT 中的数据会移动。 与普通堆组织表的索引相比，IOT 的索引效率稍低。普通表的索引访问通常只需扫描索引结构和读取一次表数据，而 IOT 通常需要两次扫描：一次是二级索引结构，另一次是 IOT 本身。不过，IOT 的索引能通过非主键列快速高效地访问数据。 #### 2. 索引组织表的总结 IOT 设置中最关键的是平衡索引块上的数据和溢出段中的数据。可以通过不同溢出条件进行基准测试，观察对 INSERT、UPDATE、DELETE 和 SELECT 操作的影响。 - 若结构一次性构建且频繁读取，应尽量将更多数据存储在索引块上。 - 若结构频繁修改，则需在将所有数据放在索引块（利于检索）和频繁重组索引数据（不利于修改）之间取得平衡。 FREELIST 对堆表的考虑同样适用于 IOT。PCTFREE 和 PCTUSED 在 IOT 中有两个作用：PCTFREE 对 IOT 的重要性远低于堆表，PCTUSED 通常不起作用。但在考虑溢出段时，PCTFREE 和 PCTUSED 的含义与堆表相同，可按堆表的逻辑进行设置。 #### 3. 索引集群表的概念 很多人对 Oracle 中集群的理解不准确，常将其与 SQL Server 或 Sybase 的“聚集索引”混淆。实际上，集群是将共享某些公共列的一组表存储在相同数据库块中，将相关数据存储在同一块上的方式。SQL Server 的聚集索引会强制按索引键对行进行排序存储，类似于 IOT；而集群的单个数据块可能包含多个表的数据，概念上是“预连接”存储数据，也可用于按某列分组存储单个表的数据。例如，部门 10 的所有员工记录会存储在同一块（或尽可能少的块）上，但数据并非按排序存储，而是按某个键进行堆存储。 下面通过一个 mermaid 流程图展示传统表存储和集群表存储的区别： ```mermaid graph LR classDef process fill:#E5F6FF,stroke:#73A6FF,stroke-width:2px A(传统表存储):::process --> B(EMP表存储在其段):::process A --> C(DEPT表单独存储):::process D(集群表存储):::process --> E(提取公共值存储一次):::process E --> F(部门10相关表数据存储在一块):::process F --> G{数据是否溢出?}:::process G -->|是| H(添加溢出块):::process G -->|否| I(存储完成):::process ``` #### 4. 创建索引集群对象 创建索引集群对象的步骤如下： 1. **创建集群**：使用 `CREATE CLUSTER` 语句，类似 `CREATE TABLE` 语句，但只需少量列（集群键列）。例如： ```sql $ sqlplus eoda/foo@PDB1 SQL> create cluster emp_dept_cluster ( deptno number(2) ) size 1024; ``` 这里创建了一个索引集群，集群列是 `DEPTNO`，`SIZE 1024` 表示每个集群键值预计关联约 1024 字节的数据，Oracle 会据此计算每个块可容纳的最大集群键数量。 2. **创建集群索引**：在插入数据前，需要为集群创建索引。例如： ```sql SQL> create index emp_dept_cluster_idx on cluster emp_dept_cluster; ``` 此索引用于根据集群键值返回包含该键的块地址。 3. **创建表并插入数据**：在集群中创建表时，使用 `CLUSTER` 关键字指定集群和映射的列。例如： ```sql SQL> create table dept ( deptno number(2) primary key, dname varchar2(14), loc varchar2(13)) cluster emp_dept_cluster(deptno); SQL> create table emp ( empno number primary key, ename varchar2(10), job varchar2(9), mgr number, hiredate date, sal number, comm number, deptno number(2) references dept(deptno)) cluster emp_dept_cluster(deptno); SQL> insert into dept ( deptno, dname, loc ) select deptno+r, dname, loc from scott.dept, (select le ```