Introduction to Oracle Index-Organized Tables

An Oracle index-organized table (IOT) is a table whose rows are stored in a B-tree index structure according to the table's primary key. In a conventional heap-organized table, Oracle stores table rows in a heap segment and normally maintains the primary-key index as a separate segment. A primary-key lookup therefore may require one access to find the index entry and another access to retrieve the table row identified by the rowid. In an IOT, the primary-key index is the table: its leaf entries contain the primary-key values and the row's non-key column values.

This organization can reduce I/O for applications that retrieve rows primarily by the full primary key, a leading portion of a composite primary key, or a range of primary-key values. It can also eliminate the duplicated primary-key storage associated with maintaining both a heap row and a separate primary-key index. These advantages make IOTs useful for selected lookup, associative, time-series, and high-throughput OLTP tables.

IOTs were introduced with Oracle8, but they are not merely a legacy feature. Oracle Database 23ai continues to support their creation, secondary indexes, overflow storage, compression, partitioning, LOBs, and maintenance. Oracle has also added Advanced LOW IOT Compression, while Oracle Database 23ai used with Exadata System Software 24ai can apply Smart Scan to IOTs. The physical design remains relevant, but it must be selected for a measured workload rather than treated as a universal replacement for heap tables.

A B-tree contains branch blocks and leaf blocks. Branch blocks guide Oracle toward the key range that contains the requested value. The leaf blocks hold the ordered key entries. In an ordinary primary-key index on a heap table, a leaf entry contains the indexed key and a physical rowid that points to the table block containing the rest of the row. Oracle follows that rowid when the query needs columns that are not available from the index.

An IOT removes that separation. The leaf entry contains the primary key together with the non-key column values stored in the index portion of the row. Once Oracle reaches the correct leaf entry, it may already have every column requested by the query. The phrase the index is the table describes this defining characteristic.

Because the primary key determines the B-tree position, every IOT must have a primary-key constraint. Oracle maintains the rows in primary-key order as inserts, updates, and deletes occur. Applications still manipulate the object with ordinary SELECT, INSERT, UPDATE, and DELETE statements; the difference is physical organization, not a different relational or SQL interface.

This comparison describes the storage model, not a guaranteed performance result. The optimizer's choices and the cost of each design depend on row width, primary-key width, data volume, caching, secondary indexes, overflow use, DML activity, statistics, and the SQL being executed.

The defining syntax is the ORGANIZATION INDEX clause of the CREATE TABLE statement:

CREATE TABLE customer_accounts_iot (
    account_id   NUMBER,
    account_name VARCHAR2(100) NOT NULL,
    status       VARCHAR2(20)  NOT NULL,
    CONSTRAINT customer_accounts_iot_pk
        PRIMARY KEY (account_id)
)
ORGANIZATION INDEX;

Oracle stores each row in the B-tree position determined by account_id. A lookup by that column can reach the leaf entry containing account_name and status without following a rowid to a separate heap segment:

SELECT account_name, status
FROM   customer_accounts_iot
WHERE  account_id = 42017;

This introductory example intentionally omits options such as overflow storage, compression, partitioning, and tablespace placement. Later lessons explain how those choices affect a production IOT.

Suppose an application retrieves a customer account thousands of times per minute by account_id. With a heap table and primary-key index, Oracle traverses the B-tree to find the rowid and then reads the heap block containing the row. With an IOT, the B-tree traversal can end at the complete row. Avoiding the second structure can reduce logical I/O, particularly when the required heap block is not already in the buffer cache.

The ordered structure is also useful for ranges. Consider an event table whose composite primary key is (device_id, event_time). Rows for one device are kept together in time order. Oracle can navigate to the beginning of one device's range and move through adjacent leaf entries:

SELECT event_time, event_type, event_value
FROM   device_events_iot
WHERE  device_id = :device_id
AND    event_time >= :start_time
AND    event_time <  :end_time
ORDER  BY event_time;

This design aligns physical organization with the query predicate and requested order. The same composite key is less helpful when most queries filter only on event_time, because event_time is not the leading primary-key column. Column order in a composite primary key is therefore a central IOT design decision.

An IOT is most attractive when its primary key is the dominant access path and the complete row is reasonably compact. Common candidates include:

• Lookup and key-value tables: applications repeatedly retrieve a small row by a unique identifier.
• Associative tables: intersection tables use a compact composite primary key and contain few additional columns.
• Time-series history: a primary key such as (instrument_id, observation_time) keeps one instrument's observations together and ordered.
• Document or message metadata: small metadata rows are frequently accessed by a stable key, while large content is stored separately.
• Primary-key range processing: applications repeatedly process contiguous ranges based on a primary key or its leading columns.

IOTs can be especially effective in high-throughput OLTP systems when primary-key requests dominate. They can also support analytic access that scans the IOT or ordered key ranges. However, neither the workload label “OLTP” nor “analytics” is enough to justify the design. Representative statements and data must demonstrate the advantage.

