Index Organized Tables (IOT) 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.
- Why Use Index Organized Tables
- Creation Of Index Organized Tables
- Maintenance Of Index Organized Tables
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.
Creation Of Index Organized Tables
To create an index organized table you must:
- Specify the primary key using a column or table constraint.
- Use the
In addition you can:
PCTTHRESHOLDto define the percentage of the block that is reserved for an IOT row. If the row exceeds this size the key columns (head piece) is stored as normal, but the non-key data (tail piece) is stored in an overflow table. A pointer is stored to locate the tail piece.
OVERFLOW TABLESPACEto define the tablespace that the overflow data will be stored in.
INCLUDINGto define which non-key columns are stored with the key columns in the head piece, should overflow be necessary.
CREATE TABLE locations (id NUMBER(10) NOT NULL, description VARCHAR2(50) NOT NULL, map BLOB, CONSTRAINT pk_locations PRIMARY KEY (id) ) ORGANIZATION INDEX TABLESPACE iot_tablespace PCTTHRESHOLD 20 INCLUDING description OVERFLOW TABLESPACE overflow_tablespace;
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;
For more information see:
- Index Organized Table Enhancements (9i)
- Overview of Index-Organized Tables
- Managing Index-Organized Tables
Hope this helps. Regards Tim...