Index-organized table

An index-organized table (IOT) is a type of table that stores data in a B*Tree index structure.

Normal relational tables, called heap-organized tables, store rows in any order (unsorted). In contrast to this, index-organized tables store rows in a B-tree index structure that is logically sorted in primary key order. Unlike normal primary key indexes, which store only the columns included in its definition, IOT indexes store all the columns of the table (see below for an exception to this rule - called the overflow area).

If some columns of the table are infrequently accessed, it is possible to offload them into another segment named the overflow area. An overflow segment will decrease the size of the main (or top) segment and will increase the performance of statements that do not need access the columns in the overflow area. The overflow segments can reside in a tablespace different from the main segments.

Notes:

The overflow area can contains only columns that are not part of the primary key.

If a row cannot fit in a block, you must define an overflow area.

Consequently, the primary key values of an IOT must fit in a single block.

The columns of the table that are recorded in the overflow segment are defined using the PCTHRESHOLD and/or INCLUDING options of the OVERFLOW clause (see example below).

The following example creates an IOT with an overflow area and shows the objects and segments that are created.
(This example was tested in versions 9.2 to 11.2, see the note in the previous section about 11g.)

All the columns up to and including the one named in the INCLUDING option of the OVERFLOW clause are in the top segment; the remaining ones are in the overflow segment.

Here, we see that 2 table objects are created, the main one is of iot_type IOT and the overflow one is of iot_type IOT_OVERFLOW.
The name of the overflow table is SYS_IOT_OVER_<table object number>.
You can see now the purpose of the IOT_NAME column, it gives the name of the IOT table for its overflow one.

You can also see that 2 segments are created: the index of the IOT and the overflow area.

In the end, the USER_INDEXES view gives you the last column included in the index in its INCLUDE_COLUMN column.

Remember that the INCLUDE_COLUMN of the USER_INDEXES view gives you the last column included in the index and so in the top segment, so joining this view with the USER_TAB_COLUMNS you can separate the columns that are in the top segment from those that are in the overflow one as with the following statement: