Index Organized Tables

INDEX ORGANIZED TABLES


Oracle Index-organized tables (IOTs) are a unique style of table structure that is stored in a B-tree index structure.Primarily used with high-updates tables, Oracle Index Organized tables structure reduces table fragmentation.An index-organized table must have a primary key. Oracle index-organized tables are best suited for use with queries based on primary key values.

Disadvantages of IOT

• It takes longer to insert into the IOT
• You don't have good secondary index capabilities

It takes longer to put the data away in the IOT (mostly regardless of the arrival order) but it is retrieved much faster with less resources (assuming you have something like the stock quotes or document management system examples) .It is best to use an IOT when you only query the table's contents by specifying the primary key in the WHERE clause.If you query other, non-PK columns in the WHERE clause, you may find the IOT will slow down your performance.

As there is no separate table storage area, changes to the table data (such as adding new rows, updating rows, or deleting rows) result only in updating the index structure.All non-key columns beyond the column specified in the INCLUDING clause are stored in the overflow segment.


Index-organized tables have full table functionality. They support features such as constraints, triggers, LOB and object columns, partitioning, parallel operations, online reorganization, and replication. And, they offer these additional features:

• Key compression
• Overflow storage area and specific column placement
• Secondary indexes, including bitmap indexes.