Oracle Index-Organized Tables: Module Conclusion

An index-organized table (IOT) is an Oracle table whose rows are stored in a primary-key B-tree. This physical organization can reduce the work required by suitable primary-key lookups and range scans, but it is not a general performance switch. An effective IOT design begins with a stable primary key and a workload whose important access paths follow that key or one of its leading prefixes.

This module developed the IOT from its basic storage model through creation, overflow design, secondary indexes, alteration, deletion, and reorganization. The central lesson is that the primary-key B-tree is the table's primary storage structure. That fact explains both the potential advantages and the responsibilities that distinguish an IOT from a heap-organized table.

After completing the module, you should be able to:

1. compare heap-organized and index-organized storage;
2. identify workloads that may benefit from primary-key organization;
3. create and verify an IOT with an appropriate primary key;
4. evaluate secondary indexes and their logical rowids;
5. divide large rows between index and overflow portions;
6. alter, drop, move, or redefine an IOT safely;
7. validate the result with dictionary metadata, execution plans, and application tests.

A heap-organized table normally keeps its rows in a heap segment. A conventional primary-key index is a separate B-tree whose leaf entries contain key values and physical rowids. When a query needs columns that are not available from that index, Oracle follows the rowid to the corresponding heap row.

An IOT combines the primary-key access structure and the row storage. Its branch blocks guide Oracle toward the required key range, and its leaf blocks contain the primary-key values together with the non-key values retained in the index portion. There is no separate heap segment for the primary row. Once Oracle reaches the matching leaf entry, the values requested by a primary-key query may already be available.

Every IOT must have a primary-key constraint because the key determines each row's B-tree position and logical identity. The key may contain one column or several columns; a composite key is not required. With a composite key, column order matters. A key defined as (device_id, event_time) supports access beginning with device_id, including a time range for one device. It does not provide the same direct ordering for a predicate on event_time alone.

An IOT row has no permanent heap-table physical rowid. The ROWID pseudocolumn returns a logical rowid, and secondary indexes use logical rowids based on the primary key. A logical rowid can contain a physical guess to accelerate access to the expected leaf block. The logical identity remains valid if a row moves, although the physical guess can become stale. A column intended to store either physical or logical rowids must use UROWID, not the physical-only ROWID datatype.

An IOT is most promising when primary-key access dominates and rows in the index portion are reasonably compact. Common candidates include lookup tables, association tables, key-value structures, message or document metadata, and time-series tables whose composite primary key keeps related observations together. Primary-key range processing can also benefit from the ordered leaf entries.

The same design can be unsuitable when rows are wide, the primary key is large or frequently updated, full scans dominate, or applications query through many unrelated non-key columns. Each additional secondary index consumes storage and adds DML maintenance. A wide primary key also enlarges the logical rowids stored in those secondary indexes. Updating the primary key is especially significant because the row must occupy a new location in the B-tree.

Insert behavior matters as well. A steadily increasing key directs new entries toward the right edge of the B-tree and can concentrate concurrent activity. Inserts and updates can split leaf blocks, while deletes can leave reusable space. These effects do not prove that the B-tree has become logically unbalanced; Oracle maintains its search structure as data changes. Diagnose measured space, contention, and access-path problems instead of scheduling rebuilds solely because an object receives heavy DML.

Physical primary-key order also does not guarantee SQL result order. A query that requires a defined sequence must still include an ORDER BY clause. Oracle may use the IOT ordering to satisfy that requirement efficiently, but the relational result is unordered when the SQL statement does not request an order.

Compare the IOT with a heap design using representative data and statements. Examine execution plans, buffer gets, elapsed time, segment size, DML cost, concurrency, and caching. The structural advantage is the possibility of avoiding a separate heap-row lookup—not a promise of one physical I/O, a fixed percentage improvement, or better performance for every query.

The defining DDL is a normal table definition with a primary-key constraint followed by ORGANIZATION INDEX:

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

Oracle rejects an IOT definition that lacks a primary key. Key selection should therefore occur before the DDL is written. Prefer a reasonably narrow, stable key whose column order matches the important equality and range predicates. A key chosen only because it is unique may produce an ordering that does not help the workload.

After creation, verify the result rather than relying only on a client success message. The following query distinguishes the top-level IOT from related overflow storage that may be added later:

SELECT table_name,
       iot_type,
       iot_name,
       tablespace_name
