IOT – Index Organized Table

Oracle supports these 3 different types of tables,

1.     Heap Table (nothing but Data Table)

2.     IOT

3.     Cluster Table (using Cluster Index)

First type, heap table is everyone aware of. Heap Table is of rows and columns and value is stored in each cells (each cell è intersection of each rows and columns). In Oracle, all these tables containing data are stored in the datafiles which would be in HardDisk. In datafile, fundamental storage unit is block (in other databases, this might be “Pages”). Logical representation of the datafile, the block, the data will be like this,

“ROWID” is the memory address assigned to the each record getting stored in a block of a datafile. ROWID can be decoded to get the actual block number and the datafile name on which the particular record is stored. .

If Suppose say, datafile is of 3MB. If size of single block is say 1MB, then 3 blocks would be in this single data file. Consider, I have to store 6 records in the EMP table and to store each record, I require 0.5MB. For this scenario, the above diagram gives the clear picture of how data is stored. Each block is interlinked by a structure called “Double Linked List”.

For eg, if user searches for the 4^th emp record (WHERE empid = 4), Oracle reads the block which contains the particular record (in this case, “Block2”) and put it into RAM. From RAM, the particular record (empid=4) from that block is retrieved and displayed to the user.

IOT is the another type of table which is supported by Oracle. IOT means storing the data in the index table itself. It means, when IOT is created, only index table stores the data but not the data table. With this specific structure, Oracle can make retrieval of data as a single step process. Till now, whenever any index is created for a data table, during the retrieval of data, first it hits the index table to get the corresponding ROWID and then it hits data table to retrieve all the attributes of the corresponding record. In IOT, since data itself is going to be stored in the index table, it become one step process by completely avoiding the data table hit. Here, data table is an empty structure alone and only index table stores the data.

HOW TO VERIFY:

How to verify whether the created table is IOT or not? Fire this query,

select iot_type from user_tables where table_name = 'EMPLOYEE'

ð  This query would return “IOT” (it means it is a table but of “IOT” type)

LITTLE-KNOWN FACTS TO BE REMEMBERED:

·         Size of IOT data table will be 0 because data is stored in the index table alone.

·         If IOT table is referred in a query, then in the explain plan, you never see the step, ‘TABLE ACCESS BY ROWID” because oracle never hits the data table for IOT since data is retrieved only from the index table.

·         An IOT table can’t be created without specifying the primary key.

ADVANTAGE:

·         Retrival of data from IOT will be much faster since the data itself is stored in the sorted order.

·         Memory space to store IOT is less because data table is not going to store any data and it just an empty structure alone.

DISADVANTAGE:

·         DML activity will consume a lot of time if data is going to come to the database in the irregular order. Since IOT stores the data in the sorted order of index value in the index table, index record has to be frequently flipped if data is not coming in the actual sorted order.

EXAMPLE:

This is the syntax to create IOT,

create table emp
(empid number,
 empname varchar2(100),
 salary number,
 deptid number,
 constraint pk_employee primary key (empid))
organization index;

The keyword, “organization index” tells oracle that the table which is going to be created is of IOT type. After this is created, inserts 10 records in an unsorted order(means insert the values for the employees in this order 4,5,2,3,8,9,10,1,6,7).

The data table will look like this,

DATA TABLE:

Empid	Empname	Salary	Depid

Since the data table doesn’t store the data, it is just an empty structure alone. Index table will logically look like this,

IOT INDEX TABLE:

Index Value	Index Pointer
	ROWID	empname	salary	deptid
1	AAAAA1	a	100	ECE
2	AAAAA2	b	100	ECE
3	AAAAA3	c	100	ECE
4	AAAAA4	d	100	ECE
5	AAAAA5	e	100	ECE
6	AAAAA6	f	100	IT
7	AAAAA7	g	100	IT
8	AAAAA8	h	100	IT
9	AAAAA9	i	100	IT
10	AAAAA10	j	100	IT

First column : it stores all the values of empid column in the ascending order.

Second column : it stores the entire content along with the ROWID of the corresponding records.

After creating this IOT, Fire this query against this table where the requirement is to display the all the attributes of an employee whose empid is “6”

Select * from emp where empid = 6;

When oracle executes this sql, first it looks for any index which has already been created on this “empid” column. It comes to know that the index, “pk_employee” has already been created. So, oracle refers the index table first. Since we are looking for the empid: ”6”, oracle goes to the corresponding index record (6ht index record). Since this is IOT, oracle never hits data table but fetches all the required columns from the index table itself. When oracle searches for the value in this index table, it follows random search (i.e. binary search) to go to the corresponding index record. Using this binary search, Oracle is intelligent enough to go to the 6^th index record directly in the index table. So, it won’t touch the remaining 9 index records.

Now, explain table will look like this,

--------------------------------------------------------------------------------------------------

| Id  | Operation                               | Name                  |    Rows |

--------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |                            |     1       |

|   1 |   INDEX (UNIQUE SCAN)        | PK_EMPLOYEE  |     1       |

If we closely look into this explain plan, we can understand that oracle never refers the data table (data table name is ‘EMP”) and it refers only the index table (index table name is “PK_EMPLOYEE” ) alone. Since IOT is referred in the query, “TABLE ACCESS BY ROWID” step is completed avoided and all the attributes are retrieved from the index table itself. To execute this query, oracle will take only 1 second (this time is consumed to go the respective index record (6^th index record) and fetch all the attributes from there itself).