Create an Index-Organized Table in Oracle Database

An index-organized table (IOT) stores its rows in a B-tree defined by the table's primary key. The ORGANIZATION INDEX clause selects this storage model. A conventional table uses ORGANIZATION HEAP, which is Oracle Database's default and normally does not need to be written explicitly.

Applications use the same relational SQL operations with either organization. The difference is physical storage. A heap-organized table places rows wherever suitable space is available and normally uses a separate primary-key index. In an IOT, the primary-key B-tree is the table's primary storage structure, and its leaf entries contain the key and the non-key values stored in the index portion of the row.

Creating an IOT is therefore more than adding a general performance option to a table. The primary key determines row placement, the supported access paths, and the structure Oracle must maintain as rows are inserted, updated, and deleted. Before selecting an IOT, the table designer should already have established that primary-key or leading-key-prefix access matches the workload.

The essential syntax consists of a normal CREATE TABLE definition, an explicitly defined primary-key constraint, and the ORGANIZATION INDEX clause:

CREATE TABLE table_name (
    column_definitions,
    CONSTRAINT constraint_name PRIMARY KEY (key_columns)
)
ORGANIZATION INDEX;

The organization clause follows the closing parenthesis of the table definition. Oracle uses the primary-key columns to build the B-tree that stores the rows. Because an IOT cannot exist without that structure, its primary-key constraint is mandatory and cannot be declared DEFERRABLE.

Although ORGANIZATION HEAP is valid syntax, it is usually omitted because heap organization is the default:

CREATE TABLE example_heap (
    example_id   NUMBER PRIMARY KEY,
    example_name VARCHAR2(100) NOT NULL
)
ORGANIZATION HEAP;

The explicit heap clause can document intent, but it does not change the normal default behavior. The remainder of this lesson uses ORGANIZATION INDEX.

The primary key has a larger physical role in an IOT than it does in a heap-organized table. Its columns determine the order of every row in the primary storage structure, so key selection and column order should reflect the access path the application actually needs. A key chosen only because it is unique may not provide a useful physical order.

For a composite key, Oracle can use the complete key or a valid leading prefix to navigate the B-tree efficiently. If the key is defined as (store_id, quarter_number, month_number), predicates beginning with store_id align with that order. A predicate on month_number alone does not use the leading portion of this primary key and may require a different access path, such as a secondary index.

Prefer primary-key columns that are stable and reasonably compact. Updating a primary-key value changes the row's logical position and can require Oracle to move the entry within the B-tree. A wide composite key also increases the size of logical rowids stored in secondary indexes. These considerations do not prohibit composite keys; they make deliberate key design important before the DDL is executed.

Confirm the following before creating the table:

• The key uniquely identifies every intended row.
• The most important queries use the complete key or one of its leading prefixes.
• Key values are unlikely to require frequent updates.
• The selected column order matches the required equality and range predicates.
• Any future partitioning key can be included among the primary-key columns.

The following statement creates an IOT for monthly sales figures. The composite primary key uniquely identifies one monthly value for a store:

CREATE TABLE sales_figures_iot (
    store_id       NUMBER,
    quarter_number NUMBER(1),
    month_number   NUMBER(2),
    amount         NUMBER(12,2) NOT NULL,
    CONSTRAINT sales_figures_quarter_ck
        CHECK (quarter_number BETWEEN 1 AND 4),
    CONSTRAINT sales_figures_month_ck
        CHECK (month_number BETWEEN 1 AND 12),
    CONSTRAINT sales_figures_iot_pk
        PRIMARY KEY (store_id, quarter_number, month_number)
)
ORGANIZATION INDEX;

Oracle organizes the B-tree first by store_id, then by quarter_number, and finally by month_number. This ordering supports an exact lookup using all three values. It also supports access through leading key prefixes, such as every recorded month for one store or every month in one store and quarter.

