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.
索引有组织的表(IOTs)是存储在索引结构中的表。虽然存储在堆中的表是无组织的，但IOT中的数据是按主键存储和排序的(数据是索引)。IOTs就像“常规”表一样，使用相同的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 not the best choice?
我猜答案是否定的，那么什么时候索引表不是最好的选择呢?
8 个解决方案
#1
19
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.
基本上，有索引的表是没有表的索引。如果你有一个表，它的列由主键组成，并且在大多数其他的列中，那么你就有了一个可能的索引。
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.
但是，如果您发现自己考虑了在非主键列上附加索引的需求，那么您最好使用常规的堆表。因此，由于大多数表可能需要额外的索引，所以大多数表不适合IOTs。
In practice, index organized tables are most likely to be reference data, code look-up affairs. Application tables are almost always just heap organized.
在实践中，索引组织的表最有可能是引用数据、代码查询事务。应用程序表几乎总是只组织成堆。
#2
13
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的讨论，小表格可以成为IOTs的好候选人。
#3
9
I consider the following kinds of tables excellent candidates for IOTs:
我认为以下几类表格是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%的数据，那么可以保存表所使用的空间)
#4
8
From the Oracle Concepts guide:
来自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空间的概述”)和OLAP应用程序(参见“OLAP”)。
This question from 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的问题可能也会引起一些兴趣，特别是当某人给出一个场景，然后问他是否会比一张有组织的桌子表现得更好时，Tom的回答是:
we can hypothesize all day long, but until you measure it, you'll never know for sure.
我们可以假设一整天，但在你测量它之前，你永远无法确定。
#5
1
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.
此外，对于其他数据库特性，还存在许多限制，不能与索引组织的表一起使用——我记得，至少有一个版本不能使用具有索引的表的逻辑备用数据库。如果它阻止您使用其他功能，那么一个索引组织的表不是一个好的选择。
#6
0
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/索引上花费了2到3个或更多的时间，这并不总是一个大的节省，除了小的数据集。
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来定位叶子节点的索引。
#7
-2
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).
我不能对IOTs进行评论，但是如果我读的是这个，那么它们和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中的序列)和日期“around the current date”之类的索引往往会为这些索引提供合适的候选索引。
#8
-2
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)是实际保存被索引的数据的索引，不同于存储在其他地方的索引，并与实际数据有链接。
#1
19
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.
基本上，有索引的表是没有表的索引。如果你有一个表，它的列由主键组成，并且在大多数其他的列中，那么你就有了一个可能的索引。
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.
但是，如果您发现自己考虑了在非主键列上附加索引的需求，那么您最好使用常规的堆表。因此，由于大多数表可能需要额外的索引，所以大多数表不适合IOTs。
In practice, index organized tables are most likely to be reference data, code look-up affairs. Application tables are almost always just heap organized.
在实践中，索引组织的表最有可能是引用数据、代码查询事务。应用程序表几乎总是只组织成堆。
#2
13
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的讨论，小表格可以成为IOTs的好候选人。
#3
9
I consider the following kinds of tables excellent candidates for IOTs:
我认为以下几类表格是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%的数据，那么可以保存表所使用的空间)
#4
8
From the Oracle Concepts guide:
来自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空间的概述”)和OLAP应用程序(参见“OLAP”)。
This question from 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的问题可能也会引起一些兴趣，特别是当某人给出一个场景，然后问他是否会比一张有组织的桌子表现得更好时，Tom的回答是:
we can hypothesize all day long, but until you measure it, you'll never know for sure.
我们可以假设一整天，但在你测量它之前，你永远无法确定。
#5
1
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.
此外，对于其他数据库特性，还存在许多限制，不能与索引组织的表一起使用——我记得，至少有一个版本不能使用具有索引的表的逻辑备用数据库。如果它阻止您使用其他功能，那么一个索引组织的表不是一个好的选择。
#6
0
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/索引上花费了2到3个或更多的时间，这并不总是一个大的节省，除了小的数据集。
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来定位叶子节点的索引。
#7
-2
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).
我不能对IOTs进行评论，但是如果我读的是这个，那么它们和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中的序列)和日期“around the current date”之类的索引往往会为这些索引提供合适的候选索引。
#8
-2
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)是实际保存被索引的数据的索引，不同于存储在其他地方的索引，并与实际数据有链接。