The feature's strengths create corresponding trade-offs. An IOT row is an index entry, so a wide primary key or wide row reduces the number of entries that fit in each leaf block. This can increase the size and height of the B-tree, create more block splits, and weaken the I/O advantage. A narrow, stable primary key is usually a better foundation than a long composite key containing frequently changed values.

Access through non-primary-key columns may require secondary indexes. Because IOT rows do not have permanent physical heap addresses, a secondary index stores a logical rowid derived from the primary key. Oracle may store a physical guess to accelerate access, but the guess can become stale after rows move between leaf blocks. The logical rowid remains valid, although Oracle may need an additional primary-key B-tree search to reach the row.

Large rows may need an overflow segment. Oracle always keeps the primary-key columns in the index portion, while selected non-key columns can be placed in overflow storage. Overflow prevents bulky values from consuming excessive leaf-block space, but retrieving those values requires an additional access. Choosing where to divide the row is therefore a balance between a compact B-tree and avoiding unnecessary overflow reads.

Insert patterns also matter. A monotonically increasing primary key directs new entries toward the right-hand edge of the B-tree. Under heavy concurrency, that pattern can concentrate block activity. Updating the primary key is more expensive than updating an ordinary non-key value because the new key changes the row's position in the B-tree.

A heap table may remain the better choice when queries use many unrelated access paths, full scans dominate, rows are very wide, the primary key is large or frequently updated, or the workload gains little from primary-key ordering. Conventional indexes can then be added only for the access paths that need them.

Relational SQL does not promise the order of a result set unless the statement contains an ORDER BY clause. An IOT physically organizes rows by primary key, and the optimizer can use that organization to satisfy some ordered access efficiently, but an application must still specify the required result order. It should never depend on rows happening to appear in primary-key order.

This distinction reflects the separation between a table's logical meaning and its physical implementation. Applications work with the same relational table regardless of whether Oracle stores it as a heap or an IOT. For additional background on physical data independence, see Codd's Rule Number 8.

Advanced LOW IOT Compression

Earlier IOT compression relied primarily on prefix compression, formerly called key compression. Prefix compression stores repeated leading primary-key values more efficiently, but a DBA must choose an appropriate prefix length. If the chosen data does not contain enough repetition, compression overhead can outweigh the savings.

Oracle Database 23ai introduces Advanced LOW IOT Compression. The feature reduces the storage footprint of IOTs while avoiding much of the manual prefix analysis associated with traditional compression. Compression still must be tested against actual data and workload behavior; the presence of a new compression method does not guarantee the same savings for every table.

Exadata Smart Scan for IOTs

Oracle Database 23ai in conjunction with Exadata System Software 24.1.0 or later supports Exadata Smart Scan on IOTs, including compressed IOTs. Smart Scan offloads eligible processing to Exadata storage servers. An execution plan can show this access through operations such as INDEX STORAGE FAST FULL SCAN or INDEX STORAGE FULL SCAN.

Oracle reports that analytic queries on IOTs using this Exadata capability can be up to 13 times faster. That result is specifically an Exadata Smart Scan figure: it is not a promise that every IOT query, every OLTP request, or a non-Exadata Oracle Database 23ai system will receive the same improvement. The correct plan and benefit must be confirmed on the target platform.

Before converting a heap table or creating a new IOT, answer the following questions:

1. Do the most important queries use the full primary key, a leading key prefix, or a primary-key range?
2. Is the primary key narrow, stable, and ordered in the same way the workload accesses the data?
3. Are the rows compact enough to preserve efficient leaf-block density?
4. Which non-key queries require secondary indexes, and what will those indexes cost?
5. Will large or infrequently accessed columns require an overflow segment?
6. Do insert and update patterns create block contention or frequent row movement?
7. Does a representative test show fewer buffer gets, acceptable DML cost, and useful space savings compared with a heap design?

Use current optimizer statistics and compare execution plans, logical reads, elapsed time, storage, and concurrency under realistic data volumes. An IOT is successful when its physical organization matches the application's persistent access pattern—not simply when the table can be created with ORGANIZATION INDEX.

This module develops the introductory model into practical design and administration skills. By the end of the module, you should be able to:

1. Explain how an index-organized table differs from a heap-organized table.
2. Identify workloads that benefit from an IOT and recognize unsuitable workloads.
3. Create an index-organized table with an appropriate primary key.
4. Create and evaluate secondary indexes on an IOT.
5. Use overflow storage to manage large rows.
6. Modify and delete index-organized tables safely.
7. Reorganize an IOT when its structure or storage requirements change.

The next lesson examines the advantages and disadvantages of index-organized tables in greater detail. It will help you decide whether the reduced primary-key access cost justifies the storage, DML, secondary-index, and maintenance trade-offs for a particular table.