Allocate Overflow Space for Oracle Index-Organized Tables

An index-organized table (IOT) stores its rows in the B-tree defined by the table's primary key. A leaf entry can therefore contain the primary-key values and the non-key values for a complete row. This organization is useful when important queries access rows by the primary key, but wide rows also produce wide index entries. As entries grow, fewer of them fit in each leaf block, which can increase the number of leaf blocks required by the B-tree.

Oracle can divide a logical IOT row into an index portion and an overflow portion. The index portion remains in the primary-key B-tree. The overflow portion is stored in a separate segment managed as part of the IOT. Applications still query and modify one table row; they do not join the index portion to a second user-visible table.

The clauses PCTTHRESHOLD, INCLUDING, and OVERFLOW control this physical division. They should be chosen from the workload rather than treated as automatic performance settings. Smaller index entries can improve leaf-block density, but a statement that needs overflowed columns may also have to access the overflow segment.

When an IOT uses row overflow, Oracle divides each applicable row into two physical pieces.

Index portion

The index portion is stored in the IOT's primary-key index segment. It always contains every primary-key column. It can also contain selected non-key columns that are useful to common primary-key queries. When a row has an overflow portion, the index entry includes an internal physical rowid that points to the corresponding overflow row piece.

That internal pointer must not be confused with the logical rowid used to identify an IOT row. An IOT row does not have a permanent physical heap address because B-tree entries can move as the index changes. Oracle therefore exposes logical rowids for IOT rows, while it can use a physical pointer internally to connect the two pieces of an overflowed row.

Overflow portion

The overflow portion contains the remaining non-key columns and is stored in a separate overflow segment. Oracle accesses this row piece when a query or data manipulation statement requires one of those columns. The overflow segment is not an independent application table; it is a storage component of the IOT.

This arrangement allows a primary-key lookup that requests only columns in the index portion to avoid reading the overflow portion. If the same lookup requests an overflowed column, Oracle follows the internal pointer to the overflow row piece. The actual cost depends on the execution plan, caching, row width, block size, and storage configuration.

The PCTTHRESHOLD clause limits the size of one row's index portion as a percentage of an index block:

PCTTHRESHOLD integer

The integer must be from 1 through 50. If the clause is omitted, the default value is 50. The threshold must still be large enough to hold the entire primary key because Oracle never moves primary-key columns to the overflow portion.

When the index portion of a row would exceed the threshold, Oracle stores trailing columns in the overflow segment, beginning with the column that causes the threshold to be crossed. The setting is defined for the IOT as a whole; it cannot be specified separately for individual IOT partitions.

PCTTHRESHOLD 50 does not reserve half of every leaf block as unused space, nor does it guarantee that at least two rows will fit in each leaf block. It limits how much of a block one row's index portion may occupy. Actual leaf-block occupancy also depends on stored values and block overhead.

The INCLUDING clause identifies the last column at the planned boundary between the index and overflow portions:

INCLUDING column_name

The named column can be the final primary-key column or a non-primary-key column. All primary-key columns remain in the index portion regardless of their position in the table definition. Non-key columns following the named column are stored in the overflow segment, so column order matters when this clause is used.

For example, if a table defines customer_id, order_date, status, and order_notes in that order, then INCLUDING status establishes status as the planned final column in the index portion. The trailing order_notes value belongs in overflow.

INCLUDING expresses a desired logical boundary, but it cannot force Oracle to keep an index portion that exceeds PCTTHRESHOLD. If retaining values through the named column would cross the specified or default threshold, Oracle divides the row according to the threshold instead. In other words, the effective split can occur earlier than the requested INCLUDING boundary.

The OVERFLOW clause instructs Oracle to create the row overflow segment. The related clauses appear after ORGANIZATION INDEX in the IOT definition:

ORGANIZATION INDEX
PCTTHRESHOLD integer
INCLUDING column_name
OVERFLOW;

The tablespace is optional. If no tablespace is named, Oracle uses the applicable default placement rules. To place the overflow segment explicitly, add a TABLESPACE clause after OVERFLOW:

OVERFLOW TABLESPACE iot_overflow;

In this fragment, iot_overflow is only an example name. The tablespace must already exist, be online and writable, and be available to the table owner through a quota or an appropriate system privilege. Storage attributes written after OVERFLOW apply to the overflow segment, not to the primary-key index segment.

Oracle also performs an important validation during CREATE TABLE. It uses the declared maximum sizes of the columns to estimate the largest possible row. If that definition can require overflow storage but the statement does not specify OVERFLOW, Oracle raises an error and does not create the table. Oracle does not simply wait for a future oversized insert to discover that the required overflow segment is missing.

