Index Organized Tables (IOT)

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:

Why Use Index Organized Tables
Creation Of Index Organized Tables
Maintenance Of Index Organized Tables

Why Use Index Organized Tables

Accessing data via the primary key is quicker as the key and the data reside in the same structure. There is no need to read an index then read the table data in a separate structure.
Lack of duplication of the key columns in an index and table mean the total storage requirements are reduced.

Creation Of Index Organized Tables

To create an index organized table you must:

Specify the primary key using a column or table constraint.
Use the ORGANIZATION INDEX.

In addition you can:

Use PCTTHRESHOLD to define the percentage of the block that is reserved for an IOT row. If the row exceeds this size the key columns (head piece) is stored as normal, but the non-key data (tail peice) is stored in an overflow table. A pointer is stored to locate the tail peice.
Use OVERFLOW TABLESPACE to define the tablespace that the overflow data will be stored in.
Use UNCLUDING to define which non-key columns are stored with the key columns in the head peice, should overflow be neccessary.

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;

Hope this helps. Regards Tim...

Back to the Top.