The check constraints reject invalid quarter and month numbers, while the primary-key constraint implicitly makes its three columns non-null. The amount column is a non-key value stored with the primary-key entry unless later storage options place it in an overflow segment.

Insert a small representative data set:

INSERT INTO sales_figures_iot
    (store_id, quarter_number, month_number, amount)
VALUES
    (101, 1, 1, 184250.75);

INSERT INTO sales_figures_iot
    (store_id, quarter_number, month_number, amount)
VALUES
    (101, 1, 2, 192840.50);

INSERT INTO sales_figures_iot
    (store_id, quarter_number, month_number, amount)
VALUES
    (101, 1, 3, 207115.25);

COMMIT;

A complete-key lookup can retrieve one row:

SELECT amount
FROM   sales_figures_iot
WHERE  store_id = 101
AND    quarter_number = 1
AND    month_number = 2;

A leading-prefix query can retrieve a range of entries for a store and quarter. Specify ORDER BY whenever the result must be returned in a defined order; physical primary-key organization does not replace SQL ordering semantics:

SELECT month_number,
       amount
FROM   sales_figures_iot
WHERE  store_id = 101
AND    quarter_number = 1
ORDER  BY month_number;

For a heap-organized table, a physical rowid identifies the data block and row location. An IOT row can move as inserts and updates change the primary-key B-tree, so it does not have a permanent physical heap address. Oracle identifies the logical IOT row through its primary-key values.

The ROWID pseudocolumn of an IOT returns a logical rowid rather than the physical rowid used by a heap table. If an application must store an IOT logical rowid in a column, that column must use the UROWID datatype; a column declared as ROWID stores only physical rowids. Most applications should continue to use the declared primary key instead of persisting row identifiers.

The mandatory primary key also explains why a deferrable primary-key constraint is unsuitable. Oracle cannot postpone establishing the key that defines the table's storage structure. Choose primary-key columns that are stable, appropriately narrow, and aligned with the access pattern before creating the table.

Do not stop at a client message reporting that the table was created. Query the data dictionary to confirm the organization and constraints. Unquoted Oracle identifiers are stored in uppercase, so use the uppercase table name in dictionary predicates.

Inspect the table organization:

SELECT table_name,
       iot_type,
       iot_name
FROM   user_tables
WHERE  table_name = 'SALES_FIGURES_IOT';

The top-level object is identified as an IOT through its dictionary metadata. The precise display values and related rows can vary with the options used to create the object, such as overflow or mapping-table features.

Verify the primary key and check constraints:

SELECT constraint_name,
       constraint_type,
       status,
       index_name
FROM   user_constraints
WHERE  table_name = 'SALES_FIGURES_IOT'
ORDER  BY constraint_name;

Finally, inspect the associated index metadata:

SELECT index_name,
       index_type,
       uniqueness,
       status
FROM   user_indexes
WHERE  table_name = 'SALES_FIGURES_IOT'
ORDER  BY index_name;

These checks confirm that the intended table and constraints exist in the current schema. They are more reliable than treating copied SQL-client output as proof of the deployed definition.

The following statement is intentionally invalid because it requests index organization without defining a primary key:

CREATE TABLE locations_iot (
    location_id    NUMBER(6) NOT NULL,
    street_address VARCHAR2(120),
    postal_code    VARCHAR2(20),
    city           VARCHAR2(80) NOT NULL,
    country_code   CHAR(2)
)
ORGANIZATION INDEX;

Oracle rejects the statement because no primary-key constraint exists:

ORA-25175: no PRIMARY KEY constraint found

Correct the original CREATE TABLE definition by adding a named primary-key constraint inside it. The failed table does not exist, so this is not a case for altering the failed object afterward. A basic corrected constraint could use location_id:

CONSTRAINT locations_iot_pk
    PRIMARY KEY (location_id)

If the table will be partitioned, however, its partitioning key must also be included among the primary-key columns. The next example accounts for that additional rule.

