什么时候应该使用Oracle的索引组织表?或者,什么时候不应该?[英] When should I use Oracle's Index Organized Table? Or, when shouldn't I?

本文是小编为大家收集整理的关于什么时候应该使用Oracle的索引组织表?或者,什么时候不应该?的处理/解决方法,可以参考本文帮助大家快速定位并解决问题,中文翻译不准确的可切换到English标签页查看源文。

问题描述

索引有组织的表(IOT)是存储在索引结构中的表.而存储的桌子 在堆中是无组织的,物联网中的数据由主键存储和排序(数据是索引).物联网的行为就像"常规"表,您使用相同的SQL访问它们.

适当的关系数据库中的每个表都应该具有一个主键...如果我的数据库中的每个表都有主键,我应该始终使用索引有组织的表?

我猜答案是否定的,所以索引有组织的表不是最佳选择?

推荐答案

基本上是一个索引组织的表是没有表的索引.我们可以在User_tables中找到一个表对象,但它只是对基础索引的引用.索引结构与表的投影匹配.因此,如果您的列由主键组成,最多是另一列的表

索引有组织的表的主要用例是一个表,几乎总是由其主键访问,我们总是想检索其所有列.实际上,指数有组织的表最有可能是参考数据,代码查找事务.申请表几乎总是组织起来的.

语法允许IoT具有多个非键列.有时这是正确的.但这也表明我们可能需要重新考虑我们的设计决策.当然,如果我们发现自己正在考虑在非主要钥匙列上需要其他索引,那么我们可能会使用常规的堆台上的情况.因此,由于大多数表可能需要其他索引,大多数表不适合物联网.


回到这个答案,我看到该线程中的其他几个响应提出了相交表作为物联网的合适候选者.这似乎是合理的,因为相交表具有与候选密钥相匹配的投影是很常见的:susident_classes可以具有Just Just(Student_id,class_id)的投影.

我不认为这是铸铁.交叉表通常具有技术密钥(即Student_Class_ID).它们也可能具有非钥匙列(元数据列,例如start_date,end_date是常见的).另外,也没有盛行的访问路径 - 我们希望找到所有经常参加课程的学生,我们想找到一个学生正在参加的所有课程 - 因此,我们需要一种索引策略,这两个策略都同样很好地支持.并不是说交叉表不是物联网的用例.只是它们不是自动的.

其他推荐答案

我会考虑它们的狭窄表(例如用于解决多个桌子的联接表).如果(实际上)表中的所有列都将在索引中处于索引中,那么为什么不应该使用IoT.

小桌子可以是理查德·富特(Richard Foote)在这里

其他推荐答案

我考虑以下类型的表格候选人:

  • "小""查找"键入表(例如,经常查询,很少更新,适合相对较少的块)
  • 您已经要拥有的任何表都覆盖所有列的索引(即,如果索引重复了100%的数据,则可以节省表所使用的空间)
  • )

本文地址:https://www.itbaoku.cn/post/597632.html

问题描述

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.

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?

推荐答案

Basically an index-organized table is an index without a table. There is a table object which we can find in USER_TABLES but it is just a reference to the underlying index. The index structure matches the table's projection. 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.

The main use case for index organized table is a table which is almost always accessed by its primary key and we always want to retrieve all its columns. In practice, index organized tables are most likely to be reference data, code look-up affairs. Application tables are almost always heap organized.

The syntax allows an IOT to have more than one non-key column. Sometimes this is correct. But it is also an indication that maybe we need to reconsider our design decisions. Certainly if we find ourselves contemplating the need for additional indexes on the non-primary key columns then we're probably better off with a regular heap table. So, as most tables probably need additional indexes most tables are not suitable for IOTs.


Coming back to this answer I see a couple of other responses in this thread propose intersection tables as suitable candidates for IOTs. This seems reasonable, because it is common for intersection tables to have a projection which matches the candidate key: STUDENTS_CLASSES could have a projection of just (STUDENT_ID, CLASS_ID).

I don't think this is cast-iron. Intersection tables often have a technical key (i.e. STUDENT_CLASS_ID). They may also have non-key columns (metadata columns like START_DATE, END_DATE are common). Also there is no prevailing access path - we want to find all the students who take a class as often as we want to find all the classes a student is taking - so we need an indexing strategy which supports both equally well. Not saying intersection tables are not a use case for IOTs. just that they are not automatically so.

其他推荐答案

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.

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

其他推荐答案

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)