Optimizing Storage Structures - Oracle Database 11gR2 Performance Tuning - page 139

We can see that when we use the heap table, we look up on the INDEX segment, and then
we access the row data using the ROWID . We have two consistent gets (on the INDEX and
the TABLE segments) to access the data.
When we query the index organized table with the same query mentioned earlier, we have
only one operation, namely the INDEX UNIQUE SCAN of the index, which retrieves (with 1
consistent get) the data we are asking for.
This behavior depends on the way data is organized in an index organized table. Similar to an
index, there is a B-tree which stores the key values; instead of storing in the leaf nodes the
ROWID of the table segment, an IOT stores the table data in the leaf nodes, so we have the
table and the index in the same segment.
This solution allows space saving (the index key values are stored once) and performance is
gained when accessing the table data by primary key values—or partial primary key values
when the primary key is multi-field and we know the first value(s) of the key. The same
benefits are in place when we access data using the primary key with an equality operation
or with a range search.
On the other hand, if we want to scan the entire table, the operation reads more data on the
IOT than on the heap table. This is because in the case of the IOT, it is the same segment of
the table where we have also stored the index.
There's more...
In index organized tables, we have some unique properties due to the dual nature of this
structure. One of these features is the use of COMPRESS and NOCOMPRESS options, to enable
the key compression we have seen for indexes.
INCLUDING, OVERFLOW, PCTTHRESHOLD
When we have created the table IOT_COUNTRIES , we have used INCLUDING and
OVERFLOW options.
These parameters—and the PCTTHRESHOLD , which we haven't used—are related to how
much row data will be in the leaf nodes of the index.
These features enable us to decide whether to store only a part of the columns in the index.
Because if we have many fields in the rows, we can store only a few index entries in each
database block, reducing the performance of index searching.
The INCLUDING option lets us specify what fields (except primary key fields) are included in
the index segment. In our example, we have included the COUNTRY_ID , COUNTRY_ISO_CODE ,
and COUNTRY_NAME fields in the index. The other fields are stored in a table segment, named
OVERFLOW , which is stored in the USER tablespace, as requested, in our example.