Reorganizing an Index-Organized Table in Oracle Database 23ai

An index-organized table (IOT) stores its rows in the leaf blocks of its primary-key B-tree. Reorganizing an IOT rebuilds or replaces that storage structure when measured space use, changed storage requirements, or a planned design change justifies the work. In Oracle Database 23ai, a direct ALTER TABLE ... MOVE is normally the starting point. An eligible online move, online redefinition, and an offline Data Pump workflow address different availability and redesign requirements.

Reorganization is not routine housekeeping that should be performed only because a table is old or has received substantial DML. It consumes CPU, I/O, temporary working space, undo, and redo according to the database configuration. It can also require locks or a maintenance window. Before changing a production IOT, establish the problem to be solved, capture a baseline, verify the recovery plan, and test the complete operation with representative data and application activity.

Oracle maintains the search balance of a B-tree as rows are inserted and deleted. Deleting keys from one range while inserting keys into another does not make the tree logically unbalanced. However, deletes and updates can leave reusable space in leaf blocks, and a segment can retain allocated extents after a large reduction in data. Increasing primary keys can also concentrate inserts at the right edge of the B-tree, but this is an insertion pattern rather than proof that the tree requires rebuilding.

A reorganization may be appropriate when evidence identifies a useful objective, such as:

• reclaiming allocated space after a substantial, permanent data reduction;
• moving the IOT to a different tablespace;
• changing compression or other supported physical attributes;
• revising the overflow placement or the division between the index and overflow portions;
• replacing a storage definition as part of a planned redesign;
• refreshing an inefficient physical layout after representative testing shows a measurable benefit.

Compare representative execution plans, buffer activity, elapsed time, segment allocation, data volume, and maintenance requirements before and after a non-production test. No single fragmentation percentage proves that every IOT should be moved, and a rebuild does not guarantee a performance improvement.

Start by inventorying the complete object rather than treating the primary-key B-tree as an isolated index. Determine whether the IOT has an overflow segment, partitions, LOB columns, domain indexes, or secondary indexes. Capture its DDL, constraints, grants, triggers, policies, dependent objects, optimizer statistics, and representative execution plans.

1. Confirm that the object is an IOT and identify its top-level and overflow components.
2. Estimate the space required to build the replacement segment and confirm the target tablespace quota and free space.
3. Decide whether DML must remain available and check the table against the restrictions for the proposed online operation.
4. Review the effect on overflow storage, LOB segments, secondary indexes, and application dependencies.
5. Document backup, recovery, rollback, and maintenance-window procedures.
6. Test the statements and validation queries in a non-production database that represents the production definition and workload.

The following query identifies the top-level IOT and an associated overflow table exposed through the current user's dictionary view. Unquoted Oracle identifiers are stored in uppercase.

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

Inspect the indexes separately. After appropriate statistics have been collected, PCT_DIRECT_ACCESS can help assess the percentage of logical-rowid physical guesses that still point directly to the expected IOT block.

SELECT index_name,
       index_type,
       status,
       tablespace_name,
       pct_direct_access
FROM   user_indexes
WHERE  table_name = 'SALES_ORDER_IOT'
ORDER  BY index_name;

These values provide evidence for planning; they are not automatic rebuild thresholds.

An IOT is moved with ALTER TABLE, not by rebuilding its primary storage with ALTER INDEX. The primary-key B-tree is the table's storage structure. The simplest move rebuilds that structure using the applicable storage settings:

ALTER TABLE sales_order_iot MOVE;

A move can also place the rebuilt primary-key index segment in another tablespace:

ALTER TABLE sales_order_iot
    MOVE TABLESPACE iot_data;

Here, iot_data is an example existing permanent tablespace. The table owner must have the required quota or privilege. A move without ONLINE requires a maintenance plan because access can be restricted while Oracle builds and activates the replacement segment.

For an eligible table and operation, the online form permits ordinary DML while Oracle moves the IOT:

ALTER TABLE sales_order_iot MOVE ONLINE;

ONLINE does not mean that the operation is free of production impact. The move still consumes resources, needs working space, generates database changes, and requires coordination locks for parts of the operation. Monitor the system and schedule the work according to the application's service requirements.

Do not assume that every IOT can be moved online. Oracle 23ai restrictions must be checked against the exact table definition and statement. Restrictions can involve partitioned IOTs, domain indexes, concurrent parallel DML or direct-path inserts, and IOTs containing LOB, VARRAY, Oracle-supplied type, or user-defined object type columns. Some clauses also cannot be combined with ONLINE in the same move statement. When these restrictions conflict with the required change, use a tested offline move or evaluate online redefinition.

Move the Overflow Segment Deliberately

