Index Organized Tables ( IOT ) have their primary key data and non-key column data stored within the same B*Tree structure. Effectively, the data is stored within the primary key index. There are several reasons to use this type of table.
In other words, An index-organized table ( IOT ) is a type of table that stores data in a B*Tree index structure.
• Normal relational tables, called heap-organized tables, store rows in any order (unsorted) or (Insert Where It Can Fit). In contrast to this, index-organized tables store rows in a B-tree index structure that is logically sorted in primary key order.
• Unlike normal indexes, which store only the columns included in its definition, IOT indexes store all the columns of the table (exception to this rule – called the OverFlow Area).
• In Relational Table,both TABLE SEGMENT & B-TREE INDEX SEGMENT will be created (2 I/O Operation).In IOT only B-TREE INDEX SEGMENT will be created (1 I/O Operation).
• In Normal relational table,the indexed columns is maintained in two different places.In IOT,the indexed columns are maintained in one place only.
Why Use Index Organized Tables?
• Accessing data via the primary key is quicker as the key and the data reside in the same structure. There is no need to read an index then read the table data in a separate structure.
• Lack of duplication of the key columns in an index and table mean the total storage requirements are reduced.
Properties Of Index Organized Tables
• An IOT must contain a primary key.
• Rows are accessed via a logical ROWID and not a physical ROWID like in heap-organized tables so the IOT will not become unusable if it is reorganized.
• An IOT cannot be in a cluster.
• An IOT cannot contain a column of LONG data type.
• You cannot modify an IOT index property using ALTER INDEX (error ORA-25176), you must use an ALTER TABLE instead.
• As the index and the table are in the same segment, less storage space is needed.
• As an IOT has the structure of an index and is thus sorted in the order of the primary key, accesses of a range of primary key values are also faster.
OverFlow Area
If some columns of the table are infrequently accessed, it is possible to offload them into another segment named the overflow area. An overflow segment will decrease the size of the main (or top) segment and will increase the performance.The overflow segments can reside in a tablespace different from the main segments.
Notes :
• The overflow area can contains only columns that are not part of the primary key.
• If a row cannot fit in a block, you must define an overflow area.
• Consequently, the primary key values of an IOT must fit in a single block.
The columns of the table that are recorded in the overflow segment are defined using the PCTHRESHOLD and/or INCLUDING options of the OVERFLOW clause.
SQL> CREATE TABLE regular
2 (
3 id INT PRIMARY KEY,
4 name VARCHAR2(20)
5 );
Table created.
SQL> INSERT INTO regular VALUES(1,'TEST1');
1 row created.
SQL> INSERT INTO regular VALUES(3,'TEST3');
1 row created.
SQL> INSERT INTO regular VALUES(2,'TEST2');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM regular;
ID NAME
---------- --------------------
1 TEST1
3 TEST3
2 TEST2
--Here the data is stored in relational table in unsorted order,but the index will be kept in sorted order.
SQL> SELECT table_name,iot_type,iot_type FROM user_tables WHERE upper(table_name) = 'REGULAR';
TABLE_NAME IOT_TYPE IOT_TYPE
------------------------------ ------------ ------------
REGULAR
SQL> SELECT segment_name,segment_type,tablespace_name FROM user_segments WHERE upper(segment_name) = 'REGULAR';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------ ---------------
REGULAR TABLE SYSTEM
SQL> SELECT index_name,index_type,table_name FROM user_indexes WHERE upper(table_name) = 'REGULAR';
INDEX_NAME INDEX_TYPE TABLE_NAME
------------------------------ --------------------------- ------------------------------
SYS_C0016222 NORMAL REGULAR
SQL> SELECT segment_name,segment_type,tablespace_name FROM user_segments WHERE upper(segment_name) = 'SYS_C0016222';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------ ----------------
SYS_C0016222 INDEX SYSTEM
SQL> SET AUTOTRACE ON EXPLAIN
--2 I/O
SQL> SELECT * FROM regular WHERE id = 1;
ID NAME
---------- --------------------
1 TEST1
Execution Plan
----------------------------------------------------------
Plan hash value: 2736244477
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| REGULAR | 1 | 25 | 1 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SYS_C0016222 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=1)
SQL> CREATE TABLE iot
2 (
3 id INT PRIMARY KEY,
4 name VARCHAR2(20)
5 )
6 ORGANIZATION INDEX;
Table created.
SQL> INSERT INTO iot VALUES(1,'TEST1');
1 row created.
SQL> INSERT INTO iot VALUES(3,'TEST3');
1 row created.
SQL> INSERT INTO iot VALUES(2,'TEST2');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM iot;
ID NAME
---------- --------------------
1 TEST1
2 TEST2
3 TEST3
--Here the data is stored in IOT in sorted order.
SQL> SELECT table_name,iot_type,iot_type FROM user_tables WHERE upper(table_name) = 'IOT';
TABLE_NAME IOT_TYPE IOT_TYPE
------------------------------ ------------ ------------
IOT IOT IOT
SQL> SELECT segment_name,segment_type,tablespace_name FROM user_segments WHERE upper(segment_name) = 'IOT';
no rows selected
SQL> SELECT index_name,index_type,table_name FROM user_indexes WHERE upper(table_name) = 'IOT';
INDEX_NAME INDEX_TYPE TABLE_NAME
------------------------------ --------------------------- ------------------------------
SYS_IOT_TOP_85199 IOT - TOP IOT
SQL> SELECT segment_name,segment_type,tablespace_name FROM user_segments WHERE upper(segment_name) = 'SYS_IOT_TOP_85199';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------ --------------------
SYS_IOT_TOP_85199 INDEX SYSTEM
SQL> SET AUTOTRACE ON EXPLAIN
--1 I/O
SQL> SELECT * FROM iot WHERE id = 1;
ID NAME
---------- --------------------
1 TEST1
Execution Plan
----------------------------------------------------------
Plan hash value: 3981810243
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 25 | 1 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| SYS_IOT_TOP_85199 | 1 | 25 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("ID"=1)SQL> CREATE TABLE iot2
2 (
3 id INT PRIMARY KEY,
4 name VARCHAR2(20),
5 salary INT
6 )
7 ORGANIZATION INDEX
8 INCLUDING name OVERFLOW;
Table created.
--Here id & name will be stored in IOT type index & salary will be stored in a different OVERFLOW segment.
SQL> INSERT INTO iot2 VALUES(1,'TEST1',2000);
1 row created.
SQL> INSERT INTO iot2 VALUES(3,'TEST3',3000);
1 row created.
SQL> INSERT INTO iot2 VALUES(2,'TEST2',4000);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> SELECT * FROM iot2;
ID NAME SALARY
---------- -------------------- ----------
1 TEST1 2000
2 TEST2 4000
3 TEST3 3000
--Here the data is stored in IOT in sorted order.
SQL> SELECT table_name,iot_type FROM user_tables WHERE upper(table_name) = 'IOT2';
TABLE_NAME IOT_TYPE
------------------------------ ------------
IOT2 IOT
SQL> SELECT segment_name,segment_type,tablespace_name FROM user_segments WHERE upper(segment_name) = 'IOT2';
no rows selected
SQL> SELECT index_name,index_type,table_name FROM user_indexes WHERE upper(table_name) = 'IOT2';
INDEX_NAME INDEX_TYPE TABLE_NAME
------------------------------ --------------------------- ------------------------------
SYS_IOT_TOP_85201 IOT - TOP IOT2
SQL> SELECT segment_name,segment_type,tablespace_name FROM user_segments WHERE upper(segment_name) = 'SYS_IOT_TOP_85201';
SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME
--------------------------------------------------------------------------------- ------------------ ---------------------
SYS_IOT_TOP_85201 INDEX SYSTEM
--This segment/table wil have OVERFLOW salary information.
SQL> SELECT table_name,iot_type FROM user_tables WHERE upper(iot_name) = 'IOT2';
TABLE_NAME IOT_TYPE
------------------------------ ------------
SYS_IOT_OVER_85201 IOT_OVERFLOW Maintenance Of Index Organized Tables
As with B*Tree indexes, IOTs can become fragmented and may need to be rebuilt. If the IOT has no overflow it can be rebuilt offline or online.
ALTER TABLE table_name MOVE INITRANS 10;
ALTER TABLE table_name MOVE ONLINE INITRANS 10;If the IOT does have overflow it can only be rebuilt offline.
ALTER TABLE table_name MOVE TABLESPACE iot_tablespace OVERFLOW TABLESPACE overflow_tablespace;