The following statement creates an IOT for a coin inventory. Frequently requested identifying attributes are placed before and through the denomination boundary. Wider or less frequently requested details follow that boundary and can be stored in overflow:

CREATE TABLE coin_inventory_iot (
    coin_id         NUMBER,
    coin_date       DATE NOT NULL,
    mint_mark       VARCHAR2(20),
    series_name     VARCHAR2(40),
    denomination    VARCHAR2(20),
    variety_name    VARCHAR2(40),
    grade_code      VARCHAR2(12),
    owner_client_id NUMBER,
    country_name    VARCHAR2(60),
    notes           VARCHAR2(1000),
    CONSTRAINT coin_inventory_iot_pk
        PRIMARY KEY (coin_id)
)
ORGANIZATION INDEX
PCTTHRESHOLD 30
INCLUDING denomination
OVERFLOW;

The primary-key column coin_id always remains in the index portion. The definition plans to retain coin_date, mint_mark, series_name, and denomination there as well. Because denomination is the INCLUDING column, the following non-key columns—variety_name, grade_code, owner_client_id, country_name, and notes—are assigned to the overflow portion.

The value 30 is an example, not a general recommendation. If the index portion through denomination would occupy more than 30 percent of an index block, the threshold determines the actual division and Oracle can move trailing non-key values to overflow before that planned boundary.

Notice that the semicolon appears only after the final OVERFLOW clause. A semicolon immediately after the closing parenthesis would terminate CREATE TABLE too early and leave ORGANIZATION INDEX as invalid standalone text.

If the database administrator has prepared a separate tablespace, the final clause can instead be written as:

OVERFLOW TABLESPACE iot_overflow;

After creating the table, use Oracle data dictionary views to verify the logical IOT and its physical components. Unquoted Oracle object names are stored in uppercase in these views.

First, inspect the top-level table and overflow-related table metadata:

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

The IOT_TYPE and IOT_NAME columns distinguish the top-level IOT from related storage objects. Exact system-generated names and displayed rows can vary with the Oracle release and object configuration.

Next, inspect the primary-key index metadata:

SELECT index_name,
       index_type,
       include_column,
       pct_threshold,
       tablespace_name
FROM   user_indexes
WHERE  table_name = 'COIN_INVENTORY_IOT';

INCLUDE_COLUMN identifies a column position rather than displaying the boundary column's name. If the name is needed, compare that position with USER_TAB_COLUMNS.COLUMN_ID for COIN_INVENTORY_IOT.

The allocated primary-key index and overflow segments can be located with the following query:

SELECT segment_name,
       segment_type,
       tablespace_name,
       bytes
FROM   user_segments
WHERE  segment_name IN (
           SELECT index_name
           FROM   user_indexes
           WHERE  table_name = 'COIN_INVENTORY_IOT'
           UNION
           SELECT table_name
           FROM   user_tables
           WHERE  iot_name = 'COIN_INVENTORY_IOT'
       )
ORDER  BY segment_type, segment_name;

If deferred segment creation is enabled, some allocation information may not appear until the table contains data. A missing segment before the first insert does not by itself mean that the IOT definition failed.

An overflow design should reflect how the table is actually used. Keep the primary key and non-key columns required by important, frequent primary-key lookups in the index portion when their combined size is reasonable. Wide, infrequently read trailing columns are stronger candidates for overflow.

Moving data to overflow can reduce the size of the B-tree entries and allow more entries to fit in a leaf block. A lookup that needs only columns retained in the index portion may be satisfied without reading the overflow row piece. However, statements that request or update overflowed columns may require an additional segment access. Setting the threshold too low can therefore move commonly used data out of the primary-key B-tree, while setting it too high can leave entries so wide that leaf-block density suffers.

Evaluate candidate definitions with representative row sizes and application statements. Compare execution plans, buffer gets, elapsed time, segment size, and DML behavior. Caching, data distribution, concurrency, database block size, and the selected columns can all change the result. There is no threshold that guarantees a fixed number of I/O operations or a universal performance improvement.

PCTTHRESHOLD must also be distinguished from PCTFREE. PCTTHRESHOLD limits one IOT row's index portion as a percentage of an index block. PCTFREE reserves block space for updates and block-management needs. The two settings control different storage concerns.

For IOTs containing LOB columns, apply the separate Oracle LOB storage rules rather than assuming that PCTTHRESHOLD alone determines where all LOB data is stored. For a partitioned IOT, Oracle equipartitions overflow data segments with the corresponding primary-key index segments.

In the next lesson, you will learn how to delete and modify an index-organized table.