Performance Tuning  «Prev  Next»
Lesson 6 Limitations in index-organized tables
Objective Understand the differences between index-organized and regular tables.

Index-organized Tables versus Regular Tables

Index-organized tables use a completely different storage structure than regular tables. There are also a number of differences in capability and functionality between the two types of tables.

Differences

The following table summarizes the differences between an index-organized table and a standard table:

Standard table Index-organized table
Bitmap indexes allowed Bitmapped indexes not allowed
Reverse indexes allowed Reverse indexes not allowed
Unique constraint allowed Unique constraint not allowed
Triggers allowed Triggers not allowed
Can be stored in a cluster Cannot be stored in a cluster
Can contain LONG columns Cannot contain LONG columns
Distribution supported Distribution not supported
Replication supported Replication not supported
ROWID uniquely identifies a row–primary key optional Primary key uniquely identifies a row–primary key required
Secondary indexes use ROWIDs to locate table rows Secondary indexes use logical ROWIDs to locate table rows
Secondary indexes store physical data Secondary indexes store primary-key based logical ROWIDs

Once upon a time, there was only one type of table known as a "normal" table. It was managed in the same way a "heap" is managed (the definition of which is below). Over time, Oracle added more sophisticated types of tables. There are clustered tables (two types of those), index organized tables, nested tables, temporary tables, and object tables in addition to the heap organized table. Each type of table has different characteristics that make it suitable for use in different application areas.
Index Organized Tables: In an (iot) table, the table is stored in an index structure. This imposes physical order on the rows themselves. Whereas in a heap, the data is stuffed wherever it might fit, in an index organized table the data is stored in sorted order, according to the primary key.

When to use index organized table

Index organized table :Efficient when most of the column values are included in the primary key. You access the index as if it were a table. The data is stored in a B-tree like structure.

Index-Organized Table

An index-organized table (IOT) stores the entire contents of the table's row in a B-tree index structure. An IOT provides fast access for queries that have exact matches and/or range searches on the primary key. Even though an IOT is implemented as a B-tree index structure, it is created via the
CREATE TABLE...ORGANIZATION INDEX 

statement. For example,

create table prod_sku
(prod_sku_id number
,sku varchar2(256),
constraint prod_sku_pk primary key(prod_sku_id, sku)
) organization index;