Index-organized table

From Oracle FAQ
⧼orafaq-jumptonavigation⧽⧼orafaq-jumptosearch⧽

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 its definition, IOT indexes store all the columns of the table (see below for an exception to this rule - called the overflow area).

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.

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.

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. 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 (see example below).

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.

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.

FAQ

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.

However, this is only the main part of the IOT - the overflow part continues to reside in its original tablespace:

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_I01

To move the overflow area also, an additional statement is necessary: 

 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

Both steps can be combined in one statement: 

SQL> ALTER TABLE my_iot MOVE TABLESPACE ts_d01 OVERFLOW TABLESPACE ts_d01;

Table altered.

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

Also see

Glossary of Terms
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z #


Retrieved from "http://www.orafaq.com/wiki/index.php?title=Index-organized_table&oldid=15369"