13.3 Index-Organized Tables and Clusters

---

Some of what applies to tables and indexes applies to index-organized tables and clusters. Since these two types of objects have often been used in the past as read-only data warehouse objects they will not be examined in any detail.

In reality index-organized tables are sometimes used in OLTP-type databases. Many commercial OLTP databases are a combination of online small transactional DML activity with some DSS reporting or listing activity mixed in. Even though index-organized tables may be useful in OLTP databases, they are likely to become unbalanced as indexes faster than a simple index would.

Degradation in index-organized tables is possible over time. Any changes to data must slot into current index structure, rearrange the structure or overflow into nonoptimally physically located segments. Any of these three situations can degrade performance for any type of index. An index-organized table includes data within the structure of the index. An index-organized table is potentially much bigger than a normal BTree index and there is much less scope for UPDATE and DELETE DML activity not degrading that index structure substantially, fairly quickly. Rapidly degrading indexes have to be more frequently rebuilt to maintain their efficiency. Regularly rebuilding indexes can be executed with the index remaining available during the rebuild process. This is not the case with a table.

Index-organized tables are possibly most appropriate for OLTP databases servicing object-built applications, Java written perhaps, where sequence numbers are used to generate primary keys. Using reverse key indexes on the primary keys could help alleviate hot block issues if the index-organized table is subject to high concurrent insertion rates, especially in Oracle RAC environments.

---