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?

Answer 1

Except for a few corner cases, generally all tables should be indexed - primary key at the very minimum. Oracle automatically creates an index on a primary key if one doesn't already exist on the table.

Coming back to the exceptions.... Indexes are for making data retreival faster, but slow down inserts/updates/deletes - which isn't a good idea on tables involved with heavy insertion and light on data retreival, like a log/audit table.

Know your data and how it gets in and is used. This information will help you determine the best strategy for table setup.

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

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

Answer 3

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.

Answer 4

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.

Answer 5

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.

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

Answer 6

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

Answer 7

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)