Index Organized Tables (IOT)

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

Related articles.

• Index Organized Table Enhancements (9i)

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 ORGANIZATION INDEX.

In addition you can:

• Use PCTTHRESHOLD to 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 peice) is stored in an overflow table. A pointer is stored to locate the tail piece.
• Use OVERFLOW TABLESPACE to define the tablespace that the overflow data will be stored in.
• Use INCLUDING to define which non-key columns are stored with the key columns in the head peice, should overflow be neccessary.

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)

Credit goes to the below website(s) :

http://www.oracle-base.com/articles/8i/index-organized-tables.php

Oracle Index-Organized Tables Oracle Tips by Burleson Consulting

About Oracle Index-Organized Tables Oracle Index-organized tables (IOTs) are a unique style of table structure that is stored in a B-tree index structure. Besides storing the primary key values of an Oracle indexed-organized tables row, each index entry in the B-tree also stores the non-key column values. Oracle Indexed-organized tables provide faster access to table rows by the primary key or any key that is a valid prefix of the primary key. Because the non-key columns of a row are present in the B-tree leaf block itself, there is no additional block access for index blocks.Also see: Super App to use Oracle IOT's, and Oracle Index-organized table (IOT) cluster. Applications manipulate, select, and can update the Oracle index-organized tables just like ordinary tables, using SQL statements. The following sections show the enhancements related to Oracle that are available in the10g Database release. LIST Partition for Oracle Index Organized tables In the previous release of the database, you could partition an Oracle index-organized table by range or by a hash on column values. The partitioning columns had to form a subset of the primary key columns. Just like ordinary tables, local partitioned (prefixed and non-prefixed) indexes as well as global partitioned (prefixed) indexes were supported for partitioned Oracle index-organized tables. With release 10g, you will also be able to partition by the list method. IOT - Oracle Index-organized tables - These structures remove the primary key from the table and keep them solely in the index, saving disk space. Primarily used with high-updates tables, Oracle Index Organized tables structure reduces table fragmentation. Dr. Tim Hall, author of the book "Oracle Job Scheduling" has a great discussion on the benefits of Oracle 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 tables data in a separate structure. Lack of duplication of the key columns in an index and table mean the total storage requirements are reduced. Index-only tables have been around since Oracle8. If neither the Oracle  HASH or Oracle INDEX ORGANIZED options are used with the CREATE TABLE command, then a table is created as a standard hash table. If the Oracle INDEX ORGANIZED option is specified, the table is created as a B-tree-organized table identical to a standard Oracle index created on similar columns. Oracle Index-organized tables do not have rowids. Oracle index-organized tables have the option of allowing overflow storage of values that exceed optimal index row size, as well as allowing compression to be used to reduce storage requirements. Overflow parameters can include columns and the percent threshold value to begin overflow. An index-organized table must have a primary key. Oracle index-organized tables are best suited for use with queries based on primary key values. Oracle index-organized tables can be partitioned in Oracle8i and in Oracle9i as long as they do not contain LOB or nested table types. http://www.dba-oracle.com/t_index_organized_tables.htm Index-organized table 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). 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 primary key indexes, which store only the columns included in it definition, IOT indexes store all the columns of the table (see below for an exception to this rule - called the overflow area). Contents  [hide]  1 Properties and restrictions 2 Advantages of an IOT 3 Row overflow area 4 Example of an IOT without an overflow area 5 Example of an IOT with an overflow area 6 FAQ 6.1 How to move an IOT into another tablespace? 6.2 How to know which columns are in the overflow segment? 7 Also see [edit]Properties and restrictions An IOT must contain a primary key. Rows are accessed via a logical rowid and not a physical rowid like in heap-organized tables. 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. [edit]Advantages of an IOT As an IOT has the structure of an index and stores all the columns of the row, accesses via primary key conditions are faster as they don't need to access the table to get additional column values. 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. As the index and the table are in the same segment, less storage space is needed. In addition, as rows are stored in the primary key order, you can further reduce space with key compression. As all indexes on an IOT uses logical rowids, they will not become unusable if the table is reorganized. [edit]Row 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 of statements that do not need access the columns in the overflow area. 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 (see example below). [edit]Example of an IOT without an overflow area The following example creates a simple IOT table and shows the objects and segments that are created. (This example was tested on Oracle versions 9.2 to 11.2.) SQL> CREATE TABLE my_iot (id INTEGER PRIMARY KEY, value VARCHAR2(50)) 2 ORGANIZATION INDEX; Table created. SQL> SELECT table_name, iot_type, iot_name FROM user_tables; TABLE_NAME IOT_TYPE IOT_NAME ------------------------------ ------------ ------------------------------ MY_IOT IOT SQL> SELECT index_name, index_type, table_name FROM user_indexes; INDEX_NAME INDEX_TYPE TABLE_NAME ------------------------------ --------------------------- ------------------------------ SYS_IOT_TOP_71133 IOT - TOP MY_IOT SQL> SELECT object_id, object_name, object_type FROM user_objects ORDER BY 1; OBJECT_ID OBJECT_NAME OBJECT_TYPE ---------- -------------------- ------------------- 71133 MY_IOT TABLE 71134 SYS_IOT_TOP_71133 INDEX SQL> SELECT segment_name, segment_type FROM user_segments ORDER BY 1; SEGMENT_NAME SEGMENT_TYPE -------------------- ------------------ SYS_IOT_TOP_71133 INDEX Note: In 11g, you must use the following syntax to see the same output: CREATE TABLE my_iot (id INTEGER PRIMARY KEY, value VARCHAR2(50)) SEGMENT CREATION IMMEDIATE ORGANIZATION INDEX; The reason for is that by default in 11g the segment is created only when the first row is inserted. As you can see 2 objects are created: the table and the index, but there is only 1 segment (implementation of the object) which is the index one. The name of the index is by default "SYS_IOT_TOP_<table object number>" and its type is "IOT - TOP". You can choose the name of your index using the following syntax: SQL> CREATE TABLE my_iot (id INTEGER CONSTRAINT my_iot_pk PRIMARY KEY, value VARCHAR2(50)) 2 ORGANIZATION INDEX; Table created. SQL> SELECT table_name, iot_type, iot_name FROM user_tables; TABLE_NAME IOT_TYPE IOT_NAME ------------------------------ ------------ ------------------------------ MY_IOT IOT SQL> SELECT index_name, index_type, table_name FROM user_indexes; INDEX_NAME INDEX_TYPE TABLE_NAME ------------------------------ --------------------------- ------------------------------ MY_IOT_PK IOT - TOP MY_IOT SQL> SELECT object_id, object_name, object_type FROM user_objects ORDER BY 1; OBJECT_ID OBJECT_NAME OBJECT_TYPE ---------- -------------------- ------------------- 71135 MY_IOT TABLE 71136 MY_IOT_PK INDEX SQL> SELECT segment_name, segment_type FROM user_segments ORDER BY 1; SEGMENT_NAME SEGMENT_TYPE -------------------- ------------------ MY_IOT_PK INDEX The IOT_NAME column is empty and will be useful in the example of the next section. [edit]Example of an IOT with an overflow area The following example creates an IOT with an overflow area and shows the objects and segments that are created. (This example was tested in versions 9.2 to 11.2, see the note in the previous section about 11g.) SQL> CREATE TABLE my_iot (id INTEGER PRIMARY KEY, value VARCHAR2(50), comments varchar2(1000)) 2 ORGANIZATION INDEX 3 INCLUDING value OVERFLOW; Table created. SQL> SELECT table_name, iot_type, iot_name FROM user_tables ORDER BY 1; TABLE_NAME IOT_TYPE IOT_NAME ------------------------------ ------------ ------------------------------ MY_IOT IOT SYS_IOT_OVER_71142 IOT_OVERFLOW MY_IOT SQL> SELECT table_name, column_name FROM user_tab_columns ORDER by table_name, column_id; TABLE_NAME COLUMN_NAME ------------------------------ ------------------------------ MY_IOT ID MY_IOT VALUE MY_IOT COMMENTS SQL> SELECT index_name, index_type, table_name, include_column FROM user_indexes; INDEX_NAME INDEX_TYPE TABLE_NAME INCLUDE_COLUMN ------------------------------ --------------------------- ------------------------- -------------- SYS_IOT_TOP_71142 IOT - TOP MY_IOT 2 SQL> SELECT object_id, object_name, object_type FROM user_objects ORDER BY 1; OBJECT_ID OBJECT_NAME OBJECT_TYPE ---------- -------------------- ------------------- 71142 MY_IOT TABLE 71143 SYS_IOT_OVER_71142 TABLE 71144 SYS_IOT_TOP_71142 INDEX SQL> SELECT segment_name, segment_type FROM user_segments ORDER BY 1; SEGMENT_NAME SEGMENT_TYPE -------------------- ------------------ SYS_IOT_OVER_71142 TABLE SYS_IOT_TOP_71142 INDEX All the columns up to and including the one named in the INCLUDING option of the OVERFLOW clause are in the top segment; the remaining ones are in the overflow segment. Here, we see that 2 table objects are created, the main one is of iot_type IOT and the overflow one is of iot_type IOT_OVERFLOW. The name of the overflow table is SYS_IOT_OVER_<table object number>. You can see now the purpose of the IOT_NAME column, it gives the name of the IOT table for its overflow one. You can also see that 2 segments are created: the index of the IOT and the overflow area. In the end, the USER_INDEXES view gives you the last column included in the index in its INCLUDE_COLUMN column. [edit]FAQ [edit]How to move an IOT into another tablespace? Based on the example used in the previous section: SQL> SELECT segment_name, segment_type, tablespace_name FROM user_segments ORDER BY 1; SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME ------------------------------ ------------------ ------------------------------ SYS_IOT_OVER_71142 TABLE TS_D01 SYS_IOT_TOP_71142 INDEX TS_D01 Let's assume you want to move all the segments from their current tablespace to another tablespace. You'll quickly discover that you cannot use the standard commands: SQL> ALTER TABLE sys_iot_over_71142 MOVE TABLESPACE ts_i01; ALTER TABLE sys_iot_over_71142 MOVE TABLESPACE ts_i01 * ERROR at line 1: ORA-25191: cannot reference overflow table of an index-organized table SQL> ALTER INDEX sys_iot_top_71142 REBUILD TABLESPACE ts_i01; ALTER INDEX sys_iot_top_71142 REBUILD TABLESPACE ts_i01 * ERROR at line 1: ORA-28650: Primary index on an IOT cannot be rebuilt You have to first find the name of the associated IOT table: SQL> SELECT iot_name FROM user_tables WHERE table_name = 'SYS_IOT_OVER_71142'; IOT_NAME ------------------------------ MY_IOT SQL> SELECT table_name FROM user_indexes WHERE index_name = 'SYS_IOT_TOP_71142'; TABLE_NAME ------------------------------ MY_IOT Then you can move the segments: SQL> ALTER TABLE my_iot MOVE TABLESPACE ts_i01; Table altered. SQL> ALTER TABLE my_iot MOVE OVERFLOW TABLESPACE ts_i01; Table altered. SQL> SELECT segment_name, segment_type, tablespace_name FROM user_segments ORDER BY 1; SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME ------------------------------ ------------------ ------------------------------ SYS_IOT_OVER_71142 TABLE TS_I01 SYS_IOT_TOP_71142 INDEX TS_I01 The first statement moves the top segment and the second one the overflow segment. This can be done in one statement using: SQL> ALTER TABLE my_iot MOVE TABLESPACE ts_d01 OVERFLOW TABLESPACE ts_d01; Table altered. [edit]How to know which columns are in the overflow segment? Remember that the INCLUDE_COLUMN of the USER_INDEXES view gives you the last column included in the index and so in the top segment, so joining this view with the USER_TAB_COLUMNS you can separate the columns that are in the top segment from those that are in the overflow one as with the following statement: SQL> SELECT c.table_name, c.column_name, 2 CASE WHEN c.column_id <= i.include_column THEN 'TOP' ELSE 'OVERFLOW' END segment 3 FROM user_tab_columns c, user_indexes i 4 WHERE i.table_name = c.table_name 5 ORDER by table_name, column_id; TABLE_NAME COLUMN_NAME SEGMENT ------------------------------ ------------------------------ -------- MY_IOT ID TOP MY_IOT VALUE TOP MY_IOT COMMENTS OVERFLOW Credit goes to the below website(s) : http://www.orafaq.com/wiki/Index-organized_table