Heap-Organized Tables versus Index-Organized Tables

Oracle Database normally stores table rows in a heap-organized table. The word heap describes a storage structure in which rows are placed wherever suitable space is available; it does not mean that the rows are logically unordered to SQL. A primary-key index on that table is normally a separate B-tree structure. Its leaf entries contain primary-key values and physical rowids that identify the corresponding rows in the heap segment.

An index-organized table (IOT) combines those roles. Its primary-key B-tree is the table's primary storage structure. The B-tree leaf entries contain every primary-key column together with the non-key columns stored in the index portion of each row. This physical organization can make primary-key and leading-key-prefix access efficient because Oracle may find the requested row data in the same structure used to locate its key.

An IOT is not a special relational object with a different SQL interface. Applications still use ordinary SELECT, INSERT, UPDATE, and DELETE statements. The distinction is physical: a heap table keeps its rows in a heap segment, whereas an IOT stores its rows in a B-tree ordered by the primary key.

A B-tree contains a root block, branch blocks, and leaf blocks. Oracle traverses the root and appropriate branch blocks to reach the leaf block containing the requested key. In a conventional primary-key index, the matching leaf entry contains both the indexed key and a physical ROWID. That rowid encodes information Oracle can use to locate the corresponding row in the heap segment.

If a query requests columns that are not available from the index, Oracle normally follows the rowid from the leaf entry to the heap row. The index and table are therefore separate access structures. The heap rows need not be physically arranged in primary-key order; row placement depends on available space and subsequent data changes.

This description must not be reduced to a fixed I/O formula. An index can satisfy some queries without a table access, and cached blocks do not require physical reads. B-tree height, predicate selectivity, row distribution, clustering factor, caching, partition pruning, and the optimizer's chosen execution plan all affect the work performed. The defining point is structural: when non-indexed values are required, a conventional index entry points to a separate heap row.

An IOT must have a primary key because that key determines the row's position in the B-tree. Branch blocks guide Oracle toward the relevant key range, while the leaf entries contain the primary-key values and the row's non-key values stored in the index portion. The phrase the index is the table summarizes this defining characteristic.

When Oracle locates an IOT leaf entry, the requested columns may already be available there. A separate heap segment does not exist for the primary row, so Oracle does not follow a permanent physical heap rowid. This can avoid the additional heap-table access required by a conventional index lookup when the query needs columns outside that index.

Rows are physically maintained in primary-key order as data changes. Inserts can split B-tree blocks, deletes can leave reusable space, and changing a primary-key value can move a row to another position in the tree. Physical organization does not guarantee SQL result order: every query that requires a particular order must still specify an ORDER BY clause.

The following diagram compares a primary-key lookup through a conventional heap-table index with the corresponding lookup through an index-organized table.

In the left panel, Oracle searches the primary-key B-tree, finds the entry for PK 1002, and obtains its physical rowid. It then follows that rowid to the matching row in the separately stored heap blocks. The deliberately different row order in the heap area illustrates that heap rows are not stored by primary-key value.

In the right panel, Oracle searches the IOT's primary-key B-tree and reaches the entry for PK 1002. The primary key, account_name, and status occupy the same conceptual leaf entry, so those values can be returned without accessing a separate heap row. This is a simplified model rather than an I/O guarantee. Actual access cost depends on the execution plan, cached blocks, B-tree height, row width, overflow storage, and the columns requested by the query.

The comparison describes physical organization, not a universal performance result. Row width, key width, caching, overflow use, secondary indexes, optimizer statistics, DML activity, and the SQL statement all influence the actual cost.

Use the ORGANIZATION INDEX clause of CREATE TABLE to create an IOT. The following table uses the composite primary key (product_id, sku):

CREATE TABLE product_skus_iot (
    product_id   NUMBER,
    sku          VARCHAR2(64),
    product_name VARCHAR2(200) NOT NULL,
    status       VARCHAR2(20)  NOT NULL,
    CONSTRAINT product_skus_iot_pk
        PRIMARY KEY (product_id, sku)
)
ORGANIZATION INDEX;

The primary-key constraint is required. Oracle uses product_id followed by sku to determine each row's B-tree position. In this basic example, product_name and status can be stored with the primary-key values in the leaf entry.

A lookup that supplies both primary-key values can navigate directly to one product and SKU:

SELECT product_name,
       status
FROM   product_skus_iot
WHERE  product_id = 42017
AND    sku = 'DB-23AI';