FROM   user_tables
WHERE  table_name = 'CUSTOMER_ACCOUNTS_IOT'
   OR  iot_name = 'CUSTOMER_ACCOUNTS_IOT'
ORDER  BY iot_type, table_name;

Use USER_CONSTRAINTS to confirm the primary key and other constraints, and use USER_INDEXES to inspect the associated index metadata. A DBA examining other schemas should use the corresponding DBA_* views and include owner columns in the predicates and joins.

A secondary index provides an access path for a column that is not the primary key or a useful leading prefix of it. For example, an account IOT organized by account_id may need a secondary index on status_code if the application frequently finds accounts by status.

A heap-table secondary index normally stores a physical rowid. An IOT secondary index stores a logical rowid based on the primary key, commonly with a physical guess. If the guess is current, Oracle can probe the expected leaf block directly. If it is stale, the logical primary-key component still locates the row, although Oracle may perform additional work.

Moving an IOT does not inherently make every secondary index unusable, because the logical rowids remain valid. Do not rebuild all secondary indexes automatically. Gather statistics, inspect their status and PCT_DIRECT_ACCESS where relevant, and act on evidence. When only the physical guesses need repair, Oracle provides the focused operation:

ALTER INDEX customer_accounts_status_ix
    UPDATE BLOCK REFERENCES;

A full secondary-index rebuild remains appropriate when the index is unusable or when a separately approved storage or maintenance requirement justifies it. Bitmap secondary indexes on IOTs are a specialized case that use a mapping table. Include that additional component in design, movement, backup, and validation plans.

Wide IOT rows create wide leaf entries, reducing the number of entries that fit in a block. Oracle can divide a logical row into an index portion and an overflow portion. Every primary-key column remains in the primary-key B-tree. Selected non-key values can also remain there, while trailing non-key values are stored in a separate overflow segment.

Three clauses control this design:

• PCTTHRESHOLD limits the size of one row's index portion as a percentage of an index block. Its value is a per-row limit, not a promise to leave the same percentage of every block empty.
• INCLUDING names the desired final column in the index portion. All trailing non-primary-key columns are assigned to overflow, although the threshold can force the effective division earlier.
• OVERFLOW creates the overflow segment and can be followed by storage attributes such as a tablespace assignment.

CREATE TABLE document_metadata_iot (
    document_id   NUMBER,
    document_name VARCHAR2(200) NOT NULL,
    status_code   VARCHAR2(20),
    description   VARCHAR2(2000),
    CONSTRAINT document_metadata_iot_pk
        PRIMARY KEY (document_id)
)
ORGANIZATION INDEX
PCTTHRESHOLD 30
INCLUDING status_code
OVERFLOW;

In this example, document_id always remains in the index portion. The definition plans to retain document_name and status_code there, while description is stored in overflow. The value 30 is illustrative and must not be treated as a universal recommendation.

Overflow is a trade-off. Smaller leaf entries can improve density, and a query that needs only index-portion values may avoid the overflow segment. A statement that requests or updates overflowed columns must reach the linked overflow row piece. Choose the boundary using actual row sizes and important queries. Keep PCTTHRESHOLD distinct from PCTFREE: the former limits an individual row's index portion, while the latter reserves block space for updates and block management.

IOTs participate in many familiar Oracle capabilities, including constraints, triggers, secondary indexes, LOB storage, supported partitioning methods, compression, and eligible online maintenance. These features do not make an IOT identical to a heap table. Partition keys, overflow segments, LOBs, domain indexes, mapping tables, and online operations have IOT-specific rules that must be checked for the exact Oracle release and object definition.

Partitioning can divide a large IOT while preserving primary-key organization within each partition. The partitioning columns must be compatible with the IOT primary key, and overflow storage is equipartitioned with the primary-key index segments. Partition-level operations should be used for a partitioned IOT rather than assuming that every whole-table move is supported.

Compression can reduce repeated key or entry data when the data distribution makes compression worthwhile. Oracle Database 23ai includes Advanced LOW IOT Compression in addition to traditional prefix-oriented options. Compression changes CPU, storage, and access behavior, so measure it with representative data rather than assuming that a smaller segment is automatically faster.

On supported Exadata software, Oracle Database 23ai can use Smart Scan for eligible IOT access, including compressed IOTs. This platform-specific capability can benefit analytic scans, but it does not change the general design rule: verify the actual execution plan and measured result on the target system.

