Index-Organized Tables, Secondary Indexes, Overflow Segments, and Key-Based Access

Learning outcomes

ServiceHub has a small reference table accessed almost entirely by a compound business key. The heap version needs a primary-key index plus a second hop to the heap row. An index-organized table (IOT) can store the row itself in primary-key order—but that changes rowid semantics, secondary-index mechanics, update costs, and wide-row design. This lesson compares the structures rather than calling either universally faster.

1. In an IOT, the primary-key index is the table storage

A heap table stores rows without primary-key ordering and uses a separate B-tree to enforce/serve the primary key. An index-organized table stores primary-key and nonkey column values in the primary-key B-tree itself. Oracle therefore requires an IOT primary key, and that primary key cannot be deferrable.

For exact primary-key lookup or ordered primary-key range scans, the IOT can avoid the second heap-block visit needed after a heap-table primary-key index lookup. The benefit is workload-specific: wide rows, random primary-key updates, secondary-index access and full scans can shift the balance.

CREATE TABLE servicehub_heap_route (    region_code  VARCHAR2(10) NOT NULL,    route_code   VARCHAR2(20) NOT NULL,    description  VARCHAR2(120),    payload       VARCHAR2(1000),    CONSTRAINT sh08_heap_route_pk      PRIMARY KEY(region_code, route_code));CREATE TABLE servicehub_iot_route (    region_code  VARCHAR2(10) NOT NULL,    route_code   VARCHAR2(20) NOT NULL,    description  VARCHAR2(120),    payload       VARCHAR2(1000),    CONSTRAINT sh08_iot_route_pk      PRIMARY KEY(region_code, route_code))ORGANIZATION INDEX;

2. Heap ROWID and IOT UROWID mean different things

A heap row’s ROWID is physical: it encodes the row location. An IOT cannot rely on a permanent physical row location because rows live in an ordered primary-key B-tree and can move as blocks split. Oracle therefore returns a logical UROWID based on the primary key.

SELECT region_code, route_code, ROWIDFROM servicehub_heap_routeFETCH FIRST 3 ROWS ONLY;SELECT region_code, route_code, ROWIDFROM servicehub_iot_routeFETCH FIRST 3 ROWS ONLY;

If an application stores rowids as durable business identifiers, it is already coupling itself to physical/storage semantics. For IOT rows, a ROWID-typed column cannot store the logical value; use UROWID where storage of logical rowids is genuinely required.

3. Secondary indexes need logical rowids and physical guesses

A secondary index on a heap table points directly to physical rowids. A secondary index on an IOT stores a logical rowid derived from the primary key. Oracle can also retain a physical guess: the block location where the IOT row was found when the secondary entry was created or maintained. If the guess is still correct, Oracle avoids a full primary-key probe; if stale, Oracle follows the logical primary key and the result remains correct.

CREATE INDEX sh08_iot_desc_ixON servicehub_iot_route(description);SELECT index_name, index_type, pct_direct_accessFROM user_indexesWHERE table_name = 'SERVICEHUB_IOT_ROUTE'ORDER BY index_name;

PCT_DIRECT_ACCESS can describe how often physical guesses are valid when relevant statistics exist. A stale guess is a performance issue, not a correctness failure.

4. Overflow segments keep wide trailing columns away from the primary index portion

Very wide IOT rows can make the primary-key B-tree bulky. The INCLUDING clause defines the last column kept in the index portion; following columns can be stored in an overflow segment. This creates a tradeoff: primary-key-only or narrow queries touch compact index entries, while queries needing overflow columns require another access.

CREATE TABLE servicehub_iot_asset (    asset_code        VARCHAR2(30) NOT NULL,    region_code       VARCHAR2(10) NOT NULL,    asset_name        VARCHAR2(100) NOT NULL,    short_status      VARCHAR2(20),    maintenance_notes VARCHAR2(2000),    CONSTRAINT sh08_iot_asset_pk PRIMARY KEY(asset_code))ORGANIZATION INDEXINCLUDING short_statusOVERFLOW;

Do not put every wide table into an IOT and then add overflow mechanically. Measure which columns the key-centric workload reads and how often the overflow hop occurs.