Oracle may return the selected values from the primary-key leaf entry. A predicate on product_id alone can also use the leading portion of the composite key to find a range of SKUs for one product. This introductory definition omits tablespace placement, compression, partitioning, and overflow options so that the primary storage model remains clear.

An IOT is most promising when the physical order of its primary key matches a persistent access pattern. Exact primary-key lookups, leading-key-prefix searches, and primary-key range scans can benefit because related entries are kept together in the B-tree. A compact association table, lookup table, message-metadata table, or application-specific inverted-index structure may fit this model well.

A narrow and stable primary key is generally a better foundation than a large key containing frequently updated values. Compact rows allow more entries to fit in each leaf block, helping preserve B-tree density. Avoiding a separate primary-key index can also reduce duplication of primary-key values between a heap segment and its index.

Some high-volume OLTP workloads benefit when their dominant requests are primary-key based. The label OLTP, however, is not sufficient justification. An orders table queried through customer, date, status, fulfillment center, and several other unrelated paths may depend heavily on secondary indexes and may not gain enough from primary-key organization to offset their costs.

The IOT's strengths create corresponding trade-offs. A wide primary key enlarges both the primary B-tree and the logical rowids used by secondary indexes. Wide rows reduce the number of entries that fit in a leaf block and can increase the tree's space requirements. Inserts can cause block splits, and a steadily increasing key can concentrate concurrent inserts at the right-hand edge of the B-tree. Updating the primary key is especially significant because it changes the row's B-tree position.

Queries that do not use the primary key or one of its leading columns may need secondary indexes. Each secondary index consumes storage and adds maintenance work to inserts, updates, and deletes. Reaching the row through a secondary index may also require navigating the IOT's primary-key B-tree after Oracle finds the secondary key.

A heap-organized table may remain preferable when full scans dominate, rows are very wide, the primary key is large or frequently updated, inserts do not distribute well, or applications use many unrelated access paths. The design decision should compare complete workloads rather than one favorable lookup.

An IOT does not require every byte of every row to remain in the primary-key leaf entry. Oracle can divide a row into an index portion and an overflow segment. Primary-key columns always remain in the index portion, while trailing non-key columns can be placed in overflow storage.

The PCTTHRESHOLD option controls how much of an index block an IOT row can occupy, and INCLUDING column_name can define a logical boundary between columns retained in the index portion and those eligible for overflow. Moving large or infrequently requested values to overflow can improve leaf-block density. The trade-off is that a query requesting an overflowed value must also access the overflow segment.

Overflow therefore changes how to interpret the phrase the row is stored in the index. The primary row and its key are represented by the IOT's B-tree entry, but selected non-key values may reside in a separate overflow area. Later lessons can examine how to choose an appropriate threshold and column boundary.

An IOT row can move as the primary-key B-tree changes, so it does not have a permanent physical heap address. A secondary index on an IOT therefore uses a logical rowid derived from the row's primary key. The logical rowid remains valid when the row moves to another leaf block.

Oracle can include a physical guess in the logical rowid. This guess identifies the block where the row is likely to be found and can shorten access when it remains accurate. If row movement makes the guess stale, Oracle can still use the primary-key information in the logical rowid to locate the row. The logical identifier remains valid even though the location hint may need to be refreshed.

Secondary indexes are separate schema structures and should be added for demonstrated access paths. Unique, nonunique, function-based, and certain bitmap secondary indexes are supported, subject to their applicable restrictions. Bitmap indexes require particular care and are generally a poor match for heavily updated, high-concurrency OLTP data.

Before choosing an IOT, examine the workload and table definition:

1. Do the most important queries use the complete 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 application accesses the data?
3. Are the rows compact enough to maintain useful leaf-block density?
4. Which non-primary-key queries require secondary indexes?
5. Which large or infrequently accessed columns should use overflow storage?
6. How will the insert, update, delete, and concurrency patterns affect the B-tree?
7. Does representative testing show an acceptable execution plan, logical-read count, elapsed time, storage footprint, and DML cost?

Create representative heap and IOT versions, gather current optimizer statistics, and compare actual execution plans under realistic data volumes and concurrency. An IOT is effective when its physical organization supports the application's important and persistent access paths—not simply because it can eliminate one type of table lookup.

The next lesson examines the advantages and disadvantages of index-organized tables in greater detail, including the design compromises that determine when this storage model is appropriate.