What Is a Index-Organized Tables (IOTs) in Oracle Database?
Stores table rows in the primary-key B-tree instead of a separate heap. It is a table storage organization, not simply another secondary-index type.
How the Index-Organized Tables (IOTs) Algorithm Works
Stores table rows in the primary-key B-tree instead of a separate heap. It is a table storage organization, not simply another secondary-index type.
When to Use Index-Organized Tables (IOTs)
Compact lookup tables primarily accessed by primary key where row/key locality matters.
How to Create a Oracle Database Index-Organized Tables (IOTs)
Review data quality, existing structures, feature support, build time, and deployment requirements before creating an index on a production table.
CREATE TABLE lookup_codes (code VARCHAR2(20) PRIMARY KEY, description VARCHAR2(200)) ORGANIZATION INDEX;Index-Organized Tables (IOTs) Query Example
This example demonstrates the matching query shape. Check the execution plan to confirm that the engine can use the index.
SELECT description FROM lookup_codes WHERE code = 'A104';Queries Supported by Index-Organized Tables (IOTs)
Compact lookup tables primarily accessed by primary key where row/key locality matters. A similar-looking predicate may not use the same access path; operators, expressions, key order, and data type all matter.
Read Performance, Storage, and Write Costs
Wide rows and non-primary access can be less efficient; overflow and secondary logical rowids affect layout. Indexes reduce some search work but require storage and add maintenance to writes.
Index-Organized Tables (IOTs) Limitations and Common Mistakes
IOT DDL, overflow, and secondary access differ from heap tables.
Do not assume that an index being present means the optimizer chose it or that the query became selective.
How to Measure and Tune Index-Organized Tables (IOTs)
Compare row width, primary-key access frequency, overflow, and secondary-index costs.
Compare representative plans and timings before and after, including concurrent writes and realistic data volumes.
Index-Organized Tables (IOTs) Questions
Will the database always use this index?
No. The optimizer weighs selectivity, estimated costs, statistics, cache state, and the amount of data returned.
What should I verify before production?
Confirm version support, query compatibility, write overhead, build/rollback procedures, and index behavior during backup and recovery.
Official Oracle Database Index Documentation
Check Oracle Database Concepts: Indexes and Index-Organized Tables for release-specific requirements and supported operators.