Index Organized Tables - Introduction

Index Organized Tables or IOT where introduced in Oracle with the arrival of Oracle 8. These tables where introduced primarily for Internet applications that involve data access based on single column primary keys.

Storage organization has been always a key factor in faster access of data. With normal tables there are no indexes created at first. First you have to create a table, then create indexes for faster performance in data access.

Indexes have some drawbacks such as it stores data in two places, one in table and in index. If a query is issued (it is assumed that it uses the index), it checks the index and retrieves the address of data in table. Then the data is fetched from the tables to produce the query output.

But in case of an Index Organized Table, data is stored in the index itself with the rows placed in a key-sequenced order, thus eliminating the necessity of duplicate data being stored in index and table.

An Index Organized Table is created using the keyword ORGANIZATION INDEX at the end of the CREATE TABLE script.

Refer the following example for creation of a IOT:
CREATE TABLE temp1 (
slno NUMBER (3) NOT NULL,
CONSTRAINT PK_temp1
PRIMARY KEY ( slno))
ORGANIZATION INDEX;

Now we will check in what order the data is actually stored by inserting some rows into the table temp1.

insert into temp1 values(6);

insert into temp1 values(1);

insert into temp1 values(5);

insert into temp1 values(4);

insert into temp1 values(3);

insert into temp1 values(2);

insert into temp1 values(9);

insert into temp1 values(7);

insert into temp1 values(8);

insert into temp1 values(10);

Now after inserting these rows by issuing a select statement without any order clause will retrieve the rows in stored order. Let's check this by issuing the following statement:

select * from temp1;

The output is:
SLNO
1
2
3
4
5
6
7
8
9
10

Now from this exercise we are clear as to how Oracle stores the data in an Index Organized Table for a normal table the rows would have been selected in the inserted order.

What is IOT?
So what is an IOT? An IOT has entirely different logic of storage and indexing. In normal tables as soon as we create a row it is associated with a ROWID. This ROWID is permenant as long as the data is there. When an index is created, it stores the column data as well as the ROWID of the table data as it provides its physical location.

IOTs do not consider ROWID. This is because the data is actually stored in a B-Tree index that sorts the data with the leaves in the order of the primary key's data. As and when INSERTs or UPDATEs are fired against this IOT, the rows are re-arranged to store the data in sorted order of the primary key.

The access to the data is fast because as soon as the values are found in the B-Tree index, Oracle is ready to pump the output directly as it is not tied up with ROWIDs. Hence there are two benefits of using IOT:
1. Lookup to table from index is eliminated
2. Storage requirements are reduced as data is stored only in one place.

Courtesy: Oracle9i Index-Organized Tables Technical Whitepaper - Oracle Corporation