Oracle Index Organized Table (IOT)

Tables in RDBMS are heap-organized (unsorted), while the index organized tables are organized in a B-tree index structure and specified by a primary key.

The B-tree (balanced tree) index structure is analogous to an inverted tree and the flow is from root-branch-leaf nodes. The index starts with a header node and leaf node with the header node consisting of a pointer to the leaf node and data stored in the leaf block. Each leaf block of the B-tree index structure stores both the key and nonkey columns. As the data volume increases, new leaf blocks are added. When header block gets filled up, new header blocks are added, which form branches. The B-tree index can be compressed to improve I/O, making IOT a good choice for OLTP systems.

In the example below, when the length of a row exceeds 20% of the index block size (specified by PCTTHRESHOLD 20), the non-key columns are moved over to the overflow storage (specified by OVERFLOW clause). The including clause (specified by INCLUDING country_desc) forces the specified column(s) to be included in the key index leaf block.

The PCTTHRESHOLD takes precedence over INCLUDING in the event of a conflict between PCTTHRESHOLD value and the INCLUDING clause.

Oracle Index Organized Table (IOT) Script

CREATE TABLE iot_country
(country_tld_id     VARCHAR2(2)
                    CONSTRAINT country_tld_id_nn NOT NULL,
 country_code       VARCHAR2(30),
 country_desc       VARCHAR2(100) 
                    CONSTRAINT country_desc_nn NOT NULL,
 currency_code      VARCHAR2(3),
 time_zones         NUMBER,
 created_date       DATE DEFAULT SYSDATE NOT NULL,
 updated_date       DATE,
                    CONSTRAINT country_tld_id_iot_pk
                    PRIMARY KEY (country_tld_id)
 )
  ORGANIZATION INDEX 
  INCLUDING       country_desc
  PCTTHRESHOLD    20
   STORAGE 
     (INITIAL      4K 
      NEXT         2K 
      PCTINCREASE  0 
      MINEXTENTS   1 
      MAXEXTENTS   1) 
   OVERFLOW 
    STORAGE 
     (INITIAL      4K 
      NEXT         2K 
      PCTINCREASE  0 
      MINEXTENTS   1 
      MAXEXTENTS   1);

-- Index Organized Tables Analysis
SELECT table_name, iot_name, iot_type, last_analyzed  
FROM user_tables
WHERE  INSTR(table_name,'IOT') > 0;

SELECT index_name, index_type, table_name 
FROM user_indexes
WHERE  INSTR(index_name,'IOT') > 0;

SELECT object_id, object_name, object_type 
FROM user_objects 
WHERE  INSTR(object_name,'IOT') > 0
ORDER BY object_id;

SELECT segment_name, segment_type,
 ROUND(Sum(bytes)/1024/1024,3) AS size_in_mb
FROM user_segments 
WHERE  INSTR(segment_name,'IOT') > 0
ORDER BY segment_name;

Oracle registered trademark of Oracle Corporation.

Last Revised On: July 28, 2014

73985