An IOT supports many familiar ALTER TABLE operations. Non-key columns can be added or dropped subject to the applicable datatype, dependency, and overflow rules. An existing IOT can receive an overflow segment with ADD OVERFLOW, and applicable physical attributes can be directed to that segment with the OVERFLOW keyword.

The primary key is different from an ordinary constraint because it defines the table's storage. It cannot simply be dropped while the IOT remains in place. Changing the primary-key columns or their order requires a physical redesign, such as creating a replacement IOT and migrating the data or using eligible online redefinition.

DROP TABLE removes the IOT rows, its table-owned indexes and triggers, and associated overflow or mapping components. Grants on the table are lost, and dependent objects can become invalid. Child foreign keys can block the drop unless the administrator deliberately uses CASCADE CONSTRAINTS. An eligible drop can enter the recycle bin, while PURGE bypasses that recovery path. These options are destructive DDL and require dependency analysis, approval, and a tested recovery plan.

For a straightforward rebuild of a nonpartitioned IOT, the primary method is:

ALTER TABLE customer_accounts_iot MOVE;

This command rebuilds the primary-key index segment because that segment is the table. ALTER INDEX ... REBUILD is not the command for rebuilding the IOT itself; it applies to a separate secondary index. A move can also change supported storage attributes or place the rebuilt structure in another tablespace.

An existing overflow segment is not automatically rebuilt merely because the primary structure moves. Include OVERFLOW when the operation intentionally rebuilds that component. LOB segments likewise have their own movement clauses and storage rules.

An eligible MOVE ONLINE permits ordinary DML during the move, but it still consumes resources, requires working space, and needs coordination locks. Restrictions can involve partitioned IOTs, domain indexes, parallel DML, direct-path inserts, LOBs, varrays, and object types. Verify the exact Oracle Database 23ai statement before execution.

DBMS_REDEFINITION is a separate option for eligible, broader online structural changes. It uses an interim table, synchronizes changes, handles dependent objects through an explicit workflow, and performs a controlled final switch. For planned offline replacement, Data Pump export, verification, drop, re-creation, import, and validation remains valid. The dump file, logs, captured DDL, grants, dependencies, and recovery procedure must all be tested before the original table is dropped.

Successful DDL completion is only the beginning of validation. Confirm row counts or application reconciliation totals, primary-key and constraint status, secondary-index status, overflow and LOB placement, grants, triggers, policies, and dependent-object validity. Run representative queries and DML through both primary-key and secondary-index paths.

After substantial movement, ensure that the database's statistics strategy has current information for the reorganized objects. Some environments rely on scheduled collection or pending statistics; others perform an approved manual collection:

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(
        ownname => USER,
        tabname => 'CUSTOMER_ACCOUNTS_IOT',
        cascade => TRUE
    );
END;
/

Compare the post-change execution plans and runtime measurements with the baseline that justified the work. Statistics gathering cannot replace data reconciliation, dependency checks, application testing, or recovery readiness.

1. Do the important queries use the complete primary key, a leading prefix, or a primary-key range?
2. Is the primary key narrow, stable, and ordered to match those predicates?
3. Are the index-portion rows compact enough for useful leaf-block density?
4. Which non-key paths require secondary indexes, and what are their DML and storage costs?
5. Which large or infrequently read values should move to overflow?
6. Do partitioning, compression, LOB, mapping-table, or platform features introduce additional rules?
7. Has the design been compared with a heap table using representative data, concurrency, and SQL?
8. Are alteration, reorganization, validation, and recovery procedures documented and tested?

An IOT succeeds when its primary-key organization matches a durable application access pattern. It should be selected because measurements demonstrate a useful physical design—not merely because Oracle supports the ORGANIZATION INDEX clause.

In the next module, you will learn about Oracle Database auditing capabilities.

Heap-organized table

A table whose rows are stored in a heap segment without being maintained in primary-key order.

Index-organized table (IOT)

A table whose rows are stored in the leaf blocks of its primary-key B-tree, with optional overflow storage for trailing non-key values.

Logical rowid

An IOT row identifier based on the primary key that can include a physical guess for faster access.

Overflow segment

The IOT storage segment containing non-key values placed outside the primary-key index portion of a logical row.

Use the quiz to test your understanding of IOT structure, primary-key design, overflow storage, secondary indexes, maintenance, and reorganization.