database 我什么时候应该使用 Oracle 的索引组织表？或者，我什么时候不应该？

When should I use Oracle's Index Organized Table? Or, when shouldn't I?

提问by Mr. Red

Index Organized Tables (IOTs) are tables stored in an index structure. Whereas a table stored in a heap is unorganized, data in an IOT is stored and sorted by primary key (the data is the index). IOTs behave just like “regular” tables, and you use the same SQL to access them.

索引组织表 (IOT) 是存储在索引结构中的表。存储在堆中的表是无组织的，而 IOT 中的数据是按主键存储和排序的（数据是索引）。IOT 的行为就像“常规”表一样，您可以使用相同的 SQL 来访问它们。

Every table in a proper relational database is supposed to have a primary key... If every table in my database has a primary key, should I always use an index organized table?

适当的关系数据库中的每个表都应该有一个主键......如果我的数据库中的每个表都有一个主键，我应该总是使用索引组织的表吗？

I'm guessing the answer is no, so when is an index organized table notthe best choice?

我猜答案是否定的，那么索引组织表何时不是最佳选择？

回答by APC

Basically an index-organized table is an index without a table. So if you have a table whose columns consist of the primary key and at most one other column then you have a possible candidate for INDEX ORGANIZED.

基本上一个索引组织的表是一个没有表的索引。因此，如果您有一个表，其列由主键和最多一个其他列组成，那么您就有一个可能的 INDEX ORGANIZED 候选项。

But if you find yourself contemplating the need for additional indexes on the non-primary key columns then you're probably better off with a regular heap table. So, as most tables probably need additional indexes most tables are not suitable for IOTs.

但是，如果您发现自己正在考虑在非主键列上增加索引的必要性，那么使用常规堆表可能会更好。因此，由于大多数表可能需要额外的索引，因此大多数表不适合 IOT。

In practice, index organized tables are most likely to be reference data, code look-up affairs. Application tables are almost always just heap organized.

在实践中，索引组织的表最有可能是引用数据、代码查找事务。应用程序表几乎总是只是堆组织的。

回答by Gary Myers

I'd consider them for very narrow tables (such as the join tables used to resolve many-to-many tables). If (virtually) all the columns in the table are going to be in an index anyway, then why shouldn't you used an IOT.

我会考虑将它们用于非常窄的表（例如用于解析多对多表的连接表）。如果（实际上）表中的所有列都将在索引中，那么为什么不使用 IOT。

Small tables can be good candidates for IOTs as discussed by Richard Foote here

正如 Richard Foote在此处讨论的那样，小表可以成为 IOT 的良好候选者

回答by Jeffrey Kemp

I consider the following kinds of tables excellent candidates for IOTs:

我认为以下类型的表格非常适合物联网：

• "small" "lookup" type tables (e.g. queried frequently, updated infrequently, fits in a relatively small number of blocks)
• any table that you already are going to have an index that covers all the columns anyway (i.e. may as well save the space used by the table if the index duplicates 100% of the data)

• “小”“查找”类型表（例如，经常查询，不经常更新，适合相对较少的块）
• 任何您已经将拥有涵盖所有列的索引的表（即，如果索引复制 100% 的数据，也可以节省表使用的空间）

回答by Ian Carpenter

From the Oracle Conceptsguide:

来自 Oracle概念指南：

Index-organized tables are useful when related pieces of data must be stored together or data must be physically stored in a specific order. This type of table is often used for information retrieval, spatial (see "Overview of Oracle Spatial"), and OLAP applications (see "OLAP").

当相关数据必须存储在一起或数据必须以特定顺序物理存储时，索引组织表非常有用。这种类型的表通常用于信息检索、空间（请参阅“Oracle Spatial 概述”）和 OLAP 应用程序（请参阅“OLAP”）。

This questionfrom AskTom may also be of some interest especially where someone gives a scenario and then asks would an IOT perform better than an heap organised table, Tom's response is:

AskTom 的这个问题也可能有些有趣，尤其是当有人给出一个场景然后询问 IOT 的性能是否比堆组织的表更好时，Tom 的回答是：

we can hypothesize all day long, but until you measure it, you'll never know for sure.

我们可以整天假设，但除非你测量它，否则你永远不会确定。

回答by Adam Musch

An index-organized table is generally a good choice if you only access data from that table by the key, the whole key, and nothing but the key.

如果您仅通过键、整个键以及仅通过键访问该表中的数据，则索引组织表通常是一个不错的选择。

Further, there are many limitations about what other database features can and cannot be used with index-organized tables -- I recall that in at least one version one could not use logical standby databases with index-organized tables. An index-organized table is not a good choice if it prevents you from using other functionality.

此外，对于其他数据库功能可以和不可以与索引组织表一起使用有很多限制——我记得至少在一个版本中，不能将逻辑备用数据库与索引组织表一起使用。如果索引组织表阻止您使用其他功能，那么它不是一个好的选择。

回答by David Aldridge

All an IOT really saves is the logical read(s) on the table segment, and as you might have spent two or three or more on the IOT/index this is not always a great saving except for small data sets.

IOT 真正节省的只是表段上的逻辑读取，因为您可能在 IOT/索引上花费了两三个或更多，这并不总是一个很好的节省，除了小数据集。

Another feature to consider for speeding up lookups, particularly on larger tables, is a single table hash cluster. When correctly created they are more efficient for large data sets than an IOT because they require only one logical read to find the data, whereas an IOT is still an index that needs multiple logical i/o's to locate the leaf node.

另一个需要考虑加速查找的功能，特别是在较大的表上，是单表哈希集群。如果正确创建，它们对于大型数据集比 IOT 更有效，因为它们只需要一次逻辑读取即可找到数据，而 IOT 仍然是一个索引，需要多个逻辑 I/O 来定位叶节点。

回答by Will A

I can't per se comment on IOTs, however if I'm reading this right then they're the same as a 'clustered index' in SQL Server. Typically you should think about not using such an index if your primary key (or the value(s) you're indexing if it's not a primary key) are likely to be distributed fairly randomly - as these inserts can result in many page splits (expensive).

我本身无法对 IOT 发表评论，但是如果我没看错，那么它们与 SQL Server 中的“聚集索引”相同。通常，如果您的主键（或您索引的值，如果它不是主键）可能相当随机地分布，您应该考虑不使用这样的索引 - 因为这些插入会导致许多页面拆分（昂贵的）。

Indexes such as identity columns (sequences in Oracle?) and dates 'around the current date' tend to make for good candidates for such indexes.

诸如标识列（Oracle 中的序列？）和“当前日期附近”的日期等索引往往是此类索引的良好候选者。

回答by Bhartendu

An Index-Organized Table--in contrast to an ordinary table--has its own way of structuring, storing, and indexing data.

与普通表相比，索引组织表具有自己的结构化、存储和索引数据方式。

Index organized tables (IOT) are indexes which actually hold the data which is being indexed, unlike the indexes which are stored somewhere else and have links to actual data.

索引组织表 (IOT) 是实际保存正在被索引的数据的索引，与存储在其他地方并具有指向实际数据的链接的索引不同。