Index Organized Tables In Oracle

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;