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 its definition, IOT indexes store all the columns of the table (see below for an exception to this rule - called the overflow area).
Contents
Properties and restrictions[edit]
- 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[edit]
- 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[edit]
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[edit]
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[edit]
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[edit]
How to move an IOT into another tablespace?[edit]
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?[edit]
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[edit]
| 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 | # |
