When to Use Index Organized table?

What is Index Organized table?

In Heap tables, oracle stores the data in data segment with ROWID. When we create the index, oracle stores the index key value and ROWID in the index segment. So the index key value and rowid are stored in both index & data segments. Whenever we search any data record, it first scan the index segment and find the rowid and match the rowid in data segment to get the particular record. So the drawback here is, rowid & index key value is stored in two places. The space consumption is one issue. Another issue is, oracle has to scan both data and index segment to obtain the record.

To overcome this problem, oracle introduced IOT in version 8. In IOT, the data is stored in the primary key index itself with the rows placed in a key-sequenced order, thus eliminating the necessity of duplicate data being stored in index and table. When we search the record, it does scan only on index segment.

IOT do not consider ROWID. IOT stores the data in B-tree index and sorts the data on primary key whenever we insert, update the record.


Example to create IOT

SQL> CREATE TABLE iot_emp
2 (empno number PRIMARY KEY,
3 empname varchar2(10),
4 salary number)
5 ORGANIZATION INDEX
6 TABLESPACE RPTS_STAGE_DATA
7 PCTTHRESHOLD 20
8 INCLUDING empname
9 OVERFLOW TABLESPACE ods_stage_data
10 /

Table created.

SQL>

ORGANIZATION INDEX denotes that the table is index organized table.

PCTTHRESHOLD Specifies the percentage of space reserved in the index block for an index-organized table row. Any portion of the row that exceeds the specified threshold is stored in the overflow segment. PCTTHRESHOLD must be a value from 1 to 50. PCTTHRESHOLD must be large enough to hold the primary key.

OVERFLOW - Index-organized table data rows exceeding the specified threshold will be placed in overflow segment.

INCLUDING - which can be used to specify nonkey columns that are to be stored in the overflow data segment.

When to use IOT?

Large tables where primary key exact fetch is time critical and there is a low volume of DML operations. In most cases, in the category of exact fetch via primary key, IOT organization is much faster than HEAP. This advantage varies between 15% and 400%, depending upon the row length, number of columns and the use of the PCTTHRESHOLD option.

Heavy volume of DML operations occurring in IOT, usually fragments the table, requiring frequent table reorganization. An index-organized table can be reorganized without invalidating its secondary indexes, and can be performed online thus reducing or even eliminating downtime.

Index-organized tables support parallel features for loading, index creation, and scans required for handling large volumes of data. Partitioned index-organized tables are also supported, so that each partition can be loaded concurrently. Data warehousing applications using star schemas can also gain performance and scalability by implementing fact tables as index-organized tables for efficient execution of star queries. All these features make index-organized tables suitable for handling large scale data.

Advantage of IOT?

1. Scanning both data and index segment is eliminated.
2. Storage requirement is reduced as data is stored only in primary key index segment.