Indexed Organized Table (IOT)

Index data is stored separately from actual table data on a Heap table which Tom calls it heap fashion. Data are stored where next free blocks are available. Whenever a data is selected, DBMS scans Index, gets the rowid of a searched record and use the rowid to fetch the data from the table and display data back to requester. Imagine a scenario where both the Index and data are stored together which is called Index Organized Table. Index Organized Table, commonly known as IOT which is a type of table that stores data in B tree Index structure. It has primary key and non primary key column data stored within the same B*tree structure. 

Why are the benefits of using IOT? 

1. Fast random access on PK because an index-only scan is sufficient.

2. Fast range access on the primary key because the rows are cluster in primary key order.

3. lower storage requirement because the duplication  of primary keys are avoided.

Syntax 1: 

CREATE TABLE hr.employee (
    enpno NUMBER PRIMARY KEY,
    ename VARCHAR2(30),
    mgr   VARCHAR2(30),
    sal   NUMBER,
    phone NUMBER(13))
  ORGANIZATION INDEX; 

Syntax:2

 
CREATE TABLE hr.employee(
    enpno NUMBER PRIMARY KEY,
    ename VARCHAR2(30),
    mgr   VARCHAR2(30),
    sal   NUMBER,
    phone NUMBER(13))
  SEGMENT CREATION IMMEDIATE ORGANIZATION INDEX; 

IOT supports full functionality like regular/heap tables. It supports constraints, trigger, partition, parallel and more. Index Organized Table are perfect for OLTP applications that requires fast PK access and high availability. Index-organized tables are suitable for modeling application-specific Index structures. For example, content-based information retrieval applications containing text, image and audio data require inverted indexes that can be effectively modeled using index-organized tables. I mostly used for lookup tables and a temp table to break complex query into simple for performance and readability

Why are they not popular? what are their restrictions?
1.  Must have Primary Key column
2.  Row are accessed using rowid
3.  Cannot have a cluster.
4.  Does not support LONG data type.
5.  You can't alter Index instead use ALTER TABLE.

Insert into IOT table takes a while as it has to store data into a right B-tree structures. Table that requires frequent update or insert is a bad candidate for IOT. IOT is best for query data using PK on WHERE clause. If you are querying other then it may slow down your performance. Always test, test and test the performance before you decide to stick with Index Organized Table.