Moving the primary-key index segment does not automatically rebuild an existing overflow segment merely because both belong to the same logical table. Include OVERFLOW when the plan intentionally rebuilds and relocates that segment:

ALTER TABLE sales_order_iot
    MOVE TABLESPACE iot_data
    OVERFLOW TABLESPACE iot_overflow;

The first tablespace applies to the rebuilt IOT primary-key index segment. iot_overflow is the example destination for the overflow segment. Oracle can also rebuild overflow storage when a supported move changes PCTTHRESHOLD or the INCLUDING boundary. Confirm the exact clause combination, space requirement, and release restrictions before execution.

Handle LOB Storage Separately

An IOT move does not implicitly rebuild or relocate every out-of-line LOB data and index segment. A supported move addresses LOB storage with its own clause. The following fragment illustrates the intent:

ALTER TABLE document_iot
    MOVE LOB (document_text)
    STORE AS (TABLESPACE iot_lob_data);

Verify the complete statement against the table's actual LOB definition and the Oracle 23ai SecureFiles and LOB rules. In particular, do not assume that this form can be combined with ONLINE for every IOT.

Check Secondary-Index Logical Rowids

An IOT row has no permanent heap-table physical rowid. A secondary index identifies the row with a logical rowid based on the IOT primary key. The logical rowid remains valid when a row moves, so an ordinary IOT move does not inherently make every secondary index unusable.

A logical rowid can also contain a physical guess that points toward the leaf block where the row was located. After a move, the guess can become stale even though the secondary index remains usable. Oracle can fall back to the primary-key component, but access can be less direct. Gather statistics and inspect the secondary indexes instead of rebuilding all of them automatically.

When the goal is specifically to refresh the physical guesses, use the focused operation:

ALTER INDEX sales_order_iot_status_ix
    UPDATE BLOCK REFERENCES;

If testing independently justifies a full secondary-index rebuild, it can be performed separately:

ALTER INDEX sales_order_iot_status_ix REBUILD ONLINE;

For a partitioned IOT, moving a partition changes the address portion of logical rowids and can leave physical guesses stale. Evaluate those indexes as part of the partition-maintenance plan.

DBMS_REDEFINITION is a different mechanism from ALTER TABLE ... MOVE ONLINE. It can keep an eligible table available while Oracle copies data into an interim table and synchronizes changes before a controlled final switch. It is useful when the required redesign is more extensive than a straightforward move and application downtime must be minimized.

A planned online redefinition normally includes these stages:

1. Use DBMS_REDEFINITION.CAN_REDEF_TABLE to check eligibility.
2. Create an interim table with the desired IOT and storage definition.
3. Start redefinition using the supported primary-key method.
4. Copy dependent objects and synchronize accumulated changes as required.
5. Finish the redefinition during a controlled final switch.
6. Review errors, validate the table and its dependents, and remove unneeded interim objects.

An IOT requires a primary key, so primary-key-based redefinition is the natural method to evaluate. The package has eligibility rules, privileges, space requirements, dependent-object handling, and rollback considerations that cannot be represented safely by a single generic script. Build and test a complete runbook using the Oracle 23ai DBMS_REDEFINITION documentation.

Export, drop, re-create, and import remains a valid planned offline method, particularly when a maintenance window is available and the replacement definition changes several storage characteristics. It is an alternative—not the required process for every IOT reorganization.

Data Pump commands are command-line utilities or API-driven jobs, not SQL statements that can be pasted into SQL*Plus without environment-specific parameters. The Oracle directory object, dump and log filenames, credentials, schemas, object filters, and any remapping options must be planned and tested.

This method creates an availability gap after the original table is dropped and before the replacement has been loaded and validated. A successful row import is not sufficient if a grant, trigger, policy, constraint, or other dependent object is missing. Retain the export log, import log, captured DDL, reconciliation results, and recovery instructions with the maintenance record.

Validation must demonstrate both data correctness and operational readiness. Compare row counts or application-specific control totals, confirm primary-key and constraint status, inspect secondary indexes and their physical guesses when relevant, and verify overflow and LOB placement. Check grants, triggers, policies, and other dependent objects whenever the chosen method can affect them. Finally, run representative application queries and DML and compare their plans and behavior with the captured baseline.

Refresh optimizer statistics according to the site's statistics-management policy. A direct example is:

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

Some environments use scheduled statistics collection, pending statistics, or release-specific validation procedures instead. Statistics gathering helps the optimizer describe the new structure, but it does not replace data reconciliation, dependency checks, recovery readiness, or application testing.

The correct reorganization method follows from the required change and availability target: use a direct move for a straightforward rebuild, an eligible online move when ordinary DML must continue, online redefinition for a supported broader redesign, or the tested Data Pump workflow for planned offline replacement. In the next lesson, you will review the module's index-organized table concepts.