索引组织表是否仅含主键查询时才高效？其工作机制如何？

Great question! Let’s break this down clearly—Indexed Organized Tables (IOTs) don’t only deliver fast results when your query includes the primary key, though that’s absolutely their most optimized use case. Let’s dive into how they work and when they shine beyond direct primary key lookups.

First, let’s contrast them with regular heap tables to set the stage:

• A standard heap table stores data in an unordered heap, with a separate primary key B-tree index that points to the row’s physical ROWID in the heap. When you query via the primary key, the database jumps to the index entry, fetches the ROWID, then goes back to the heap to get the actual data (this is called a "table lookup" or "bookmark lookup").
• An IOT flips this model: the table data itself is the primary key B-tree. There’s no separate heap storage—all column values for a row are stored directly in the leaf nodes of the primary key B-tree. The primary key defines the order of the B-tree, so rows are always sorted by the primary key columns.

While exact primary key lookups are the fastest scenario, IOTs still perform well in several other cases:

• Primary key range queries: If your query uses a range condition on the primary key (e.g., WHERE customer_id BETWEEN 1000 AND 2000), the B-tree structure lets the database traverse a contiguous block of leaf nodes directly. No extra table lookup is needed, which is way faster than a heap table’s equivalent query (which would require index lookups + heap retrievals for each matching row).
• Covering queries for non-primary key columns: Since all row data lives in the B-tree leaf nodes, any query that only needs columns present in the IOT (even non-primary key ones) can be satisfied directly from the B-tree. For example, if your IOT has a primary key order_id and columns order_date and total_amount, a query like SELECT order_date, total_amount FROM orders WHERE order_id > 500 doesn’t need to jump to a separate heap—it pulls everything straight from the leaf nodes.
• Queries using secondary indexes: IOTs support secondary indexes, but unlike heap tables, secondary indexes store the primary key values instead of ROWIDs. When you use a secondary index to find rows, the database uses the primary key values from the secondary index to directly look up the full row in the IOT’s B-tree. This avoids the heap table’s extra step of resolving ROWIDs to physical locations, making secondary index queries faster than in a heap table.

IOTs aren’t magic—they’ll perform like a full table scan if your query has no conditions that can leverage the primary key B-tree or a secondary index. For example, a query like SELECT * FROM orders WHERE status = 'Pending' with no index on status will require traversing the entire B-tree, which is similar in speed to a heap table full scan (and maybe slightly slower due to the B-tree’s structural overhead).

内容的提问来源于stack exchange，提问作者Ankita Patel