What is an Index Organized Table (IOT) and when should an IOT be used?

An IOT keeps its data stored according to the primary key column values for the table. IOTs store the data as if the entire table was stored in an index.A normal index only stores the indexed columns in the index; an IOT stores all of the table's columns in the index. IOTs are new in Oracle8.As the table's data is stored as an index, the rows of the table do not have physical RowIDs. Therefore, you cannot select the RowID pseudocolumn values from an IOT.

Oracle8i introduces a new feature for IOTs called logical RowIDs. Logical RowIDs allow the construction of secondary indexes and improve index performance allowing the IOT performance to match the performance of regular indexes.

Prior to Oracle8i you could not create additional indexes on an IOT. The only valid index being the primary key index. As of Oracle 8i you can create secondary indexes on IOTs. An IOT will require less space than a regular table since there are no RowIDs.

Suppose you create the following IOT:

CREATE TABLE iottest
(areacode CHAR(3) primary key,
city VARCHAR2 (50))
ORGANIZATION INDEX TABLESPACE users
PCTTHRESHOLD 10
OVERFLOW TABLESPACE users;

An IOT is appropriate if you will always be accessing the areacode information by the areacode column in the where clause of your queries. To limit the amount of internal management with the IOT you should only create an IOT if the data is very static. If the data in your table changes frequently you should use a regular table with indexes. In general, IOTs are most efffective when the primary key constitutes a large part of the tables columns.If the IOT contains many frequently accessed columns that are not part of the primary key, then the IOT will need to repeatedly access it's overflow area.

Use of the pctthreshold clause is optional. When specified, pctthreshold indicates a threshold limit that any row of data can occupy in an index-organized table block.In the example, the value set for this clause is 10, which means that any row in the table that takes up 10 percent or more of its data block for storage will have nonkey column data moved into an overflow tablespace defined using the overflow tablespace keywords. If overflow tablespace is specified without defining a pctthreshold, the default pctthreshold of 50 will be used.A row bigger than pctthreshold in an IOT might destroy the dense storage of the rows in the index. In cases where this condition might occur, an overflow tablespace clause should be used.

To retrieve Oracle8
IOT information from the Data Dictionary issue the following SQL:
-----------------------------------------------------------------
SELECT t.table_name AS ?IOT?, o.table_name AS ?Overflow?,
i.index_name AS ?Index?, o.tablespace_name AS ?Overflow TS?,
i.tablespace_name AS ?Index TX?, i.pct_threshold
FROM dba_tables t, dba_tables o, dba_indexes i
WHERE t.owner = o.owner
AND t.table_name = o.iot_name
AND t.owner = i.owner
AND t.table_name = i.table_name AND t.owner = ?SCOTT?;

To retrieve post Oracle8

IOT information from the Data Dictionary issue the following SQL:
-----------------------------------------------------------------

SQL> SELECT table_name, tablespace_name, iot_name, iot_type
FROM dba_tables
where table_name = 'IOTTEST'

TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
IOT_NAME IOT_TYPE
------------------------------ ------------
IOTTEST
IOT
SQL> SELECT object_name, object_id
FROM dba_objects
WHERE object_name = 'IOTTEST';

OBJECT_NAME OBJECT_ID
------------------------------------- ---------
IOTTEST 74858

SQL> SELECT index_name, index_type, tablespace_name, table_name
FROM dba_indexes
WHERE table_name = 'IOTTEST';

INDEX_NAME INDEX_TYPE
------------------------------ ---------------------------
TABLESPACE_NAME TABLE_NAME
------------------------------ ------------------------------
SYS_IOT_TOP_74858 IOT - TOP
USERS IOTTEST


Now using the object_id obtained from dba_objects

SQL> SELECT segment_name, tablespace_name, segment_type
FROM dba_segments
WHERE segment_name like '%74858%';

SEGMENT_NAME
--------------------------------------------------------------
TABLESPACE_NAME SEGMENT_TYPE
------------------------------ ------------------
SYS_IOT_OVER_74858
USERS TABLE

SYS_IOT_TOP_74858 USERS INDEX

IOT Restrictions:
-----------------
1. A primary key must be specified otherwise ORA-25175 is returned.
2. If pctthreshold is defined and an overflow segment is not defined,
rows exceeding the threshold are rejected with an ORA-1429 error.
3. Cannot create an IOT of object types.
4. An IOT can contain columns of LOB and nested table types, but only
if the table is not partitioned.
5. IOTs must be reorganized using the move clause of the alter table
command.