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

14 comments :

  1. The Article has been nicely written and hence is Easy enough to understand in the first glance..

    Thanks!!
    Tarun Thaper

    ReplyDelete
  2. Hello Friend
    i am new in DBA FIELD
    UREXAPLE WAS EXCELLENT

    ReplyDelete
  3. Nice explanation. Very easy to understand when i read first time.

    ReplyDelete
  4. I like the simple way of explanation. Thanks

    ReplyDelete
  5. agree , simplest explaination of IOT

    ReplyDelete
  6. very good document ,understood in first look,
    Please if possible can you provide the scenerios when to use IOT

    ReplyDelete
  7. This is a nice article. I believe the following information would benefit some.

    1. IOT - Faster Reads and Slower Writes (due to reorganization upon inserts)
    2. Comparable to Clustered Index in MSSQL.

    I request the author to comment on this.

    ReplyDelete
  8. But the index has to be the primary key, which is not the case with other Relational databases.

    ReplyDelete
  9. Thank you .

    article very helpful.

    ReplyDelete
  10. Nice explanation.

    Thank you.

    ReplyDelete
  11. very simple explanation, was able to understand in the first go, keep up the good work Anantha.

    ReplyDelete
  12. simple & crisp clear explaination

    ReplyDelete
  13. Thanks a lot for breaking it down into layman terms and the followup comments were helpful too.

    ReplyDelete
  14. Good Article on briefing IOT with example... can you please add disadvante also of using IOT.. tat will complete the description!! :)

    ReplyDelete