Oracle supports single-level range, list, and hash partitioning for an IOT. The partitioning key must be composed of columns from the IOT's primary key. The following example list-partitions locations by country_code, which is also part of the composite primary key:

CREATE TABLE locations_iot (
    location_id    NUMBER(6),
    country_code   CHAR(2),
    city           VARCHAR2(80) NOT NULL,
    street_address VARCHAR2(120),
    postal_code    VARCHAR2(20),
    CONSTRAINT locations_iot_pk
        PRIMARY KEY (location_id, country_code)
)
ORGANIZATION INDEX
PARTITION BY LIST (country_code) (
    PARTITION locations_us
        VALUES ('US'),
    PARTITION locations_ca
        VALUES ('CA'),
    PARTITION locations_other
        VALUES (DEFAULT)
);

This statement creates one primary-key index segment for each table partition. Rows for the United States and Canada have named partitions, while all other country codes enter the default partition. The fallback partition avoids an insertion failure when a valid but previously unlisted country code arrives.

For an IOT, list partitioning uses one partitioning-key column. Range or hash partitioning can be selected when those methods better represent the data and access pattern. Regardless of method, the designer must ensure that the partitioning columns satisfy the primary-key rule.

Verify the partition definitions:

SELECT table_name,
       partition_name,
       partition_position,
       high_value
FROM   user_tab_partitions
WHERE  table_name = 'LOCATIONS_IOT'
ORDER  BY partition_position;

The HIGH_VALUE representation is dictionary metadata and may require additional handling in applications that need to process it programmatically. For this lesson, the query is intended as an administrative verification of the created partitions.

An IOT does not support composite partitioning clauses. A definition that attempts to combine a top-level range partition with list subpartitions is intentionally invalid:

-- Intentionally invalid for an IOT
ORGANIZATION INDEX
PARTITION BY RANGE (location_id)
SUBPARTITION BY LIST (country_code)

Oracle can reject this organization with:

ORA-25198: partitioning method is not supported for index-organized table

The corrective action is to select a supported single-level range, list, or hash method and verify the rules documented for the installed Oracle release. System partitioning and automatic list partitioning are also not supported for an IOT.

ORA-25198 is specifically about an unsupported IOT partitioning method. It is not an error about FREELIST GROUPS, manual freelists, or Automatic Segment Space Management.

A related but distinct error can occur when the selected partitioning key is not included in the IOT primary key:

ORA-25199: partitioning key of an index-organized table must be a subset of the primary key

Resolve that error by redesigning the primary key or selecting an eligible partitioning key. Do not add an unrelated column to a primary key only to silence an error without considering how the wider key affects the IOT and its access paths.

The examples in this lesson keep complete rows in the primary-key structure. For wider rows, Oracle provides PCTTHRESHOLD, INCLUDING column_name, and OVERFLOW options. These clauses can divide a row between the primary-key index portion and an overflow segment.

Primary-key columns always remain in the index portion. Selected trailing non-key columns can be moved to overflow storage to preserve leaf-block density, but a query that requests those columns must access the overflow segment. The next lesson explains how to create IOTs with larger rows and how to choose an appropriate boundary.

Before moving the definition into a production change, complete these checks:

1. Confirm that the object name does not conflict with an existing table, view, synonym, or other schema object.
2. Validate every datatype, nullability rule, check constraint, and primary-key column against the application data model.
3. Confirm that the primary key is nondeferrable and ordered for the dominant access path.
4. If the IOT is partitioned, verify that the method is supported and that every partitioning-key column belongs to the primary key.
5. Run the DDL in a development or test schema before applying it to production.
6. Insert representative rows and test complete-key, leading-prefix, and required secondary-key queries.
7. Query the data dictionary to verify the created organization, constraints, indexes, and partitions.

Creation success proves that Oracle accepted the syntax; it does not prove that the physical design is best for the workload. Use representative data, current optimizer statistics, and actual execution plans when validating the design.

Practice creating an IOT, confirming its required primary key, and verifying its organization through the data dictionary: