Oracle 8i | Oracle 9i | Oracle 10g | Oracle 11g | Oracle 12c | Miscellaneous | PL/SQL | SQL | Oracle RAC | Oracle Apps | Linux

Index Organized Tables (IOT) in Oracle

Index Organized Tables (IOT) have their primary key data and non-key column data stored within the same B*Tree structure. Effectively, the data is stored within the primary key index. There are several reasons to use this type of table.

Related articles.

Why Use Index Organized Tables

Creation Of Index Organized Tables

To create an index organized table you must:

In addition you can:

CREATE TABLE locations
(id           NUMBER(10)    NOT NULL,
 description  VARCHAR2(50)  NOT NULL,
 map          BLOB,
 CONSTRAINT pk_locations PRIMARY KEY (id)
)
ORGANIZATION INDEX 
TABLESPACE iot_tablespace
PCTTHRESHOLD 20
INCLUDING description
OVERFLOW TABLESPACE overflow_tablespace;

Maintenance Of Index Organized Tables

As with B*Tree indexes, IOTs can become fragmented and may need to be rebuilt. If the IOT has no overflow it can be rebuilt offline or online.

ALTER TABLE table_name MOVE INITRANS 10;
ALTER TABLE table_name MOVE ONLINE INITRANS 10;

If the IOT does have overflow it can only be rebuilt offline.

ALTER TABLE table_name MOVE TABLESPACE iot_tablespace OVERFLOW TABLESPACE overflow_tablespace;

For more information see:

Hope this helps. Regards Tim...

Back to the Top.