Oracle 19c: Index-Organized Table (IOT)

In Oracle Database 19c, tables are typically stored as heap-organized tables, where data rows are stored without any particular order and indexes are created separately.

However, Oracle also supports Index-Organized Tables (IOTs), where the table data itself is stored in a B-tree index structure. In this architecture, the primary key index and table data are stored together, eliminating the need for a separate table lookup.

Because the data is stored inside the index, Oracle can retrieve rows directly through Index-Organized Table Scans, improving performance for certain workloads.

1. What is an Index-Organized Table (IOT)?

An Index-Organized Table is a table where the rows are stored sorted by the primary key inside a B-tree index structure.

Unlike traditional tables:

• There is no separate heap table
• The primary key index stores the actual row data

Structure comparison:

Table Type	Data Storage
Heap Table	Data stored in table blocks, indexes separate
Index-Organized Table	Data stored directly in the primary key index

---

2. Creating an Index-Organized Table

Example:

create table tb_iot ( id number(4) primary key, name varchar2(10) not null) organization index;

Here:

• id becomes the primary key index
• The table rows are stored inside the index

Check Indexes:

Note: Oracle create the index name “SYS_IOT_TOP_71067”

Check Segment Name:

Note: The segment name is the name of the index not the name of the table

3. How IOT Scans Work

When querying an Index-Organized Table, Oracle accesses rows directly through the index structure.

Typical process:

1. Oracle searches the B-tree index
2. The index already contains all row data
3. No additional table lookup by ROWID is required

This reduces I/O operations.

4. Types of Access Paths for IOT

Oracle may use several access methods when scanning an Index-Organized Table.

4.1. Index Unique Scan

Occurs when searching by primary key.

Example:

Fast direct access to the row.

4.2. Index Range Scan

Occurs when retrieving multiple rows in a key range.

Example:

Oracle scans the index sequentially.

4.3. Full IOT Scan

If no index condition is provided, Oracle scans the entire index structure.

Example:

This is equivalent to scanning the entire table.

5. Advantages of Index-Organized Tables

• Reduced I/O: Data and index are stored together, eliminating extra table lookups.
• Faster Primary Key Access: Direct access using the index.
• Reduced Storage: Only one structure instead of table + primary key index.
• Efficient Range Queries: Because rows are physically stored in primary key order.

6. Limitations of IOT

Despite their advantages, IOTs have some limitations:

• Not ideal for frequent updates to primary key
• Large rows may require overflow segments
• Secondary indexes may require logical ROWIDs

7. When to Use Index-Organized Tables

IOTs are best suited for:

• Primary key lookup tables
• Read-heavy workloads
• Applications that frequently access rows by primary key
• Range-based queries on primary key

Typical use cases:

• Lookup tables
• Reference data
• Small transactional datasets

8. Checking if a Table is IOT

You can verify if a table is index-organized:

Conclusion

An Index-Organized Table (IOT) in Oracle 19c stores table rows directly inside a B-tree index based on the primary key. This structure eliminates the need for a separate table lookup and allows Oracle to retrieve rows efficiently through index-based scans.