A normal table stores rows wherever there is room, and its primary key index points to them, so every lookup by key reads the index and then the table. An index-organized table (IOT) stores the rows inside the primary key's B-tree index itself, sorted by the key. Lookups and range scans by the key read one structure instead of two, and the data takes less space.
Code for This Guide
The main examples are in the examples/special-tables folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.
They come from Oracle Database 26ai SQL and PL/SQL Book.
Syntax
create table table ( column type primary key, ... ) organization index [overflow ...]
An IOT must have a primary key. OVERFLOW stores less-used, wide columns in a separate segment so the index stays compact.
Create an IOT
Example:
create table airport_codes_iot ( airport_code char(3) primary key, city varchar2(40) ) organization index; insert into airport_codes_iot select airport_code, city from airports; select table_name, iot_type from user_tables where table_name = 'AIRPORT_CODES_IOT'; select index_name, index_type from user_indexes where table_name = 'AIRPORT_CODES_IOT';
Output:
Table AIRPORT_CODES_IOT created. 22 rows inserted. TABLE_NAME IOT_TYPE ____________________ ___________ AIRPORT_CODES_IOT IOT INDEX_NAME INDEX_TYPE ____________________ _____________ SYS_IOT_TOP_84260 IOT - TOP
USER_TABLES reports IOT_TYPE = IOT, and the only segment is an index of type IOT - TOP, named by the system: there is no separate table. Drop the table afterward with drop table airport_codes_iot purge.
The Primary Key Is Required
Example:
create table codes_iot (code char(3), city varchar2(40)) organization index;
Output:
Error starting at line : 1 In command - create table codes_iot (code char(3), city varchar2(40)) organization index Error report - ORA-25175: no PRIMARY KEY constraint found
When to Use an IOT
- Lookup and code tables accessed by their key, such as airport codes or currency rates.
- Tables queried by key ranges, such as readings by sensor and time, where the sort order helps.
- Association tables of two foreign keys, whose primary key is both columns.
Avoid IOTs for tables that are mostly accessed through other columns, or whose rows are wide and change often, since secondary indexes on an IOT use logical rowids and rows move when the key changes.
Related Guides
Conclusion
An index-organized table stores its rows in the B-tree of its primary key, sorted by the key, so access by key is fast and storage is compact. Create it with ORGANIZATION INDEX and a primary key, and use it for tables that are always read by their key.
