Databases Reference
In-Depth Information
IOTs are generally better suited for OLTP applications than data warehouse applications, simply
because OLTP applications often have a requirement for very fast lookup of primary key data. IOTs are
generally avoided in the data warehouse simply because a data warehouse typically does bulk loading of
data, and the performance of inserts on IOTs is slower. This is especially noticeable with a large volume
of data. Also, if there are access requirements to place many secondary indexes on the IOT, it can
generally be slower just because no physical
ROWID
exists within an IOT; this can slow access of data,
especially over time as physical guesses become stale. Of course, these are guidelines. Deciding whether
or not to use IOTs within your application depends on your specific data loading and data retrieval
requirements. You should also consider your available maintenance windows, which can be used in part
to rebuild IOTs when they become fragmented.
All this said, the index-organized table is a valuable tool. Knowing their features, advantages, and
disadvantages can help you decide where and when to implement them properly within your
application.
