主页 / dba / 问题 / 129504
Accepted
Prabhakar
Asked: 2016-02-18 01:16:07 +0800 CST

如何在 Oracle 中创建聚集索引和非聚集索引？

  • 772

我就我的问题挖了几个小时，但没有得到满意的答案。我还是有疑问。我发现以下关于聚集索引的内容：

  1. 数据按聚集索引的顺序存储。
  2. 每个表只有一个聚集索引。
  3. 创建主键时，也会自动创建聚集索引。

我得到了这些观点，但我的问题是：

  1. Oracle数据库中是否存在聚集索引？因为我在一些博客中读到“Oracle 没有聚集索引的概念”。
  2. 如果是，请告诉我创建聚集索引的 sql 语句。
  3. 如上所述，当在表的列上定义主键时，会自动创建聚集索引，如何检查索引类型是否已创建？

请找到我的表格架构：

在此处输入图像描述

让我知道是否需要其他任何东西来获得这些问题的答案。

oracle clustered-index

4 个回答

  1. Best Answer

    Oracle数据库中是否存在聚集索引？因为我读过一些博客

    就在这里。

    它被称为“索引组织表”（IOT）——在我看来这是一个更好的名字，因为它绝对清楚地表明索引和表是同一个物理事物（这就是我们只能有一个聚集索引的原因在 SQL Server 中）

    如果是，请告诉我创建聚簇索引的 SQL 语句。

    create clustered index在 Oracle中没有这样的东西。

    要创建索引组织表，请使用带有选项的create table语句。organization index

    在 Oracle 中，您通常将 IOT 用于非常窄的表。经常用于只包含主键列的表（例如 m:n 映射表），例如

    create table assignment
(
   person_id  integer not null, 
   job_id     integer not null, 
   primary key (person_id, job_id)
)
organization index;

    您可以创建具有更多列的 IOT，在这种情况下，您需要将非 pk 列定义为“包含”列。例如，如果分配表应该有其他列，例如不属于主键的开始日期和结束日期：

    create table assignment
(
   person_id   integer not null, 
   job_id      integer not null, 
   start_date  date, 
   end_date    date,
   primary key (person_id, job_id)
)
organization index
including start_date
overflow storage (initial 4k);

    有关更多详细信息和示例，请参阅手册：https ://docs.oracle.com/database/121/SQLRF/statements_7002.htm#i2153235

    有点不相关，但无论如何可能很有趣：

    一篇有趣的博客文章质疑 SQL Server（和 MySQL）在创建表时默认使用聚集索引的行为：

    不合理的默认值：主键作为聚类键

    来自 Oracle 背景，我完全同意这一点。

    • 24

  2. 我发现以下关于聚集索引的内容：

    1. 数据按聚集索引的顺序存储。
    2. 每个表只有一个聚集索引。
    3. 创建主键时，也会自动创建集群索引。

    你挖了几个小时。您应该已经注意到，以上所有事实都是针对SQL Server而不是 Oracle。

    Oracle 中的表（至少是普通表）没有聚集索引。有一种特殊的表，称为索引组织表 (IOT)，它们具有相似的概念。Oracle 文档 (9i)中的详细信息：索引组织表：

    什么是索引组织表？

    与普通表相比，索引组织表具有自己的结构化、存储和索引数据方式。与普通表的比较可能有助于解释其独特性。

    索引组织表与普通表

    普通表中的一行具有稳定的物理位置。一旦建立了这个位置，该行就永远不会完全移动。即使随着新数据的添加而部分移动，在原始物理地址处始终存在一行块——由原始物理 rowid 标识——系统可以从中找到该行的其余部分。只要行存在，它的物理rowid就不会改变。普通表中的索引既存储列数据又存储rowid。

    索引组织表中的行没有稳定的物理位置。它将数据按排序顺序保存在基于表的主键构建的 B*-tree 索引的叶子中。这些行可以移动以保留排序顺序。例如，插入可能会导致现有行移动到不同的插槽，甚至移动到不同的块。

    B*-tree 索引的叶子包含主键和实际的行数据。对表数据的更改（例如，添加新行或更新或删除现有行）只会导致更新索引。

    另请参阅有关(11g) 的文档：更新 Oracle 11g 版本中的索引组织表。

    • 10

  3. 目前无法创建聚集索引，但可以添加一些功能以实现几乎相同的结果：（B-Tree & Bitmap INDX） 1. STEP - 为一个或多个表创建集群 - 参考例如 PK -公共列 2. STEP - 将一些表添加到集群 3. STEP - 在 CLUSTER 中创建 CLUSTERED INDEX - 参见最后一个 sql stmt (INDEX_TYPE=CLUSTERED CLUSTERING_FACTOR=1)

    在此处输入图像描述

    • -2

  4. 创建集群 employees_departments_cluster (department_id NUMBER(4)) SIZE 512;

    创建表员工（department_id number(4)，ename varchar2(10)，empno int）CLUSTER employees_departments_cluster（department_id）；

    CREATE TABLE 部门 (department_id number(4), dname varchar2(10), sales int) CLUSTER employees_departments_cluster (department_id);

    在 CLUSTER employees_departments_cluster 上创建索引 idx_emp_dept_cluster；插入部门值（1，'d1'，1000）；插入员工值（1，'emp123'，123）；插入员工值（1，'emp124'，124）；插入员工值（1，'emp125'，125）；

    从 DBA_INDEXES 中选择 INDEX_NAME、INDEX_TYPE、TABLE_NAME、TABLE_TYPE、CLUSTERING_FACTOR WHERE INDEX_NAME=UPPER('idx_emp_dept_cluster') ；

    INDEX_NAME, INDEX_TYPE, TABLE_NAME, TABLE_TYPE, CLUSTERING_FACTOR IDX_EMP_DEPT_CLUSTER,CLUSTER,EMPLOYEES_DEPARTMENTS_CLUSTER,CLUSTER,1

    • -7

相关问题