Index-Organized Tables – Oracle

Data is stored in B-Tree structure in Index-Organized tables(IOT). IOTs are unlike indexed tables in which index information is stored at a different place. Index is an object inside oracle database which stores index values corresponding to rowids for faster row access.

Suppose we have a table EMPLOYEE with some columns, and column emp_id is its primary key. As emp_id is the primary key, oracle would have automatically created index on emp_id. Now, if we fire a select query as

SELECT * FROM employee WHERE emp_id=12345;

Oracle will first consult index on this column to get rowid of row in the table. Once it gets the rowid, it accesses the row.

So, we can say that two i/o calls took place

1) to access the index object to get the rowid.

2) to access the table using the rowid.

This was about how indexed tables work.

Let's see what's different in IOTs

As told in the beginning, IOTs store data in B-Tree data structure. Data in these tables are sorted on primary key and stored in B-Tree. Not only the primary key, but other columns of the row are also stored within index leaf block of the B-Tree.

IOTs are more efficient than indexed tables as only one i/o call is needed to fetch data directly from B-Tree, and storage space is also reduced, as no separate index object is made.

IOT can be created by specifying ORGANIZATION INDEX qualifier while creating table. Also, it is necessary to create primary key, as data in IOT is sorted on primary key.

CREATE TABLE employee
(
emp_id NUMBER(10) PRIMARY KEY,
emp_name VARCHAR2(20),
phone_no NUMBER(10)
)
ORGANIZATION INDEX;

Some optional information can also be provided while creating IOT.

CREATE TABLE employee
(
emp_id NUMBER(10) PRIMARY KEY,
emp_name VARCHAR2(20),
phone_no NUMBER(10)
)
ORGANIZATION INDEX TABLESPACE tbs_01
PCTTHRESHOLD 30
INCLUDING emp_name
OVERFLOW TABLESPACE tbs_02;

Index blocks of IOT will be stored in tablespace tbs_01 in B-Tree structure.

Amount of data stored in index leaf blocks depends on PCTTHRESHOLD value. According to above value, only 30 % space of the block can be used to store row data, rest of the data will be stored in OVERFLOW TABLESPACE tbs_02. Row will be split at 30 % threshold with first part stored in index leaf block in tbs01 and rest of the part stored in OVERFLOW TABLESPACE tbs02. This 30% data may contain key as well as non key columns. Storage of non key columns depends on PCTTHRESHOLD value. INCLUDING option specify the non key columns which should be given preference to be stored in the index leaf block.

For more info visit