Databases Reference
In-Depth Information
IOTs support many of the same features found in heap-organized tables, such as
•
Constraints
•
Partitioning
•
Triggers
•
LOB columns
•
Parallelism
•
Indexes (e.g. secondary indexes on IOTs)
•
Global hash-partitioned indexes
Online reorganization
Because all of the data within an index-organized table is stored within the index itself, there are
physical differences in the way an index-organized table is stored, as compared to a normal B-tree index
that supports a normal heap-organized table. Some of the unique aspects of IOT's are as follows:
•
Secondary indexes use logical
ROWID
s rather than physical
ROWID
s.
•
•
They
require
a primary key.
•
Primary key compression can be used to save storage and reduce size of an IOT.
•
An overflow segment can be used for non-key column data.
•
Secondary bitmap indexes require a defined mapping table.
•
Non-key column data is stored in the leaf blocks of an IOT.
There are limitations on index-organized tables, although many of the limitations will not affect
their use in the majority of applications. Some of these limitations include:
•
Rows exceeding 50% of a block must use an overflow segment.
•
IOTs can't use virtual columns.
•
Tables with more than 255 columns must have an overflow segment.
•
Tables can't have more than 1,000 total columns.
•
The primary key can't be more than 32 columns.
Understanding the Advantages
There are specific advantages of IOTs, including the following:
•
Storage space can be saved because the data is the index, so there is only one
segment or set of segments in the database for an index-organized table, rather
than the normal two segments that come with a heap-organized table and
associated index(es).
