Searching in indexed organized table

Question

Does indexed organized table return result quickly only when searching condition has primary key? If not, how do they work?

Answer 1

Does indexed organized table return result quickly only when searching condition has primary key?

The short answer is yes but it's really the wrong question to ask. The correct is, "what kind of data is suited to an index-organized table?" And of course the answer is, the sort of data which we only ever want to look up by a primary key.

The classic IOT comprises a primary key (one or more columns) and at most one other column; having more non-key columns is allowed but it's not common. We can build additional - secondary indexes - on an IOT but their performance can be unpredictable. Find out more.

In practice this means we use Index-Organized Tables for things like reference data look-up tables, say of the structure (code, description). Data where we always want to query the table by the primary key, where we always want to get the description through the code. If we ever need to get the code through the description in will most likely be a full scan (unless we try our luck and build secondary indexes).