5. Deliberately wrong approach: use a physical ROWID assumption on an IOT

A maintenance script stores an IOT’s ROWID in a ROWID column and treats it as a permanent locator. The design is invalid because IOT rowids are logical UROWIDs. The safer repair is to carry the primary key itself as the durable identifier; use UROWID only for technical cases that truly require logical rowids.

6. Hands-on lab: compare primary-key and secondary access

DROP TABLE servicehub_heap_route IF EXISTS PURGE;DROP TABLE servicehub_iot_route IF EXISTS PURGE;CREATE TABLE servicehub_heap_route (    region_code  VARCHAR2(10) NOT NULL,    route_code   VARCHAR2(20) NOT NULL,    description  VARCHAR2(120),    payload       VARCHAR2(400),    CONSTRAINT sh08_heap_route_pk      PRIMARY KEY(region_code, route_code));CREATE TABLE servicehub_iot_route (    region_code  VARCHAR2(10) NOT NULL,    route_code   VARCHAR2(20) NOT NULL,    description  VARCHAR2(120),    payload       VARCHAR2(400),    CONSTRAINT sh08_iot_route_pk      PRIMARY KEY(region_code, route_code))ORGANIZATION INDEX;INSERT INTO servicehub_heap_routeSELECT    CASE MOD(LEVEL,4) WHEN 0 THEN 'N' WHEN 1 THEN 'S' WHEN 2 THEN 'E' ELSE 'W' END,    'R-' || LPAD(LEVEL,6,'0'),    'Route ' || LEVEL,    RPAD('p', 120, 'p')FROM dualCONNECT BY LEVEL <= 5000;INSERT INTO servicehub_iot_routeSELECT * FROM servicehub_heap_route;CREATE INDEX sh08_heap_desc_ix ON servicehub_heap_route(description);CREATE INDEX sh08_iot_desc_ix  ON servicehub_iot_route(description);COMMIT;BEGIN    DBMS_STATS.GATHER_TABLE_STATS(USER, 'SERVICEHUB_HEAP_ROUTE', cascade => TRUE);    DBMS_STATS.GATHER_TABLE_STATS(USER, 'SERVICEHUB_IOT_ROUTE',  cascade => TRUE);END;/

EXPLAIN PLAN SET STATEMENT_ID = 'SH08L4_HEAP'FORSELECT region_code, route_code, descriptionFROM servicehub_heap_routeWHERE region_code = 'N'  AND route_code = 'R-004000';SELECT *FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'SH08L4_HEAP', 'BASIC +PREDICATE'));EXPLAIN PLAN SET STATEMENT_ID = 'SH08L4_IOT'FORSELECT region_code, route_code, descriptionFROM servicehub_iot_routeWHERE region_code = 'N'  AND route_code = 'R-004000';SELECT *FROM TABLE(DBMS_XPLAN.DISPLAY(NULL, 'SH08L4_IOT', 'BASIC +PREDICATE'));

DROP TABLE servicehub_heap_route PURGE;DROP TABLE servicehub_iot_route PURGE;

The heap plan can require a primary-key index lookup followed by table access; the IOT primary-key structure already contains the row. Exact logical I/O must be measured on executed statements in a representative environment before claiming a performance win.

7. Production judgment

IOTs fit narrow, key-centric tables with stable primary keys and access patterns that benefit from primary-key ordering. Heap tables remain a strong general default for mixed access, wide rows and heavy secondary-index workloads. Secondary indexes on IOTs are correct even when physical guesses become stale, but the additional lookup path should be measured. Overflow segments are a space/access design tool, not a blanket recommendation.

No paid option, restart, or special COMPATIBLE change is required for this Free lab. Lesson 5 broadens the index decision beyond B-tree/bitmap/IOT structures to application-specific domain indexes, Spatial, Oracle Text, and AI vector indexes.

Authoritative references

• CREATE TABLE — index-organized tables — IOT primary-key, rowid and storage syntax/restrictions
• Indexes and Index-Organized Tables — IOT storage, secondary indexes, logical rowids and physical guesses
• Data Types — ROWID and UROWID semantics
• ALL_INDEXES — PCT_DIRECT_ACCESS and IOT-related index metadata