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

Question

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?

Solution 1

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.

Solution 2

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

Solution 3

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)

Solution 4

From the Oracle Concepts guide:

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").

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:

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

Solution 5

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.

View more solutions

Comments

[OMG Ponies]

You're correct - in Oracle, indexes are just called indexes, there's no distinction like you see in MySQL & SQL Server. An unindexed table in Oracle is heap sorted. Sequences are closer to SQL Server's IDENTITY column, because the increment and offset values in sequences are independent of one another - unlike MySQL. But sequences also aren't attached to any one table - they're stand alone objects. Which also means you can have more than one sequence used in a table (though not common).

[Jeffrey Kemp]

Sorry, this is pretty much a duplicate of Gary's answer.

[David Aldridge]

I've always felt that the "stored together" thing was a red herring, as the primary key for the table is probably non-meaningful. You might want to cluster records that were inserted at the same time, but co-location of rows is usually a matter of multiple rows having the same (eg.) customer_id or invoice_id, in which case a hash cluster is a better choice.

[APC]

This is an explanation of the IOT concept but doesn't actually address the question by explaining when to use one rather than a standard table.